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

Updated on 24-Jun-2020 14:15:23

• Related Questions & Answers
• What is the maximum length of MySQL VARCHAR column?
• What is the difference between CHAR and VARCHAR in MySQL?
• MySQL query to get the max value with numeric values in varchar field?
• MySQL query to get max id from varchar type and the values in numeric?
• Will extending the size of a varchar field in MySQL affect the data inside it?
• How much size does the VARCHAR takes- the defined size or the size of the content in SAP HANA?
• What is the way to check the size of all the MySQL databases?
• What is the max length of a Python string?
• Will MySQL work if we won’t include the size of VARCHAR while creating a new table?
• Resolve ERROR 1064 (42000) that occurred after using varchar (without providing the size)
• Sorting varchar field numerically in MySQL?
• Convert varchar to date in MySQL?
• How to implement MAX(distinct…) in MySQL and what is the difference without using DISTINCT?
• How to get the longest VarChar length in MySQL?
• How to alter a MySQL Column from varchar(30) to varchar(100)?