MySQL VARCHAR Data Type - Features, Examples and Equivalents

VARCHAR data type stores variable-length character data in single-byte and multibyte character sets.

Versions: MySQL 5.x

Related data types for VARCHAR in MySQL:

Although VARCHAR supports the maximum size at 65535 characters, the actual maximum value depends on other columns in the table and character set:

• Maximum row size is 65535 bytes in MySQL that shared among all columns in the table, except TEXT/BLOB columns
• A character set may require more than 1 byte per character (up to 3 bytes in UTF-8) that further limits the maximum length of VARCHAR

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:

• VARCHAR can have DEFAULT, TEXT cannot

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

• Restricting Column Length

By specifying the column size, VARCHAR(n) prevents from inserting values that exceed n characters. A TEXT column does not have such a restriction.

• No Row Size Limit on TEXT Columns

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:

SQL Server:

PostgreSQL:

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)
   );

• MySQL Reference
• The CHAR and VARCHAR Types - MySQL 5.6 Reference Manual