A.3. String Data Types
There are several column data types for storing strings. String
data types are case-sensitive, so lowercase and uppercase letters remain
unchanged when stored or retrieved. For a few of the string data types,
you may specify a maximum column width. If a string is entered in a column
that exceeds the width set for the column, the string will be
right-truncated when stored. Binary strings are case-sensitive.
Following is a list of string data types. They're not organized
alphabetically; instead, they're organized in ascending order based on the
size of string values they can contain. This list also includes the width
in characters or bytes for each data type.
CHAR(width) [BINARY|ASCII|UNICODE] [CHARACTER SET character_set]
[COLLATE collation] | |
The CHAR data type is a fixed-width column, padded with spaces to the right as
needed. The spaces are not included in the results when queried. This
column may be from 0 to 255 characters wide. The default if no width
is given is 1. This type is synonymous with
CHARACTER. You can also use NATIONAL
CHARACTER or NCHAR to indicate that a
predefined national character set is to be used. Columns are
right-padded with spaces when stored. FULLTEXT
indexing and searching may be performed on a CHAR
column with a MyISAM table.
As of version 4.1 of MySQL, you can specify the
ASCII attribute for use with the
CHAR data type. This will set the column to the
latin1 character set. Also as of version 4.1 of
MySQL, you can specify the UNICODE attribute, which
will set the column to the ucs2 character
set.
The VARCHAR data type adjusts its width and does not pad the strings stored.
Any trailing spaces contained in a string that is stored are removed.
This column may be from 0 to 65,535 characters wide. The type of
character set given can affect the number of characters given, as some
require more than one byte per character (e.g., UTF-8). The default if
no width is given is 1. This type is synonymous with
CHARACTER VARYING. You can also use
NATIONAL VARCHAR to indicate that a predefined
national character set is to be used. FULLTEXT
indexing and searching may be performed on a
VARCHAR column with a MyISAM table.
This data type stores data as binary strings, not as character strings
like CHAR does. The width given is for the maximum
width in bytes—this value must be specified. This data type replaces
CHAR BINARY. Before version 4.1.2 of MySQL, if you
added the BINARY flag after
CHAR, it instructed MySQL to treat the values as
byte strings for sorting and comparing. If a BINARY
column is used in an expression, all elements of the expression are
treated as binary.
VARBINARY(width) [CHARACTER SET character_set] [COLLATE collation] | |
This data type stores data as binary strings, not as
character strings like VARCHAR. The width given is
for the maximum width in bytes—this value must be specified. If you
want to use a character set for the column other than the default for
the table, you can give one for the column. Values are sorted based on
the collation of the character set for the column. This data type
replaces VARCHAR BINARY. Before version 4.1.2 of
MySQL, if you added the BINARY flag after
VARCHAR, it instructed MySQL to treat the values as
byte strings for sorting and comparing. All elements of the expression
are then treated as binary.
This column data type allows for the storage of binary data.
The maximum width is 255 bytes.
TINYTEXT[CHARACTER SET character_set] [COLLATE collation] | |
This column data type allows for the storage of text data.
The maximum width is 255 bytes. If you want to use a character set for
the column other than the default for the table, you can give one for
the column. Values are sorted based on the collation of the character
set for the column.
This column data type allows for the storage of a large
amount of binary data. You may give a width with this data type; the
maximum is 65,535 bytes. If you attempt to store a value in a
BLOB column that is larger than its limit, unless
the server is set to SQL strict mode, the data will be truncated and a
warning message will be generated. If strict mode is on, the data will
be rejected and an error will be returned. A BLOB
column cannot have a default value. For sorting data, the value given
for the system variable max_sort_length will be
used. Only the number of bytes specified by that variable for each
column will be included in sorts.
TEXT[(width)] [CHARACTER SET character_set] [COLLATE collation] | |
This column data type allows for the storage of a large amount of
text data. You may give a width with this data type; the maximum is
65,535 bytes. If you attempt to store a value in a
TEXT column that is larger than its limit, unless
the server is set to SQL strict mode, the data will be truncated and a
warning message will be generated. If strict mode is on, the data will
be rejected and an error will be returned. A TEXT
column cannot have a default value. For sorting data, the value given
for the system variable max_sort_length will be
used. Only the number of bytes specified by that variable for each
column will be included in sorts. FULLTEXT indexing
and searching may be performed on a TEXT column
with a MyISAM table, but not on a BLOB column. If
you want to use a character set for the column other than the default
for the table, you can give one for the column. Values are sorted
based on the collation of the character set for the column.
This column data type allows for the storage of a large
amount of binary data. The maximum width is 16,777,215 bytes.
MEDIUMTEXT [CHARACTER SET character_set] [COLLATE collation] | |
This column data type allows for the storage of a large
amount of text data. The maximum width is 16,777,215 bytes. If you
want to use a character set for the column other than the default for
the table, you can give one for the column. Values are sorted based on
the collation of the character set for the column.
This column data type allows for the storage of a large
amount of binary data. The maximum width is 4 GB.
LONGTEXT [CHARACTER SET character_set] [COLLATE collation] | |
This column data type allows for the storage of a large amount of
text data. The maximum width is 4 GB. If you want to use a character
set for the column other than the default for the table, you can give
one for the column. Values are sorted based on the collation of the
character set for the column.
ENUM('value', ...) [CHARACTER SET character_set] [COLLATE collation] | |
An ENUM column is one in which all possible choices are enumerated
(e.g., ENUM('yes', 'no', 'maybe')). It's possible
for it to contain a blank value (i.e., '') and NULL. If an
ENUM column is set up to allow NULL values, NULL
will be the default value. If an ENUM column is set
up with NOT NULL, NULL isn't allowed and the
default value becomes the first element given.
MySQL stores a numeric index of the enumerated values in the
column, 1 being the first value. The values can be retrieved when the
column is used in a numeric context (e.g., SELECT col1 + 0
FROM table1;). The reverse may be performed when entering
data into a column (e.g., UPDATE table1 SET col1 =
3; to set the value to the third element). The column values
are sorted in ascending order based on the numeric index, not on their
corresponding enumerated values. If you want to use a character set
for the column other than the default for the table, you can give one
for the column. Values are sorted based on the collation of the
character set for the column.
SET('value', ...) [CHARACTER SET character_set] [COLLATE collation] | |
The SET data type is similar to ENUM, except that
a SET column can hold multiple values (e.g.,
UPDATE table1 SET col1 = 'a, b';). For this data
type, values may be filtered with the
FIND_IN_SET( ) function. If you want to use a
character set for the column other than the default for the table, you
can give one for the column. Values are sorted based on the collation
of the character set for the column.
No comments:
Post a Comment