Skip navigation links
10.1.4 Data Type Default Values »
Section Navigation      [Toggle]

10.1.3. Overview of String Types

A summary of the string data types follows. For additional information about properties of the string types, see Section 10.4, “String Types”. Storage requirements are given in Section 10.5, “Data Type Storage Requirements”.

In some cases, MySQL may change a string column to a type different from that given in a CREATE TABLE or ALTER TABLE statement. See Section 12.1.5.1, “Silent Column Specification Changes”.

In MySQL 4.1 and up, string data types include some features that you may not have encountered in working with versions of MySQL prior to 4.1:

  • As of version 4.1, MySQL interprets length specifications in character column definitions in character units. (Before MySQL 4.1, column lengths were interpreted in bytes.) This applies to CHAR, VARCHAR, and the TEXT types.

  • Column definitions for many string data types can include attributes that specify the character set or collation of the column. These attributes apply to the CHAR, VARCHAR, the TEXT types, ENUM, and SET data types:

    • The CHARACTER SET attribute specifies the character set, and the COLLATE attribute specifies a collation for the character 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.

      The rules for assigning the character set and collation when either or both of the CHARACTER SET and COLLATE attributes are missing are described in Section 9.1.3.4, “Column Character Set and Collation”.

      CHARSET is a synonym for CHARACTER SET.

    • From MySQL 4.1.2 on, specifying the CHARACTER SET binary attribute for a character data type causes the column to be created as the corresponding binary data type: CHAR becomes BINARY, VARCHAR becomes VARBINARY, and TEXT becomes BLOB. For the ENUM and SET data types, this does not occur; they are created as declared. Suppose that you specify a table using this definition: 

      CREATE TABLE t
(
  c1 VARCHAR(10) CHARACTER SET binary,
  c2 TEXT CHARACTER SET binary,
  c3 ENUM('a','b','c') CHARACTER SET binary
);

      The resulting table has this definition: 

      CREATE TABLE t
(
  c1 VARBINARY(10),
  c2 BLOB,
  c3 ENUM('a','b','c') CHARACTER SET binary
);

    • From MySQL 4.1.0 on, the ASCII attribute is shorthand for CHARACTER SET latin1.

    • From MySQL 4.1.1 on, the UNICODE attribute is shorthand for CHARACTER SET ucs2.

    • As of MySQL 4.1.2, 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. (Before MySQL 4.1.2, BINARY caused was disallowed for the TEXT types. For CHAR and VARCHAR, BINARY caused a column to store binary strings and sorting and comparison were based on numeric byte values. This is the same as using character values for single-byte character sets, but not for multi-byte character sets.)

  • Character column sorting and comparison are based on the character set assigned to the column. (Before MySQL 4.1, sorting and comparison were based on the collation of the server character set.) For the CHAR, VARCHAR, TEXT, ENUM, and SET data types, you can declare a column with a binary collation or the BINARY attribute to cause sorting and comparison to use the underlying character code values rather then a lexical ordering.

Section 9.1, “Character Set Support”, provides additional information about use of character sets in MySQL 4.1 and up.

  • [NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

    A fixed-length string that is always right-padded with spaces to the specified length when stored. M represents the column length in characters. The range of M is 0 to 255. (1 to 255 prior to MySQL 3.23). If M is omitted, the length is 1.

    Note

    Trailing spaces are removed when CHAR values are retrieved.

    In MySQL 4.1, a CHAR column with a length specification greater than 255 is converted to the smallest TEXT type that can hold values of the given length. For example, CHAR(500) is converted to TEXT, and CHAR(200000) is converted to MEDIUMTEXT. This is a compatibility feature. However, this conversion causes the column to become a variable-length column, and also affects trailing-space removal.

    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 uses utf8 as this predefined character set. Section 9.1.3.6, “National Character Set”.

    From MySQL 4.1.2 on, the CHAR BYTE data type is an alias for the BINARY data 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 column that is defined as CHAR(0) NULL occupies only one bit and can take only the values NULL and '' (the empty string).

  • [NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]

    A variable-length string. M represents the maximum column length in characters. The range of M is 1 to 255 before MySQL 4.0.2, and 0 to 255 as of MySQL 4.0.2.

    MySQL stores VARCHAR values as a one-byte length prefix plus data. The length prefix indicates the number of bytes in the value.

    Note

    Trailing spaces are removed when VARCHAR values are stored. This differs from the standard SQL specification.

    In MySQL 4.1, a VARCHAR column with a length specification greater than 255 is converted to the smallest TEXT type that can hold values of the given length. For example, VARCHAR(500) is converted to TEXT, and VARCHAR(200000) is converted to MEDIUMTEXT. This is a compatibility feature. However, this conversion affects trailing-space removal.

    VARCHAR is shorthand for CHARACTER VARYING. NATIONAL VARCHAR is the standard SQL way to define that a VARCHAR column should use some predefined character set. MySQL 4.1 and up uses utf8 as this predefined character set. Section 9.1.3.6, “National Character Set”. As of MySQL 4.1.1, NVARCHAR is shorthand for NATIONAL VARCHAR.

  • BINARY(M)

    The BINARY type is similar to the CHAR type, but stores binary byte strings rather than nonbinary character strings. M represents the column length in bytes.

    This type was added in MySQL 4.1.2.

  • VARBINARY(M)

    The VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than nonbinary character strings. M represents the maximum column length in bytes.

    This type was added in MySQL 4.1.2.

  • TINYBLOB

    A BLOB column with a maximum length of 255 (28 – 1) bytes. Each TINYBLOB value is stored using a one-byte length prefix that indicates the number of bytes in the value.

  • TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

    A TEXT column with a maximum length of 255 (28 – 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each TINYTEXT value is stored using a one-byte length prefix that indicates the number of bytes in the value.

  • BLOB[(M)]

    A BLOB column with a maximum length of 65,535 (216 – 1) bytes. Each BLOB value is stored using a two-byte length prefix that indicates the number of bytes in the value.

    Beginning with MySQL 4.1, an optional length M can be given for this type. MySQL creates the column as the smallest BLOB type large enough to hold values M bytes long.

  • TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

    A TEXT column with a maximum length of 65,535 (216 – 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each TEXT value is stored using a two-byte length prefix that indicates the number of bytes in the value.

    Beginning with MySQL 4.1, an optional length M can be given for this type. 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 (224 – 1) bytes. Each MEDIUMBLOB value is stored using a three-byte length prefix that indicates the number of bytes in the value.

  • MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

    A TEXT column with a maximum length of 16,777,215 (224 – 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each MEDIUMTEXT value is stored using a three-byte length prefix that indicates the number of bytes in the value.

  • LONGBLOB

    A BLOB column with a maximum length of 4,294,967,295 or 4GB (232 – 1) bytes. Up to MySQL 3.23, the client/server protocol and MyISAM tables had a limit of 16MB per communication packet or table row. As of MySQL 4.0, the effective maximum length of LONGBLOB columns depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGBLOB value is stored using a four-byte length prefix that indicates the number of bytes in the value.

  • LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

    A TEXT column with a maximum length of 4,294,967,295 or 4GB (232 – 1) characters. The effective maximum length is less if the value contains multi-byte characters. Up to MySQL 3.23, the client/server protocol and MyISAM tables had a limit of 16MB per communication packet or table row. As of MySQL 4.0, the effective maximum length of LONGTEXT columns depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGTEXT value is stored using a four-byte length prefix that indicates the number of bytes in the value.

  • ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

    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',...) [CHARACTER SET charset_name] [COLLATE collation_name]

    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.