-
[NATIONAL] CHAR(M
)
[BINARY | ASCII | UNICODE]
A fixed-length string that is always right-padded with
spaces to the specified length when stored.
M
represents the column length.
The range of M
is 0 to 255
characters.
Note: Trailing spaces are
removed when CHAR
values are retrieved.
If you attempt to set the length of a
CHAR
greater than 255, the
CREATE TABLE
or ALTER
TABLE
statement in which this is done fails with
an error:
mysql> CREATE TABLE c1 (col1 INT, col2 CHAR(500));
ERROR 1074 (42000): Column length too big for column 'col' (max = 255);
use BLOB or TEXT instead
mysql> SHOW CREATE TABLE c1;
ERROR 1146 (42S02): Table 'test.c1' doesn't exist
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
utf8
as this predefined character set.
Section 10.3.6, “National Character Set”.
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.
The ASCII
attribute is shorthand for
CHARACTER SET latin1
.
The UNICODE
attribute is shorthand for
CHARACTER SET ucs2
.
The CHAR BYTE
data type is an alias for
the BINARY
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
CHAR(0)
column that is not defined as
NOT NULL
occupies only one bit and can
take only the values NULL
and
''
(the empty string).
-
CHAR
This type is a synonym for CHAR(1)
.
-
[NATIONAL] VARCHAR(M
)
[BINARY]
A variable-length string. M
represents the maximum column length. The range of
M
is 0 to 65,535. (The actual
maximum length of a VARCHAR
is determined
by the maximum row size and the character set you use. The
maximum effective length is 65,532
bytes.)
Note: MySQL
5.1 follows the standard SQL specification, and
does not remove trailing spaces from
VARCHAR
values.
VARCHAR
is shorthand for
CHARACTER VARYING
.
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.
VARCHAR
is stored with a one-byte or
two-byte length prefix plus data. The length prefix is two
bytes if the VARCHAR
column is declared
with a length greater than 255.
-
BINARY(M
)
The BINARY
type is similar to the
CHAR
type, but stores binary byte strings
rather than non-binary character strings.
-
VARBINARY(M
)
The VARBINARY
type is similar to the
VARCHAR
type, but stores binary byte
strings rather than non-binary character strings.
-
TINYBLOB
A BLOB
column with a maximum length of
255 (28 – 1) bytes.
-
TINYTEXT
A TEXT
column with a maximum length of
255 (28 – 1) characters.
-
BLOB[(M
)]
A BLOB
column with a maximum length of
65,535 (216 – 1) bytes.
An optional length M
can be given
for this type. If this is done, MySQL creates the column as
the smallest BLOB
type large enough to
hold values M
bytes long.
-
TEXT[(M
)]
A TEXT
column with a maximum length of
65,535 (216 – 1)
characters.
An optional length M
can be given
for this type. If this is done, 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.
-
MEDIUMTEXT
A TEXT
column with a maximum length of
16,777,215 (224 – 1)
characters.
-
LONGBLOB
A BLOB
column with a maximum length of
4,294,967,295 or 4GB (232 –
1) bytes. The maximum effective
(permitted) length of LONGBLOB
columns
depends on the configured maximum packet size in the
client/server protocol and available memory.
-
LONGTEXT
A TEXT
column with a maximum length of
4,294,967,295 or 4GB (232 –
1) characters. The maximum effective
(permitted) length of LONGTEXT
columns
depends on the configured maximum packet size in the
client/server protocol and available memory.
-
ENUM('value1
','value2
',...)
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
',...)
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.