11.3.1 String Data Type Syntax (original) (raw)

11.3.1 String Data Type Syntax

The string data types are CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM, andSET.

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 13.1.18.6, “Silent Column Specification Changes”.

For definitions of character string columns (CHAR,VARCHAR, and theTEXT types), MySQL interprets length specifications in character units. For definitions of binary string columns (BINARY,VARBINARY, and theBLOB types), MySQL interprets length specifications in byte units.

Column definitions for character string data typesCHAR,VARCHAR, theTEXT types,ENUM,SET, and any synonyms) can specify the column character set and collation:

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

This table definition creates a column namedc1 that has a character set ofutf8 with the default collation for that character set, and a column named c2 that has a character set of latin1 and a case-sensitive (_cs) collation.
The rules for assigning the character set and collation when either or both of CHARACTER SET and theCOLLATE attribute are missing are described in Section 10.3.5, “Column Character Set and Collation”.
CHARSET is a synonym forCHARACTER SET.

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  
);  
CREATE TABLE t  
(  
  c1 VARCHAR(10) CHARACTER SET latin1 BINARY,  
  c2 TEXT BINARY  
) CHARACTER SET utf8mb4;

The resulting table has this definition: 

CREATE TABLE t (  
  c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin,  
  c2 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin  
) CHARACTER SET utf8mb4;

Character column comparison and sorting are based on the collation assigned to the column. For theCHAR,VARCHAR,TEXT,ENUM, andSET data types, you can declare a column with a binary (_bin) collation or theBINARY attribute to cause comparison and sorting to use the underlying character code values rather than a lexical ordering.

For additional information about use of character sets in MySQL, see Chapter 10, Character Sets, Collations, Unicode.