String Type Overview
String
A summary of the string data types follows. For additional information about properties and storage requirements of the string types, see Section 11.4, “String Types”, and Section 11.7, “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 13.1.14.3, “Silent Column Specification Changes”.
MySQL interprets length specifications in character column definitions in character units. 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 theCOLLATE
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 ofutf8
with the default collation for that character set, and a column namedc2
that has a character set oflatin1
and a case-sensitive collation.The rules for assigning the character set and collation when either or both of the
CHARACTER SET
andCOLLATE
attributes are missing are described in Section 10.1.3.4, “Column Character Set and Collation”.CHARSET
is a synonym forCHARACTER SET
. -
Specifying the
CHARACTER SET binary
attribute for a character data type causes the column to be created as the corresponding binary data type:CHAR
becomesBINARY
,VARCHAR
becomesVARBINARY
, andTEXT
becomesBLOB
. For theENUM
andSET
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 );
-
The
ASCII
attribute is shorthand forCHARACTER SET latin1
. -
The
UNICODE
attribute is shorthand forCHARACTER SET ucs2
. -
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.
Character column sorting and comparison are based on the character set assigned to the column. 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 than a lexical ordering.
Section 10.1, “Character Set Support”, provides additional information about use of character sets in MySQL.
-
[NATIONAL] CHAR[(
M
)] [CHARACTER SETcharset_name
] [COLLATEcollation_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 ofM
is 0 to 255. IfM
is omitted, the length is 1.NoteTrailing spaces are removed when
CHAR
values are retrieved unless thePAD_CHAR_TO_FULL_LENGTH
SQL mode is enabled.CHAR
is shorthand forCHARACTER
.NATIONAL CHAR
(or its equivalent short form,NCHAR
) is the standard SQL way to define that aCHAR
column should use some predefined character set. MySQL 4.1 and up usesutf8
as this predefined character set. Section 10.1.3.6, “National Character Set”.The
CHAR BYTE
data type is an alias for theBINARY
data type. This is a compatibility feature.MySQL permits 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 asCHAR(0) NULL
occupies only one bit and can take only the valuesNULL
and''
(the empty string).