String Types

The MySQL string types are commonly used to store text, but are general-purpose types and may hold arbitrary data. Types are available to hold values of varying maximum lengths and can be chosen according to whether or not you want values to be treated in case sensitive fashion.

As of MySQL 4.1, you can specify character sets on a column-specific basis for the CHAR, VARCHAR, and TEXT types. The syntax is CHARACTER SET charset, where charset is a character set identifier such as latin1, greek, or utf8. The allowable character sets supported by the server can be determined by issuing a SHOW CHARACTER SET statement. Note that specifying a character set for CHAR or VARCHAR columns precludes use of the BINARY attribute that normally is allowable for those types.

CHAR[(M)]
Meaning: A fixed-length character string 0 to M bytes long. M should be an integer from 0 to 255 prior (1 to 255 prior to MySQL 3.23). If M is omitted, it defaults to 1. Strings longer than M characters are chopped to length M when stored. Strings shorter than M characters are right-padded with spaces when stored. Trailing spaces are removed when values are retrieved.

Allowable attributes: BINARY, CHARACTER SET (as of MySQL 4.1).

Allowable length: 0 to M bytes.

Default value: NULL if column can be NULL, '' (empty string) if NOT NULL.

Storage required: M bytes.

Comparisons: Not case sensitive, unless the BINARY attribute is specified.

Synonyms: CHAR with no argument is a synonym for CHAR(1). BINARY(M) is a synonym for CHAR(M) BINARY. As of MySQL 3.23.5, NCHAR(M) and NATIONAL CHAR(M) are synonyms for CHAR(M).

VARCHAR(M)
Meaning: A variable-length character string 0 to M bytes long. M should be an integer from 0 to 255 prior (1 to 255 prior to MySQL 4.0.2). Strings longer than M characters are chopped to length M when stored. Trailing spaces are removed from values when stored. (Trailing space removal is scheduled to become optional in MySQL 4.1, but that has not been implemented as of this writing.)

Allowable attributes: BINARY, CHARACTER SET (as of MySQL 4.1).

Allowable length: 0 to M bytes.

Default value: NULL if column can be NULL, '' (empty string) if NOT NULL.

Storage required: Length of value, plus 1 byte to record the length.

Comparisons: Not case sensitive, unless the BINARY attribute is specified.

Synonyms: CHAR VARYING(M). As of MySQL 3.23.5, NCHAR VARYING(M) and NATIONAL CHAR VARYING(M) are synonyms for VARCHAR(M).

TINYBLOB
Meaning: A small BLOB value

Allowable attributes: None, other than the global attributes

Allowable length: 0 to 255 (0 to 28–1) bytes

Default value: NULL if column can be NULL, '' (empty string) if NOT NULL

Storage required: Length of value plus 1 byte to record the length

Comparisons: Case sensitive

BLOB
Meaning: A normal-sized BLOB value

Allowable attributes: None, other than the global attributes

Allowable length: 0 to 65535 (0 to 216–1) bytes

Default value: NULL if column can be NULL, '' (empty string) if NOT NULL

Storage required: Length of value plus 2 bytes to record the length

Comparisons: Case sensitive

MEDIUMBLOB
Meaning: A medium-sized BLOB value

Allowable attributes: None, other than the global attributes

Allowable length: 0 to 16777215 (0 to 224–1) bytes

Default value: NULL if column can be NULL, '' (empty string) if NOT NULL

Storage required: Length of value plus 3 bytes to record the length

Comparisons: Case sensitive

Synonyms: LONG VARBINARY

LONGBLOB
Meaning: A large BLOB value

Allowable attributes: None, other than the global attributes

Allowable length: 0 to 4294967295 (0 to 232–1) bytes

Default value: NULL if column can be NULL, '' (empty string) if NOT NULL

Storage required: Length of value plus 4 bytes to record the length

Comparisons: Case sensitive

TINYTEXT
Meaning: A small TEXT value

Allowable attributes: CHARACTER SET (as of MySQL 4.1)

Allowable length: 0 to 255 (0 to 28–1) bytes

Default value: NULL if column can be NULL, '' (empty string) if NOT NULL

Storage required: Length of value plus 1 byte to record the length

Comparisons: Not case sensitive

TEXT
Meaning: A normal-sized TEXT value

Allowable attributes: CHARACTER SET (as of MySQL 4.1)

Allowable length: 0 to 65535 (0 to 216–1) bytes

Default value: NULL if column can be NULL, '' (empty string) if NOT NULL

Storage required: Length of value plus 2 bytes to record the length

Comparisons: Not case sensitive

MEDIUMTEXT
Meaning: A medium-sized TEXT value

Allowable attributes: CHARACTER SET (as of MySQL 4.1)

Allowable length: 0 to 16777215 (0 to 224–1) bytes

Default value: NULL if column can be NULL, '' (empty string) if NOT NULL

Storage required: Length of value plus 3 bytes to record the length

Comparisons: Not case sensitive

Synonyms: LONG VARCHAR

LONGTEXT
Meaning: A large TEXT value

Allowable attributes: CHARACTER SET (as of MySQL 4.1)

Allowable length: 0 to 4294967295 (0 to 232–1) bytes

Default value: NULL if column can be NULL, '' (empty string) if NOT NULL

Storage required: Length of value plus 4 bytes to record the length

Comparisons: Not case sensitive

ENUM('value1','value2',...)
Meaning: An enumeration; column values may be assigned exactly one member of the value list

Allowable attributes: None, other than the global attributes listed in the chapter introduction

Default value: NULL if column can be NULL, first enumeration value if NOT NULL

Storage required: 1 byte for enumerations with 1 to 255 members, 2 bytes for enumerations with 256 to 65535 members

Comparisons: Not case sensitive (case sensitive prior to MySQL 3.22.1)

SET('value1','value2',...)
Meaning: A set; column values may be assigned zero or more members of the value list

Allowable attributes: None, other than the global attributes listed in the chapter introduction

Default value: NULL if column can be NULL, '' (empty set) if NOT NULL

Storage required: 1 byte (for sets with 1 to 8 members), 2 bytes (9 to 16 members), 3 bytes (17 to 24 members), 4 bytes (25 to 32 members), or 8 bytes (33 to 64 members)

Comparisons: Not case sensitive (case sensitive prior to MySQL 3.22.1)