7.2 Column typesMySQL supports a number of column types, which may be grouped into three categories: numeric types, date and time types, and string (character) types. This section first gives an overview of the types available and summarizes the storage requirements for each column type, then provides a more detailed description of the properties of the types in each category. The overview is intentionally brief. The more detailed descriptions should be consulted for additional information about particular column types, such as the allowable formats in which you can specify values. The column types supported by MySQL are listed below. The following code letters are used in the descriptions:
Square brackets (`[' and `]') indicate parts of type specifiers that are optional.
Note that if you specify
7.2.1 Column type storage requirementsThe storage requirements for each of the column types supported by MySQL are listed below by category. 7.2.2 Numeric types
7.2.3 Date and time types
7.2.4 String types
The If a table includes any variable-length column types, the record format will also be variable-length. Note that when a table is created, MySQL may under certain conditions change a column from a variable-length type to a fixed-length type, or vice-versa. 7.6.1 Silent column specification changes.
The size of an
The size of a 7.2.5 Numeric types
All integer types can have an optional attribute
All numeric types can have an optional attribute When asked to store a value in a numeric column that is outside the column type's allowable range, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead.
For example, the range of an
If the
Conversions that occur due to clipping are reported as ``warnings'' for
The maximum display size (
MySQL will store any value that fits a column's storage type
even if the value exceeds the display size. For example, an
The
The maximum range of
To avoid some rounding problems, MySQL always rounds everything that
it stores in any floating-point column to the number of decimals indicated by
the column specification. Suppose you have a column type of
7.2.6 Date and time types
The date and time types are Here are some general considerations to keep in mind when working with date and time types:
7.2.6.1 Y2K issues and date typesMySQL itself is Y2K-safe ( 1.6 Year 2000 compliance), but input values presented to MySQL may not be. Any input containing 2-digit year values is ambiguous, since the century is unknown. Such values must be interpreted into 4-digit form since MySQL stores years internally using four digits.
For
Remember that these rules provide only reasonable guesses as to what your data mean. If the heuristics used by MySQL don't produce the correct values, you should provide unambiguous input containing 4-digit year values. 7.2.6.2 The
|
Column type | Display format |
TIMESTAMP(14) | YYYYMMDDHHMMSS
|
TIMESTAMP(12) | YYMMDDHHMMSS
|
TIMESTAMP(10) | YYMMDDHHMM
|
TIMESTAMP(8) | YYYYMMDD
|
TIMESTAMP(6) | YYMMDD
|
TIMESTAMP(4) | YYMM
|
TIMESTAMP(2) | YY
|
All TIMESTAMP
columns have the same storage size, regardless of
display size. The most common display sizes are 6, 8, 12, and 14. You can
specify an arbitrary display size at table creation time, but values of 0 or
greater than 14 are coerced to 14. Odd-valued sizes in the range from 1 to
13 are coerced to the next higher even number.
You can specify DATETIME
, DATE
and TIMESTAMP
values using
any of a common set of formats:
'YYYY-MM-DD HH:MM:SS'
or 'YY-MM-DD
HH:MM:SS'
format. A ``relaxed'' syntax is allowed--any punctuation
character may be used as the delimiter between date parts or time parts.
For example, '98-12-31 11:30:45'
, '98.12.31 11+30+45'
,
'98/12/31 11*30*45'
and '98@12@31 11^30^45'
are
equivalent.
'YYYY-MM-DD'
or 'YY-MM-DD'
format.
A ``relaxed'' syntax is allowed here, too. For example, '98-12-31'
,
'98.12.31'
, '98/12/31'
and '98@12@31'
are
equivalent.
'YYYYMMDDHHMMSS'
or
'YYMMDDHHMMSS'
format, provided that the string makes sense as a
date. For example, '19970523091528'
and '970523091528'
are
interpreted as '1997-05-23 09:15:28'
, but '971122459015'
is
illegal (it has a nonsensical minute part) and becomes '0000-00-00
00:00:00'
.
'YYYYMMDD'
or 'YYMMDD'
format, provided that the string makes sense as a date. For example,
'19970523'
and '970523'
are interpreted as
'1997-05-23'
, but '971332'
is illegal (it has nonsensical month
and day parts) and becomes '0000-00-00'
.
YYYYMMDDHHMMSS
or YYMMDDHHMMSS
format, provided that the number makes sense as a date. For example,
19830905132800
and 830905132800
are interpreted as
'1983-09-05 13:28:00'
.
YYYYMMDD
or YYMMDD
format, provided that the number makes sense as a date. For example,
19830905
and 830905
are interpreted as '1983-09-05'
.
DATETIME
, DATE
or TIMESTAMP
context, such as
NOW()
or CURRENT_DATE
.
Illegal DATETIME
, DATE
or TIMESTAMP
values are converted
to the ``zero'' value of the appropriate type ('0000-00-00 00:00:00'
,
'0000-00-00'
or 00000000000000
).
For values specified as strings that include date part delimiters, it is not
necessary to specify two digits for month or day values that are less than
10
. '1979-6-9'
is the same as '1979-06-09'
. Similarly,
for values specified as strings that include time part delimiters, it is not
necessary to specify two digits for hour, month or second values that are
less than 10
. '1979-10-30 1:2:3'
is the same as
'1979-10-30 01:02:03'
.
Values specified as numbers should be 6, 8, 12 or 14 digits long. If the
number is 8 or 14 digits long, it is assumed to be in YYYYMMDD
or
YYYYMMDDHHMMSS
format and that the year is given by the first 4
digits. If the number is 6 or 12 digits long, it is assumed to be in
YYMMDD
or YYMMDDHHMMSS
format and that the year is given by the
first 2 digits. Numbers that are not one of these lengths are interpreted
as though padded with leading zeros to the closest length.
Values specified as non-delimited strings are interpreted using their length
as given. If the string is 8 or 14 characters long, the year is assumed to
be given by the first 4 characters. Otherwise the year is assumed to be
given by the first 2 characters. The string is interpreted from left to
right to find year, month, day, hour, minute and second values, for as many
parts as are present in the string. This means you should not use strings
that have fewer than 6 characters. For example, if you specify '9903'
,
thinking that will represent March, 1999, you will find that MySQL
inserts a ``zero'' date into your table. This is because the year and month
values are 99
and 03
, but the day part is missing (zero), so
the value is not a legal date.
TIMESTAMP
columns store legal values using the full precision with
which the value was specified, regardless of the display size. This has
several implications:
TIMESTAMP(4)
or TIMESTAMP(2)
. Otherwise, the value will not
be a legal date and 0
will be stored.
ALTER TABLE
to widen a narrow TIMESTAMP
column,
information will be displayed that previously was ``hidden''.
TIMESTAMP
column does not cause information to
be lost, except in the sense that less information is shown when the values
are displayed.
TIMESTAMP
values are stored to full precision, the only
function that operates directly on the underlying stored value is
UNIX_TIMESTAMP()
. Other functions operate on the formatted retrieved
value. This means you cannot use functions such as HOUR()
or
SECOND()
unless the relevant part of the TIMESTAMP
value is
included in the formatted value. For example, the HH
part of a
TIMESTAMP
column is not displayed unless the display size is at least
10, so trying to use HOUR()
on shorter TIMESTAMP
values
produces a meaningless result.
You can to some extent assign values of one date type to an object of a different date type. However, there may be some alteration of the value or loss of information:
DATE
value to a DATETIME
or TIMESTAMP
object, the time part of the resulting value is set to '00:00:00'
,
because the DATE
value contains no time information.
DATETIME
or TIMESTAMP
value to a DATE
object, the time part of the resulting value is deleted, because the
DATE
type stores no time information.
DATETIME
, DATE
and TIMESTAMP
values all can be specified using the same set of formats, the types do not
all have the same range of values. For example, TIMESTAMP
values
cannot be earlier than 1970
or later than 2037
. This means
that a date such as '1968-01-01'
, while legal as a DATETIME
or
DATE
value, is not a valid TIMESTAMP
value and will be
converted to 0
if assigned to such an object.
Be aware of certain pitfalls when specifying date values:
'10:11:12'
might look like a time value
because of the `:' delimiter, but if used in a date context will be
interpreted as the year '2010-11-12'
. The value '10:45:15'
will be converted to '0000-00-00'
because '45'
is not a legal
month.
00-69
are converted to 2000-2069
.
70-99
are converted to 1970-1999
.
TIME
type
MySQL retrieves and displays TIME
values in 'HH:MM:SS'
format (or 'HHH:MM:SS'
format for large hours values). TIME
values may range from '-838:59:59'
to '838:59:59'
. The reason
the hours part may be so large is that the TIME
type may be used not
only to represent a time of day (which must be less than 24 hours), but also
elapsed time or a time interval between two events (which may be much greater
than 24 hours, or even negative).
You can specify TIME
values in a variety of formats:
'HH:MM:SS'
format.
A ``relaxed'' syntax is allowed--any punctuation character may be
used as the delimiter between time parts. For example, '10:11:12'
and '10.11.12'
are equivalent.
'HHMMSS'
format, provided that
it makes sense as a time. For example, '101112'
is understood as
'10:11:12'
, but '109712'
is illegal (it has a nonsensical
minute part) and becomes '00:00:00'
.
HHMMSS
format, provided that it makes sense as a time.
For example, 101112
is understood as '10:11:12'
.
TIME
context, such as CURRENT_TIME
.
For TIME
values specified as strings that include a time part
delimiter, it is not necessary to specify two digits for hours, minutes or
seconds values that are less than 10
. '8:3:2'
is the same as
'08:03:02'
.
Be careful about assigning ``short'' TIME
values to a TIME
column. MySQL interprets values using the assumption that the
rightmost digits represent seconds. (MySQL interprets TIME
values as elapsed time, rather than as time of day.) For example, you might
think of '11:12'
, '1112'
and 1112
as meaning
'11:12:00'
(12 minutes after 11 o'clock), but MySQL
interprets them as '00:11:12'
(11 minutes, 12 seconds). Similarly,
'12'
and 12
are interpreted as '00:00:12'
.
Values that lie outside the TIME
range
but are otherwise legal are clipped to the appropriate
endpoint of the range. For example, '-850:00:00'
and
'850:00:00'
are converted to '-838:59:59'
and
'838:59:59'
.
Illegal TIME
values are converted to '00:00:00'
. Note that
since '00:00:00'
is itself a legal TIME
value, there is no way
to tell, from a value of '00:00:00'
stored in a table, whether the
original value was specified as '00:00:00'
or whether it was illegal.
YEAR
type
The YEAR
type is a 1-byte type used for representing years.
MySQL retrieves and displays YEAR
values in YYYY
format. The range is 1901
to 2155
.
You can specify YEAR
values in a variety of formats:
'1901'
to '2155'
.
1901
to 2155
.
'00'
to '99'
. Values in the
ranges '00'
to '69'
and '70'
to '99'
are
converted to YEAR
values in the ranges 2000
to 2069
and
1970
to 1999
.
1
to 99
. Values in the
ranges 1
to 69
and 70
to 99
are converted to
YEAR
values in the ranges 2001
to 2069
and 1970
to 1999
. Note that the range for two-digit numbers is slightly
different than the range for two-digit strings, since you cannot specify zero
directly as a number and have it be interpreted as 2000
. You
must specify it as a string '0'
or '00'
or it will be
interpreted as 0000
.
YEAR
context, such as NOW()
.
Illegal YEAR
values are converted to 0000
.
The string types are CHAR
, VARCHAR
, BLOB
, TEXT
,
ENUM
and SET
.
CHAR
and VARCHAR
types
The CHAR
and VARCHAR
types are similar, but differ in the
way they are stored and retrieved.
The length of a CHAR
column is fixed to the length that you declare
when you create the table. The length can be any value between 1 and 255.
(As of MySQL 3.23, the length of CHAR
may be 0 to 255.)
When CHAR
values are stored, they are right-padded with spaces to the
specified length. When CHAR
values are retrieved, trailing spaces are
removed.
Values in VARCHAR
columns are variable-length strings. You can
declare a VARCHAR
column to be any length between 1 and 255, just as
for CHAR
columns. However, in contrast to CHAR
, VARCHAR
values are stored using only as many characters as are needed, plus one byte
to record the length. Values are not padded; instead, trailing spaces are
removed when values are stored. (This space removal differs from the ANSI
SQL specification.)
If you assign a value to a CHAR
or VARCHAR
column that
exceeds the column's maximum length, the value is truncated to fit.
The table below illustrates the differences between the two types of columns
by showing the result of storing various string values into CHAR(4)
and VARCHAR(4)
columns:
Value | CHAR(4) | Storage required | VARCHAR(4) | Storage required |
'' | ' ' | 4 bytes | '' | 1 byte |
'ab' | 'ab ' | 4 bytes | 'ab' | 3 bytes |
'abcd' | 'abcd' | 4 bytes | 'abcd' | 5 bytes |
'abcdefgh' | 'abcd' | 4 bytes | 'abcd' | 5 bytes |
The values retrieved from the CHAR(4)
and VARCHAR(4)
columns
will be the same in each case, because trailing spaces are removed from
CHAR
columns upon retrieval.
Values in CHAR
and VARCHAR
columns are sorted and compared in
case-insensitive fashion, unless the BINARY
attribute was specified
when the table was created. The BINARY
attribute means that column
values are sorted and compared in case-sensitive fashion according to the
ASCII order of the machine where the MySQL server is running.
The BINARY
attribute is ``sticky''. This means that if a column marked
BINARY
is used in an expression, the whole expression is compared as a
BINARY
value.
MySQL may silently change the type of a CHAR
or VARCHAR
column at table creation time.
7.6.1 Silent column specification changes.
BLOB
and TEXT
types
A BLOB
is a binary large object that can hold a variable amount of
data. The four BLOB
types TINYBLOB
, BLOB
,
MEDIUMBLOB
and LONGBLOB
differ only in the maximum length of
the values they can hold.
7.2.1 Column type storage requirements.
The four TEXT
types TINYTEXT
, TEXT
, MEDIUMTEXT
and LONGTEXT
correspond to the four BLOB
types and have the
same maximum lengths and storage requirements. The only difference between
BLOB
and TEXT
types is that sorting and comparison is performed
in case-sensitive fashion for BLOB
values and case-insensitive fashion
for TEXT
values. In other words, a TEXT
is a case-insensitive
BLOB
.
If you assign a value to a BLOB
or TEXT
column that exceeds
the column type's maximum length, the value is truncated to fit.
In most respects, you can regard a TEXT
column as a VARCHAR
column that can be as big as you like. Similarly, you can regard a
BLOB
column as a VARCHAR BINARY
column. The differences are:
BLOB
and TEXT
columns with
MySQL versions 3.23.2 and newer. Older versions of
MySQL did not support this.
BLOB
and TEXT
columns
when values are stored, as there is for VARCHAR
columns.
BLOB
and TEXT
columns cannot have DEFAULT
values.
MyODBC defines BLOB
values as LONGVARBINARY
and
TEXT
values as LONGVARCHAR
.
Because BLOB
and TEXT
values may be extremely long, you
may run up against some constraints when using them:
GROUP BY
or ORDER BY
on a BLOB
or
TEXT
column, you must convert the column value into a fixed-length
object. The standard way to do this is with the SUBSTRING
function. For example:
mysql> select comment from tbl_name,substring(comment,20) as substr ORDER BY substr;If you don't do this, only the first
max_sort_length
bytes of the
column are used when sorting. The default value of max_sort_length
is
1024; this value can be changed using the -O
option when starting the
mysqld
server. You can group on an expression involving BLOB
or
TEXT
values by specifying the column position or by using an alias:
mysql> select id,substring(blob_col,1,100) from tbl_name GROUP BY 2; mysql> select id,substring(blob_col,1,100) as b from tbl_name GROUP BY b;
BLOB
or TEXT
object is determined by its
type, but the largest value you can actually transmit between the client and
server is determined by the amount of available memory and the size of the
communications buffers. You can change the message buffer size, but you must
do so on both the server and client ends. 10.1 Tuning server parameters.
Note that each BLOB
or TEXT
value is represented internally by
a separately-allocated object. This is in contrast to all other column types,
for which storage is allocated once per column when the table is opened.
ENUM
type
An ENUM
is a string object whose value normally is chosen from a list
of allowed values that are enumerated explicitly in the column specification
at table creation time.
The value may also be the empty string (""
) or NULL
under
certain circumstances:
ENUM
(that is, a string not
present in the list of allowed values), the empty string is inserted
instead as a special error value.
ENUM
is declared NULL
, NULL
is also a legal value
for the column, and the default value is NULL
. If an ENUM
is
declared NOT NULL
, the default value is the first element of the
list of allowed values.
Each enumeration value has an index:
SELECT
statement to find rows into which invalid
ENUM
values were assigned:
mysql> SELECT * FROM tbl_name WHERE enum_col=0;
NULL
value is NULL
.
For example, a column specified as ENUM("one", "two", "three")
can
have any of the values shown below. The index of each value is also shown:
Value | Index |
NULL | NULL
|
"" | 0 |
"one" | 1 |
"two" | 2 |
"three" | 3 |
An enumeration can have a maximum of 65535 elements.
Lettercase is irrelevant when you assign values to an ENUM
column.
However, values retrieved from the column later have lettercase matching the
values that were used to specify the allowable values at table creation time.
If you retrieve an ENUM
in a numeric context, the column value's index
is returned. If you store a number into an ENUM
, the number is
treated as an index, and the the value stored is the enumeration member with
that index.
ENUM
values are sorted according to the order in which the enumeration
members were listed in the column specification. (In other words,
ENUM
values are sorted according to their index numbers.) For
example, "a"
sorts before "b"
for ENUM("a", "b")
, but
"b"
sorts before "a"
for ENUM("b", "a")
. The empty
string sorts before non-empty strings, and NULL
values sort before
all other enumeration values.
If you want to get all possible values for an ENUM
column, you should
use: SHOW COLUMNS FROM table_name LIKE enum_column_name
and parse
the ENUM
definition in the second column.
SET
type
A SET
is a string object that can have zero or more values, each of
which must be chosen from a list of allowed values specified when the table
is created. SET
column values that consist of multiple set members
are specified with members separated by commas (`,'). A consequence of
this is that SET
member values cannot themselves contain commas.
For example, a column specified as SET("one", "two") NOT NULL
can have
any of these values:
"" "one" "two" "one,two"
A SET
can have a maximum of 64 different members.
MySQL stores SET
values numerically, with the low-order bit
of the stored value corresponding to the first set member. If you retrieve a
SET
value in a numeric context, the value retrieved has bits set
corresponding to the set members that make up the column value. If a number
is stored into a SET
column, the bits that are set in the binary
representation of the number determine the set members in the column value.
Suppose a column is specified as SET("a","b","c","d")
. Then the
members have the following bit values:
SET member | Decimal value | Binary value |
a | 1 | 0001
|
b | 2 | 0010
|
c | 4 | 0100
|
d | 8 | 1000
|
If you assign a value of 9
to this column, that is 1001
in
binary, so the first and fourth SET
value members "a"
and
"d"
are selected and the resulting value is "a,d"
.
For a value containing more than one SET
element, it does not matter
what order the elements are listed in when you insert the value. It also
doesn't not matter how many times a given element is listed in the value.
When the value is retrieved later, each element in the value will appear
once, with elements listed according to the order in which they were
specified at table creation time. For example, if a column is specified as
SET("a","b","c","d")
, then "a,d"
, "d,a"
and
"d,a,a,d,d"
will all appear as "a,d"
when retrieved.
SET
values are sorted numerically. NULL
values sort before
non-NULL
SET
values.
Normally, you perform a SELECT
on a SET
column using
the LIKE
operator or the FIND_IN_SET()
function:
mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%'; mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
But the following will also work:
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2'; mysql> SELECT * FROM tbl_name WHERE set_col & 1;
The first of these statements looks for an exact match. The second looks for values containing the first set member.
If you want to get all possible values for an SET
column, you should
use: SHOW COLUMNS FROM table_name LIKE set_column_name
and parse
the SET
definition in the second column.
For the most efficient use of storage, try to use the most precise type in
all cases. For example, if an integer column will be used for values in the
range between 1
and 99999
, MEDIUMINT UNSIGNED
is the
best type.
Accurate representation of monetary values is a common problem. In
MySQL, you should use the DECIMAL
type. This is stored as
a string, so no loss of accuracy should occur. If accuracy is not
too important, the DOUBLE
type may also be good enough.
For high precision, you can always convert to a fixed-point type
stored in a BIGINT
. This allows you to do all calculations with
integers and convert results back to floating-point values only when necessary.
10.17 What are the different row formats? Or, when should VARCHAR/CHAR
be used?.
All MySQL column types can be indexed. Use of indexes on the
relevant columns is the best way to improve the performance of SELECT
operations.
A table may have up to 16 indexes. The maximum index length is 256 bytes, although this may be changed when compiling MySQL.
For CHAR
and VARCHAR
columns, you can index a prefix of a
column. This is much faster and requires less disk space than indexing the
whole column. The syntax to use in the CREATE TABLE
statement to
index a column prefix looks like this:
KEY index_name (col_name(length))
The example below creates an index for the first 10 characters of the
name
column:
mysql> CREATE TABLE test ( name CHAR(200) NOT NULL, KEY index_name (name(10)));
For BLOB
and TEXT
columns, you must index a prefix of the
column, you cannot index the entire thing.
MySQL can create indexes on multiple columns. An index may
consist of up to 15 columns. (On CHAR
and VARCHAR
columns you
can also use a prefix of the column as a part of an index).
A multiple-column index can be considered a sorted array containing values that are created by concatenating the values of the indexed columns.
MySQL uses multiple-column indexes in such a way that queries are
fast when you specify a known quantity for the first column of the index in a
WHERE
clause, even if you don't specify values for the other columns.
Suppose a table is created using the following specification:
mysql> CREATE TABLE test ( id INT NOT NULL, last_name CHAR(30) NOT NULL, first_name CHAR(30) NOT NULL, PRIMARY KEY (id), INDEX name (last_name,first_name));
Then the index name
is an index over last_name
and
first_name
. The index will be used for queries that specify
values in a known range for last_name
, or for both last_name
and first_name
.
Therefore, the name
index will be used in the following queries:
mysql> SELECT * FROM test WHERE last_name="Widenius"; mysql> SELECT * FROM test WHERE last_name="Widenius" AND first_name="Michael"; mysql> SELECT * FROM test WHERE last_name="Widenius" AND (first_name="Michael" OR first_name="Monty"); mysql> SELECT * FROM test WHERE last_name="Widenius" AND first_name >="M" AND first_name < "N";
However, the name
index will NOT be used in the following queries:
mysql> SELECT * FROM test WHERE first_name="Michael"; mysql> SELECT * FROM test WHERE last_name="Widenius" OR first_name="Michael";
For more information on the manner in which MySQL uses indexes to improve query performance, see MySQL indexes.
To make it easier to use code written for SQL implementations from other vendors, MySQL maps column types as shown in the table below. These mappings make it easier to move table definitions from other database engines to MySQL:
Other vendor type | MySQL type |
BINARY(NUM) | CHAR(NUM) BINARY
|
CHAR VARYING(NUM) | VARCHAR(NUM)
|
FLOAT4 | FLOAT
|
FLOAT8 | DOUBLE
|
INT1 | TINYINT
|
INT2 | SMALLINT
|
INT3 | MEDIUMINT
|
INT4 | INT
|
INT8 | BIGINT
|
LONG VARBINARY | MEDIUMBLOB
|
LONG VARCHAR | MEDIUMTEXT
|
MIDDLEINT | MEDIUMINT
|
VARBINARY(NUM) | VARCHAR(NUM) BINARY
|
Column type mapping occurs at table creation time. If you create a table
with types used by other vendors and then issue a DESCRIBE tbl_name
statement, MySQL reports the table structure using the equivalent
MySQL types.