What is the maximum length of MySQL VARCHAR column?

MySQLMySQLi Database

Actually VARCHAR data type stores variable-length character data in single byte and multibyte character Syntax for this data type is VARCHAR(n),where n is the maximum number of characters and it must be specified while creating the table. Before MySQL 5.03 the value of n can be in the range of 0 to 255 but in and after MySQL 5.03 the value can be in the range of 0 to 65,535.

The maximum number of characters stored in VARCHAR depends upon maximum row size and character set used. If we are using ASCII character set then it can store 65,535 characters because ASCII would use 1 byte per character. On the other hand, if we are using utf8 character set then the character limit would be of 21,844 because utf8 will use 3 bytes per characters. Maximum row size limit is 65,535 bytes which means that including all columns it must not be more than 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 query above created a table with two columns, FName Varchar(32765) and LName Varchar(32766). Hence the total length would be 32765+2+32766+2=65535(2 bytes are used each column for storing 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, on increasing the length by 1 byte MySQL returns an error as shown in the query above.

raja
Published on 19-Feb-2018 16:55:26
Advertisements