- Trending Categories
- Data Structure
- Operating System
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
- 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?
- What is the maximum length of MySQL VARCHAR column?
- What is the difference between CHAR and VARCHAR in MySQL?
- Will extending the size of a varchar field in MySQL affect the data inside it?
- Will MySQL work if we won’t include the size of VARCHAR while creating a new table?
- How much size does the VARCHAR takes- the defined size or the size of the content in SAP HANA?
- How to alter a MySQL Column from varchar(30) to varchar(100)?
- Sorting varchar field numerically in MySQL?
- Convert varchar to date in MySQL?
- How to increase varchar size of an existing column in a database without breaking existing data in MySQL?
- How to implement MAX(distinct…) in MySQL and what is the difference without using DISTINCT?
- What is the way to check the size of all the MySQL databases?
- Resolve ERROR 1064 (42000) that occurred after using varchar (without providing the size)
- How to get the longest VarChar length in MySQL?