- Trending Categories
- Data Structure
- Operating System
- C Programming
- 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 maximum length of MySQL VARCHAR column?
152 Lectures 16 hours
87 Lectures 5.5 hours
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.
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.
- Find maximum value from a VARCHAR column in MySQL
- What is the maximum length of a table name in MySQL?
- What is the maximum length of data we can put in a BLOB column in MySQL?
- What is the maximum length of data we can put in a TEXT column in MySQL?
- What is the maximum length of each type of identifier in MySQL?
- What is the MySQL VARCHAR max size?
- What is the maximum length of string in Python?
- How to update a column of varchar type in MySQL to increase its length?
- How to get the longest VarChar length in MySQL?
- What is the difference between CHAR and VARCHAR in MySQL?
- Getting the maximum value from a varchar field in MySQL
- How to alter a MySQL Column from varchar(30) to varchar(100)?
- Maximum Column name length in SAP HANA
- Order By Length of Column in MySQL
- Get the minimum and maximum value from a VARCHAR column and display the result in separate MySQL columns?