Contents

Column definitions for many string data types can include a CHARACTER SET attribute to specify the character set. (CHARSET is a synonym for CHARACTER SET.) The COLLATE attribute specifies a collation for the the character set. These attributes apply to CHAR, VARCHAR, the TEXT types, ENUM, and SET. For example:

CREATE TABLE t
(
    c1 VARCHAR(20) CHARACTER SET utf8,
    c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);

This table definition creates a column named c1 that has a character set of utf8 with the default collation for that character set, and a column named c2 that has a character set of latin1 and a case-sensitive collation.

MySQL 5.1 interprets length specifications in character column definitions in character units. (Previously, MySQL interpreted lengths in bytes.)

For CHAR, VARCHAR, and the TEXT types, the BINARY attribute causes the column to be assigned the binary collation of the column character set. (Previously, BINARY caused a column to store binary strings.)

Character column sorting and comparison are based on the character set assigned to the column. (Previously, sorting and comparison were based on the collation of the server character set.) For CHAR and VARCHAR columns, you can declare the column with a binary collation or the BINARY attribute to cause sorting and comparison to use the underlying character code values rather than a lexical ordering.

[NATIONAL] CHAR(M) [BINARY | ASCII | UNICODE]

A fixed-length string that is always right-padded with spaces to the specified length when stored. M represents the column length. The range of M is 0 to 255 characters.

Note: Trailing spaces are removed when CHAR values are retrieved.

If you attempt to set the length of a CHAR greater than 255, the CREATE TABLE or ALTER TABLE statement in which this is done fails with an error:

mysql> CREATE TABLE c1 (col1 INT, col2 CHAR(500));
ERROR 1074 (42000): Column length too big for column 'col' (max = 255);
use BLOB or TEXT instead
mysql> SHOW CREATE TABLE c1;
ERROR 1146 (42S02): Table 'test.c1' doesn't exist

CHAR is shorthand for CHARACTER. NATIONAL CHAR (or its equivalent short form, NCHAR) is the standard SQL way to define that a CHAR column should use some predefined character set. MySQL 4.1 and up utf8 as this predefined character set. Section 10.3.6, “National Character Set”.

The BINARY attribute is shorthand for specifying the binary collation of the column character set. In this case, sorting and comparison are based on numeric character values.

The ASCII attribute is shorthand for CHARACTER SET latin1.

The UNICODE attribute is shorthand for CHARACTER SET ucs2.

The CHAR BYTE data type is an alias for the BINARY type. This is a compatibility feature.

MySQL allows you to create a column of type CHAR(0). This is useful primarily when you have to be compliant with old applications that depend on the existence of a column but that do not actually use its value. CHAR(0) is also quite nice when you need a column that can take only two values: A CHAR(0) column that is not defined as NOT NULL occupies only one bit and can take only the values NULL and '' (the empty string).

CHAR

This type is a synonym for CHAR(1).

[NATIONAL] VARCHAR(M) [BINARY]

A variable-length string. M represents the maximum column length. The range of M is 0 to 65,535. (The actual maximum length of a VARCHAR is determined by the maximum row size and the character set you use. The maximum effective length is 65,532 bytes.)

Note: MySQL 5.1 follows the standard SQL specification, and does not remove trailing spaces from VARCHAR values.

VARCHAR is shorthand for CHARACTER VARYING.

The BINARY attribute is shorthand for specifying the binary collation of the column character set. In this case, sorting and comparison are based on numeric character values.

VARCHAR is stored with a one-byte or two-byte length prefix plus data. The length prefix is two bytes if the VARCHAR column is declared with a length greater than 255.

BINARY(M)

The BINARY type is similar to the CHAR type, but stores binary byte strings rather than non-binary character strings.

VARBINARY(M)

The VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than non-binary character strings.

TINYBLOB

A BLOB column with a maximum length of 255 (2⁸ – 1) bytes.

TINYTEXT

A TEXT column with a maximum length of 255 (2⁸ – 1) characters.

BLOB[(M)]

A BLOB column with a maximum length of 65,535 (2¹⁶ – 1) bytes.

An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest BLOB type large enough to hold values M bytes long.

TEXT[(M)]

A TEXT column with a maximum length of 65,535 (2¹⁶ – 1) characters.

An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest TEXT type large enough to hold values M characters long.

MEDIUMBLOB

A BLOB column with a maximum length of 16,777,215 (2²⁴ – 1) bytes.

MEDIUMTEXT

A TEXT column with a maximum length of 16,777,215 (2²⁴ – 1) characters.

LONGBLOB

A BLOB column with a maximum length of 4,294,967,295 or 4GB (2³² – 1) bytes. The maximum effective (permitted) length of LONGBLOB columns depends on the configured maximum packet size in the client/server protocol and available memory.

LONGTEXT

A TEXT column with a maximum length of 4,294,967,295 or 4GB (2³² – 1) characters. The maximum effective (permitted) length of LONGTEXT columns depends on the configured maximum packet size in the client/server protocol and available memory.

ENUM('value1','value2',...)

An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., NULL or the special '' error value. An ENUM column can have a maximum of 65,535 distinct values. ENUM values are represented internally as integers.

SET('value1','value2',...)

A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... A SET column can have a maximum of 64 members. SET values are represented internally as integers.