{"id":5572,"date":"2017-02-07T02:26:12","date_gmt":"2017-02-07T09:26:12","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5572"},"modified":"2025-09-10T18:12:34","modified_gmt":"2025-09-11T01:12:34","slug":"mysql-varchar","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-varchar\/","title":{"rendered":"MySQL VARCHAR Data Type"},"content":{"rendered":"\n

Summary<\/strong>: this tutorial introduces you to the MySQL VARCHAR<\/code> data type and discusses some important features of VARCHAR<\/code>.<\/p>\n\n\n\n

Introduction to MySQL VARCHAR data type<\/h2>\n\n\n\n

MySQL VARCHAR<\/code> is the variable-length string whose length can be up to 65,535. MySQL stores a VARCHAR<\/code> value as a 1-byte or 2-byte length prefix plus actual data.<\/p>\n\n\n\n

The length prefix specifies the number of bytes in the value. If a column requires less than 255 bytes, the length prefix is 1 byte. In case the column requires more than 255 bytes, the length prefix is two length bytes.<\/p>\n\n\n\n

The maximum length, however, is subject to the maximum row size (65,535 bytes) and the character set<\/a> used. It means that the total length of all columns should be less than 65,535 bytes.<\/p>\n\n\n\n

Let’s take a look at an example.<\/p>\n\n\n\n

We will create a new table<\/a> that has two columns s1<\/code> and s2<\/code> with the length of 32765(+2 for length prefix) and 32766 (+2). Note that 32765+2+32766+2=65535, which is the maximum row size.<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> varchar_test (\n    s1 VARCHAR<\/span>(32765<\/span>) NOT<\/span> NULL<\/span>,\n    s2 VARCHAR<\/span>(32766<\/span>) NOT<\/span> NULL<\/span>\n)  CHARACTER<\/span> SET<\/span> 'latin1'<\/span> COLLATE<\/span> LATIN1_DANISH_CI;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The statement created the table successfully. However, if we increase the length of the s1<\/code> column by 1.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> varchar_test_2 (\n    s1 VARCHAR<\/span>(32766<\/span>) NOT<\/span> NULL<\/span>, -- error<\/span>\n    s2 VARCHAR<\/span>(32766<\/span>) NOT<\/span> NULL<\/span>\n)  CHARACTER<\/span> SET<\/span> 'latin1'<\/span> COLLATE<\/span> LATIN1_DANISH_CI;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL will issue the error message:<\/p>\n\n\n
Error<\/span> Code: 1118.<\/span> Row size too large. The maximum row size for<\/span> the used table type, not counting BLOBs, is 65535.<\/span> This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs 0.000<\/span> sec<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the row size is too large and the statement fails.<\/p>\n\n\n\n
CREATE<\/span> TABLE<\/span> items (\n    id<\/span> INT<\/span> PRIMARY KEY<\/span> AUTO_INCREMENT,\n    title VARCHAR<\/span>(3<\/span>)\n);\n\nINSERT<\/span> INTO<\/span> items(title)\nVALUES<\/span>('ABCD'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, MySQL will issue the following error message:<\/p>\n\n\n
Error<\/span> Code: 1406.<\/span> Data too long for<\/span> column 'title'<\/span> at row 1<\/span> 0.000<\/span> sec<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL VARCHAR and spaces<\/h2>\n\n\n\n
MySQL does not implicitly pad space when storing the VARCHAR<\/code> values. Additionally, MySQL retains the trailing spaces when inserting or retrieving VARCHAR<\/code> values. For example:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> items(title)\nVALUES<\/span>('AB '<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
SELECT<\/span> \n    id<\/span>, title, length<\/span>(title)\nFROM<\/span>\n    items;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----+-------+---------------+\n| id | title | length(title) |\n+----+-------+---------------+\n|  1<\/span> | AB    |             3<\/span> |\n+----+-------+---------------+\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
However, MySQL will truncate the trailing spaces when inserting a VARCHAR<\/code> value that contains trailing spaces which cause the column length exceeded. In addition, MySQL issues a warning. Let’s see the following example:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> items(title)\nVALUES<\/span>('ABC '<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Query<\/span> OK<\/span>, 1 row<\/span> affected<\/span>, 1 warning<\/span> (0.01<\/span> sec<\/span>)<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
This statement inserts a string whose length is 4 into the title<\/code> column. MySQL inserts the string but truncates the trailing space before inserting the value.<\/p>\n\n\n\n
To show the warning message, you can use the SHOW WARNINGS<\/code> statement:<\/p>\n\n\n
SHOW<\/span> WARNINGS<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
1 row(s) affected, 1 warning(s): 1265 Data truncated for column 'title' at row 1<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following query verifies the action:<\/p>\n\n\n
SELECT<\/span> \n    title, LENGTH<\/span>(title)\nFROM<\/span>\n    items;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-------+---------------+\n| title | LENGTH(title) |\n+-------+---------------+\n| AB    |             3<\/span> |\n| ABC   |             3<\/span> |\n+-------+---------------+\n2<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned how to use MySQL VARCHAR<\/code> data type to store variable strings in the database.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
If you insert<\/a> a string whose length is greater than the length of a VARCHAR<\/code> column, MySQL will issue an error and skip inserting data. For example:<\/p>\n\n\n