- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
What is the maximum length of MySQL VARCHAR column?
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.
- Related Articles
- Find maximum value from a VARCHAR column 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?
- How to update a column of varchar type in MySQL to increase its length?
- What is the maximum length of a table name in MySQL?
- What is the maximum length of each type of identifier in MySQL?
- What is the MySQL VARCHAR max size?
- How to get the longest VarChar length in MySQL?
- How to alter a MySQL Column from varchar(30) to varchar(100)?
- Get the minimum and maximum value from a VARCHAR column and display the result in separate MySQL columns?
- Getting the maximum value from a varchar field in MySQL
- Get maximum date from a list of varchar dates in MySQL
- What is the maximum length of string in Python?
- How to sum varchar column and display the count in MySQL?
- What is the difference between CHAR and VARCHAR in MySQL?