What is the MySQL VARCHAR max size?

The MySQL version before 5.0.3 was capable of storing 255 characters but from the version 5.0.3 , it is capable of storing 65,535 characters.

MySQL official documentation states −

The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters.

Keep in mind that the limitation of maximum row size is 65,535 bytes.This states that including all columns it shouldn't be more than 65,535 bytes.

Let us see what happens if this is violated −

Here is a table with two columns, “one” varchar with the length of 32,765 and “two” with 32766.

Length = 32765+2 + 32766 + 2 = 65535.

CREATE TABLE IF NOT EXISTS `mytable` (
`one` varchar(32765) NOT NULL,
`two` varchar(32766) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Now let us increase the column length −

CREATE TABLE IF NOT EXISTS `mytable` (
`one` varchar(32767) NOT NULL,
`two` varchar(32770) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Above gives the following error −

#1118 - 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

The above itself states that −

The maximum row size is 65,535 bytes. If it exceeds, an error will be visible.

George John

Published on 18-Oct-2018 09:41:10

Previous Page Print Page