VARCHAR data type stores variable-length character data in single-byte and multibyte character sets.
Syntax | VARCHAR(n) | ||
Quick Example | CREATE TABLE t (c VARCHAR(10)); | ||
Parameter | n is the maximum number of characters | ||
Range | 0 ⇐ n ⇐ 65535/charsize | 0 ⇐ n ⇐ 21844 for UTF-8 | 65,535 bytes shared by all columns |
Default | n must be specified | ||
Padding | Not right-padded with spaces to n | ||
Trailing Spaces | Stored and retrieved if data contains them. Insignificant in comparisons, primary/unique keys |
||
Zero Length | VARCHAR(0) can store '' (empty value) and NULL only | ||
Synonyms | CHAR VARYING, CHARACTER VARYING |
Versions: MySQL 5.x
Related data types for VARCHAR in MySQL:
CHAR(n) | 0 ⇐ n ⇐ 255 | Fixed-length |
TINYTEXT | up to 255 characters | Variable-length |
TEXT | up to 65,535 characters | |
MEDIUMTEXT | up to 16 Mb | |
LONGTEXT | up to 4 Gb |
Although VARCHAR supports the maximum size at 65535 characters, the actual maximum value depends on other columns in the table and character set:
For example, in a UTF-8 database (default in MySQL), you can create VARCHAR column with maximum length at 21,844 characters only:
-- UTF-8 database CREATE TABLE t_varchar1u ( c1 VARCHAR(21844) ); -- Table created. CREATE TABLE t_varchar1u2 ( c1 VARCHAR(21845) ); -- Error Code: 1118. Row size too large. -- The maximum row size for the used table type, not counting BLOBs, is 65535.
If a table contains multiple VARCHAR columns, the maximum size for each column is reduced even more:
-- UTF-8 database CREATE TABLE t_varchar1u2 ( c1 VARCHAR(8000), c2 VARCHAR(8000), c3 VARCHAR(8000) ); -- Error Code: 1118. Row size too large. -- The maximum row size for the used table type, not counting BLOBs, is 65535.
Note. You can use TEXT columns to overcome the MySQL row size limit (see below).
Both VARCHAR and TEXT can store data up to 65535 bytes, and besides the maximum length restriction on VARCHAR described above, they have other differences:
A VARCHAR column can have a DEFAULT, while a TEXT column cannot:
CREATE TABLE t_varchar1d ( c1 VARCHAR(10) DEFAULT 'A' ); -- Table created CREATE TABLE t_text1d ( c1 TEXT DEFAULT 'A' ); -- Error Code: 1101. BLOB/TEXT column 'c1' can't have a default value
By specifying the column size, VARCHAR(n) prevents from inserting values that exceed n characters. A TEXT column does not have such a restriction.
Row size limit at 65,535 bytes does not apply to TEXT columns, and you can have multiple TEXT columns in a table that store up to 65,535 bytes each.
Similar data types in other databases:
Oracle:
VARCHAR2(n) | 1 ⇐ n ⇐ 4000/charsize | |
CLOB | Up to 4 Gb | Cannot be used in ORDER BY and GROUP BY |
SQL Server:
VARCHAR(n | max) | 1 ⇐ n ⇐ 8000 | Up to 2 Gb if max is specified |
TEXT | Up to 2 Gb | Legacy, not recommended for use |
PostgreSQL:
VARCHAR(n) | 1 ⇐ n ⇐ 1 Gb | n is 1 Gb by default |
TEXT | Up to 1 Gb | Trailing spaces are significant in comparisons |
Convertion of VARCHAR data type:
MySQL:
CREATE TABLE t_varchar1 ( c1 VARCHAR(0), c2 VARCHAR(100), c3 VARCHAR(15000) );
Oracle:
Oracle does not allow VARCHAR2(0), and the maximum length is 4,000 bytes.
Note. Although you can use Oracle CLOB columns in string functions, you cannot use them in GROUP BY and ORDER BY.
CREATE TABLE t_varchar1 ( c1 VARCHAR2(1), c2 VARCHAR2(100), c3 CLOB );
SQL Server:
SQL Server does not allow VARCHAR(0), and the maximum length is 8,000 bytes:
CREATE TABLE t_varchar1 ( c1 VARCHAR(1), c2 VARCHAR(100), c3 VARCHAR(max) );
PostgreSQL:
PostgreSQL does not allow VARCHAR(0), but the maximum length is 1 Gb:
CREATE TABLE t_varchar1 ( c1 VARCHAR(0), c2 VARCHAR(100), c3 VARCHAR(15000) );