Mysql varchar max length

Posted on July 7, 2015 by Suresh Kamrushi in Mysql

Maximum data what we can store in varchar in mysql was 255. But after Mysql version 5.0.3 varchar can store maximum of 65,535 characters.

But again this limit is having limitation of maximum row size which is 65535 bytes. It means including all columns it should be less than 65,535 bytes.

Lets take an example as below:
I have created below table with two columns as “f1” varchar with the length of 32,765 and “f2” with 32766. Total length will be 32765+2(for storing length) + 32766 + 2 = 65535.

CREATE TABLE IF NOT EXISTS `test_table` (
  `f1` varchar(32765) NOT NULL,
  `f2` varchar(32766) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

But when I create increase the column length with a single character as below:

CREATE TABLE IF NOT EXISTS `test_table2` (
  `f1` varchar(32766) NOT NULL,   //INCREASED LENGTH BY 1
  `f2` varchar(32766) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

it will give me 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

For more details as below: dev.mysql.com

Hope it help somebody out there….

  Comments or questions are welcome  

Tagged as , ,