(mysql.info.gz) String type overview
Info Catalog
(mysql.info.gz) Date and time type overview
(mysql.info.gz) Column type overview
11.1.3 Overview of String Types
-------------------------------
A summary of the string column types follows. For additional
information, see String types. Column 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.
Silent column changes.
A change that affects many string column types is that, as of MySQL 4.1,
character column definitions can include a `CHARACTER SET' attribute to
specify the character set and, optionally, a collation. This applies to
`CHAR', `VARCHAR', the `TEXT' types, `ENUM', and `SET'. For example:
CREATE TABLE t
(
c1 CHAR(20) CHARACTER SET utf8,
c2 CHAR(20) CHARACTER SET latin1 COLLATE latin1_bin
);
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 the binary
collation for the character set. The binary collation is not case
sensitive.
For `CHAR', `VARCHAR', and the `TEXT' types, the `BINARY' attribute
causes the column to be assigned the binary collation of the column
character set as of MySQL 4.1. Before 4.1, `BINARY' is disallowed for
the `TEXT' types, and causes `CHAR' and `VARCHAR' to be treated as
binary strings.
Character column sorting and comparison are based on the character set
assigned to the column. Before MySQL 4.1, sorting and comparison are
based on the collation of the server character set. For `CHAR' and
`VARCHAR' columns, you can declare the column with the `BINARY'
attribute to cause sorting and comparison to use the underlying
character code values rather then a lexical ordering.
For more details, see Charset.
Also as of 4.1, MySQL interprets length specifications in character
column definitions in characters. (Earlier versions interpret them in
bytes.)
`[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 (1 to 255 prior to MySQL
3.23).
* Trailing spaces are removed when `CHAR' values are
retrieved.
From MySQL 4.1.0, 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 the default character set. This
is the default in MySQL.
As of MySQL 4.1.2, the `BINARY' attribute is shorthand for
specifying the binary collation of the column character set.
Before 4.1.2, `BINARY' attribute causes the column to be treated
as a binary string. In either case, sorting and comparisons
become case sensitive.
From MySQL 4.1.0 on, the `ASCII' attribute can be specified. It
assigns the `latin1' character set to a `CHAR' column.
From MySQL 4.1.1 on, the `UNICODE' attribute can be specified. It
assigns the `ucs2' character set to a `CHAR' column.
MySQL allows you to create a column of type `CHAR(0)'. This is
mainly useful when you have to be compliant with some old
applications that depend on the existence of a column but that do
not actually use the value. This 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 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 255 characters (1 to 255 prior to MySQL
4.0.2). In MySQL 5.0.3 this is extended to `65535' bytes. (The
exact number of characters is depending on the character set).
* Before 5.0.3, trailing spaces where removed when `VARCHAR'
values was stored, which differs from the standard SQL
specification.
From MySQL 4.1.0 - 5.0.2 on, 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'.
As of MySQL 4.1.2, the `BINARY' attribute is shorthand for
specifying the binary collation of the column character set.
Before 4.1.2, `BINARY' attribute causes the column to be treated
as a binary string. In either case, sorting and comparisons
become case sensitive.
Starting from MySQL 5.0.3, `VARCHAR' is stored with a 1 byte or 2
byte length prefix + data. The length prefix is 1 byte if the
storage size for the `VARCHAR' column is less than 256.
`BINARY(M)'
The `BINARY' type is similar to the `CHAR' type, but stores binary
strings rather than non-binary strings.
This type was added in MySQL 4.1.2.
`VARBINARY(M)'
The `VARBINARY' type is similar to the `VARCHAR' type, but stores
binary strings rather than non-binary strings.
This type was added in MySQL 4.1.2.
`TINYBLOB'
`TINYTEXT'
A `BLOB' or `TEXT' column with a maximum length of 255 (2^8 - 1)
characters.
`BLOB'
`TEXT'
A `BLOB' or `TEXT' column with a maximum length of 65,535 (2^16 -1)
characters.
`MEDIUMBLOB'
`MEDIUMTEXT'
A `BLOB' or `TEXT' column with a maximum length of 16,777,215
(2^24 - 1) characters.
`LONGBLOB'
`LONGTEXT'
A `BLOB' or `TEXT' column with a maximum length of 4,294,967,295 or
4GB (2^32 - 1) characters. Up to MySQL 3.23, the client/server
protocol and `MyISAM' tables had a limit of 16MB per communication
packet / table row. From MySQL 4.0, the maximum allowed length of
`LONGBLOB' or `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.gz) Date and time type overview
(mysql.info.gz) Column type overview
automatically generated byinfo2html