(mysql.info) string-type-overview
Info Catalog
(mysql.info) date-and-time-type-overview
(mysql.info) data-type-overview
(mysql.info) data-type-defaults
11.1.3 Overview of String Types
-------------------------------
A summary of the string data types follows. For additional information,
see string-types. Type storage requirements are given in
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
silent-column-changes.
In MySQL 4.1 and up, string data types include some features that you
may not have encountered in working with previous versions of MySQL
(prior to 4.1):
* Column definitions for many string data types can include a
`CHARACTER SET' attribute to specify the character set. (`CHARSET'
is a synonym for `CHARACTER SET'.) The `COLLATE' attribute
specifies a collation for the the character set. These attributes
apply to `CHAR', `VARCHAR', the `TEXT' types, `ENUM', and `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.
* MySQL 5.0 interprets length specifications in character column
definitions in character units. (Previously, MySQL interpreted
lengths in bytes.)
* For `CHAR', `VARCHAR', and the `TEXT' types, the `BINARY'
attribute causes the column to be assigned the binary collation of
the column character set. (Previously, `BINARY' caused a column to
store binary strings.)
* Character column sorting and comparison are based on the character
set assigned to the column. (Previously, sorting and comparison
were based on the collation of the server character set.) For
`CHAR' and `VARCHAR' columns, you can declare the 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.
charset, provides additional information about use of character
sets in MySQL.
* `[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.
* Trailing spaces are removed when `CHAR' values are
retrieved.
Before MySQL 5.0.3, 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.
In MySQL 5.0.3 and later, 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.
charset-national.
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.
In MySQL 5.0, the range of M is 0 to 255 before MySQL 5.0.3, and 0
to 65,535 in MySQL 5.0.3 and later. (The actual maximum length of a
`VARCHAR' in MySQL 5.0 is determined by the maximum row size and
the character set you use. The maximum _effective_ length starting
with MySQL 5.0.3 is 65,532 bytes.)
* Before 5.0.3, trailing spaces were removed when `VARCHAR'
values were stored, which differs from the standard SQL
specification.
Prior to MySQL 5.0.3, a `VARCHAR' column with a length
specification greater than 255 was converted to the smallest
`TEXT' type that could hold values of the given length. For
example, `VARCHAR(500)' was converted to `TEXT', and
`VARCHAR(200000)' was converted to `MEDIUMTEXT'. This was a
compatibility feature. However, this conversion affected
trailing-space removal.
`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.
Starting from MySQL 5.0.3, `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.
Info Catalog
(mysql.info) date-and-time-type-overview
(mysql.info) data-type-overview
(mysql.info) data-type-defaults
automatically generated byinfo2html