What is the maximum length of a MySQL VARCHAR column?

#In fact, the VARCHAR data type stores variable-length character data in single-byte and multi-byte characters. The syntax for this data type is VARCHAR(n), where n is the maximum number of characters that must be specified when creating the table. Prior to MySQL 5.03, the value of n could be in the range 0 to 255, but in MySQL 5.03 and later, the value could be in the range 0 to 65,535.

The maximum number of characters stored in a VARCHAR depends on the maximum row size and the character set used. If we use ASCII character set then it can store 65,535 characters as ASCII uses 1 byte per character. On the other hand, if we use utf8 character set, the character limit will be 21,844 because utf8 will use 3 bytes per character. The maximum row size limit is 65,535 bytes, which means that including all columns, the row size must not exceed 65,535 bytes.

Example

mysql> Create Table var_test(FName Varchar(32765) NOT NULL, LName Varchar(32766)
       NOT NULL);
       Query OK, 0 rows affected (0.25 sec)

The above query creates a table with two columns: FName Varchar(32765) and LName Varchar(32766). So the total length is 32765 2 32766 2 = 65535 (using 2 bytes per column to store the length).

mysql> Create Table var_test1(FName varchar(32766) NOT NULL, LName Varchar(32766)
       NOT NULL);
       ERROR 1118 (42000): Row size too large. The maximum row size for the used
       table
       type, not counting BLOBs, is 65535. You have to change some columns to TEXT
       or BLOBs

Now, MySQL will return an error when the length is increased by 1 byte, as shown in the query above.

The above is the detailed content of What is the maximum length of a MySQL VARCHAR column?. For more information, please follow other related articles on the PHP Chinese website!