MySQL
offers a wide
variety of data types to support the storage of different kinds of
data. This chapter lists the full range of these data types and
describes their functionality, syntax, and data storage requirements.
For each data type, the syntax shown uses square brackets
([]
) to indicate optional parts of the syntax. The
following example shows how BIGINT
is explained in
this chapter:
BIGINT[(display_size)]
This indicates that you can use BIGINT
alone or
with a display size value. The italics indicate that you do not enter
display_size
literally, but instead enter your own
value. Thus, possible uses of BIGINT
include:
BIGINT BIGINT(20)
Like the BIGINT
type above, many MySQL data types
support the specification of a display size. Unless otherwise
specified, this value must be an integer between 1 and 255.
Table 16-1 lists the data types and categorizes them as numeric, string, date, or complex. You can find the full description of each data type later in this chapter.
Data type |
Classification |
|
Numeric |
|
String |
|
String |
|
String |
|
String |
|
Date |
|
Date |
|
Numeric |
|
Numeric |
|
Numeric |
|
Numeric |
|
Complex |
|
Numeric |
|
Numeric |
|
Numeric |
|
String |
|
String |
|
String |
|
Numeric |
|
String |
|
String |
|
String |
|
String |
|
String |
|
Numeric |
|
Numeric |
|
Complex |
|
Numeric |
|
String |
|
Date |
|
Date |
|
String |
|
Numeric |
|
String |
|
String |
|
Date |
In the following cases, MySQL silently changes the column type you specify in your table creation to something else:
VARCHAR
-> CHAR
When the specified VARCHAR
column size is less
than four characters, it is converted to CHAR
.
CHAR
-> VARCHAR
When a table has at least one column of a variable length, all
CHAR
columns greater than three characters in
length are converted to VARCHAR
.
TIMESTAMP
display sizes
Display sizes for TIMESTAMP
fields must be an even
value between 2 and 14. A display size of 0 or greater than 14 will
convert the field to a display size of 14. An odd-valued display size
will be converted to the next highest even value.