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.
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.
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
.
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.
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.
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.
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.
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.
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.
User Comments
Add your own comment.