Table of Contents
MySQL supports a number of data types in several categories: numeric types, date and time types, and string (character and byte) types. This chapter provides an overview of these data types, a more detailed description of the properties of the types in each category, and a summary of the data type storage requirements. The initial overview is intentionally brief. The more detailed descriptions later in the chapter should be consulted for additional information about particular data types, such as the permissible formats in which you can specify values.
MySQL 4.1 and up also supports extensions for handing spatial data. For information about these data types, see Chapter 16, Spatial Extensions.
Data type descriptions use these conventions:
M
indicates the maximum display width
for integer types. For floating-point and fixed-point types,
M
is the total number of digits that
can be stored (the precision). For string types,
M
is the maximum length. The maximum
permissible value of M
depends on the
data type.
D
applies to floating-point and
fixed-point types and indicates the number of digits following
the decimal point (the scale). The maximum possible value is 30,
but should be no greater than
M
–2.
Square brackets (“[
” and
“]
”) indicate optional parts of
type definitions.
A summary of the numeric data types follows. For additional information about properties and storage requirements of the numeric types, see Section 10.2, “Numeric Types”, and Section 10.5, “Data Type Storage Requirements”.
M
indicates the maximum display width
for integer types. The maximum legal display width is 255.
Display width is unrelated to the range of values a type can
contain, as described in Section 10.2, “Numeric Types”. For
floating-point and fixed-point types,
M
is the total number of digits that
can be stored.
If you specify ZEROFILL
for a numeric column,
MySQL automatically adds the UNSIGNED
attribute to the column.
Numeric data types that permit the UNSIGNED
attribute also permit SIGNED
. However, these
data types are signed by default, so the
SIGNED
attribute has no effect.
As of MySQL 4.1, SERIAL
is an alias for
BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
UNIQUE
.
SERIAL DEFAULT VALUE
in the definition of an
integer column is an alias for NOT NULL AUTO_INCREMENT
UNIQUE
.
When you use subtraction between integer values where one is
of type UNSIGNED
, the result is unsigned
unless the
NO_UNSIGNED_SUBTRACTION
SQL
mode is enabled. See Section 11.10, “Cast Functions and Operators”.
In versions of MySQL up to and lincluding 4.1,
BIT
is a synonym for
TINYINT(1)
.
TINYINT[(
M
)]
[UNSIGNED] [ZEROFILL]
A very small integer. The signed range is
-128
to 127
. The
unsigned range is 0
to
255
.
These types are synonyms for TINYINT(1)
.
The synonym BOOLEAN
was added
in MySQL 4.1.0. A value of zero is considered false. Nonzero
values are considered true:
mysql>SELECT IF(0, 'true', 'false');
+------------------------+ | IF(0, 'true', 'false') | +------------------------+ | false | +------------------------+ mysql>SELECT IF(1, 'true', 'false');
+------------------------+ | IF(1, 'true', 'false') | +------------------------+ | true | +------------------------+ mysql>SELECT IF(2, 'true', 'false');
+------------------------+ | IF(2, 'true', 'false') | +------------------------+ | true | +------------------------+
However, the values TRUE
and
FALSE
are merely aliases for
1
and 0
, respectively,
as shown here:
mysql>SELECT IF(0 = FALSE, 'true', 'false');
+--------------------------------+ | IF(0 = FALSE, 'true', 'false') | +--------------------------------+ | true | +--------------------------------+ mysql>SELECT IF(1 = TRUE, 'true', 'false');
+-------------------------------+ | IF(1 = TRUE, 'true', 'false') | +-------------------------------+ | true | +-------------------------------+ mysql>SELECT IF(2 = TRUE, 'true', 'false');
+-------------------------------+ | IF(2 = TRUE, 'true', 'false') | +-------------------------------+ | false | +-------------------------------+ mysql>SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+ | IF(2 = FALSE, 'true', 'false') | +--------------------------------+ | false | +--------------------------------+
The last two statements display the results shown because
2
is equal to neither
1
nor 0
.
SMALLINT[(
M
)]
[UNSIGNED] [ZEROFILL]
A small integer. The signed range is
-32768
to 32767
. The
unsigned range is 0
to
65535
.
MEDIUMINT[(
M
)]
[UNSIGNED] [ZEROFILL]
A medium-sized integer. The signed range is
-8388608
to 8388607
.
The unsigned range is 0
to
16777215
.
INT[(
M
)]
[UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is
-2147483648
to
2147483647
. The unsigned range is
0
to 4294967295
.
INTEGER[(
M
)]
[UNSIGNED] [ZEROFILL]
This type is a synonym for
INT
.
BIGINT[(
M
)]
[UNSIGNED] [ZEROFILL]
A large integer. The signed range is
-9223372036854775808
to
9223372036854775807
. The unsigned range
is 0
to
18446744073709551615
.
As of MySQL 4.1, SERIAL
is an alias for
BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
UNIQUE
.
Some things you should be aware of with respect to
BIGINT
columns:
All arithmetic is done using signed
BIGINT
or
DOUBLE
values, so you
should not use unsigned big integers larger than
9223372036854775807
(63 bits) except
with bit functions! If you do that, some of the last
digits in the result may be wrong because of rounding
errors when converting a
BIGINT
value to a
DOUBLE
.
MySQL 4.0 can handle
BIGINT
in the following
cases:
When using integers to store large unsigned values
in a BIGINT
column.
In
MIN(
or
col_name
)MAX(
,
where col_name
)col_name
refers to
a BIGINT
column.
When using operators
(+
,
-
,
*
,
and so on) where both operands are integers.
You can always store an exact integer value in a
BIGINT
column by storing
it using a string. In this case, MySQL performs a
string-to-number conversion that involves no
intermediate double-precision representation.
The -
,
+
, and
*
operators use BIGINT
arithmetic when both operands are integer values. This
means that if you multiply two big integers (or results
from functions that return integers), you may get
unexpected results when the result is larger than
9223372036854775807
.
DECIMAL[(
M
[,D
])]
[UNSIGNED] [ZEROFILL]
An unpacked fixed-point number. Behaves like a
CHAR
column;
“unpacked” means the number is stored as a
string, using one character for each digit of the value.
M
is the total number of digits
and D
is the number of digits
after the decimal point. The decimal point and (for negative
numbers) the “-
” sign are
not counted in M
, although space
for them is reserved. If D
is 0,
values have no decimal point or fractional part. The maximum
range of DECIMAL
values is
the same as for DOUBLE
, but
the actual range for a given
DECIMAL
column may be
constrained by the choice of M
and D
. If
D
is omitted, the default is 0.
If M
is omitted, the default is
10.
UNSIGNED
, if specified, disallows
negative values.
Before MySQL 3.23, the value of
M
must be large enough to
include the space needed for the sign and the decimal
point characters.
DEC[(
,
M
[,D
])]
[UNSIGNED] [ZEROFILL]NUMERIC[(
,
M
[,D
])]
[UNSIGNED] [ZEROFILL]FIXED[(
M
[,D
])]
[UNSIGNED] [ZEROFILL]
These types are synonyms for
DECIMAL
. The
FIXED
synonym was added in
MySQL 4.1.0 for compatibility with other database systems.
FLOAT[(
M
,D
)]
[UNSIGNED] [ZEROFILL]
A small (single-precision) floating-point number.
Permissible values are -3.402823466E+38
to -1.175494351E-38
,
0
, and 1.175494351E-38
to 3.402823466E+38
. These are the
theoretical limits, based on the IEEE standard. The actual
range might be slightly smaller depending on your hardware
or operating system.
M
is the total number of digits
and D
is the number of digits
following the decimal point. If M
and D
are omitted, values are
stored to the limits permitted by the hardware. A
single-precision floating-point number is accurate to
approximately 7 decimal places.
UNSIGNED
, if specified, disallows
negative values.
Using FLOAT
might give you
some unexpected problems because all calculations in MySQL
are done with double precision. See
Section B.5.5.7, “Solving Problems with No Matching Rows”.
DOUBLE[(
M
,D
)]
[UNSIGNED] [ZEROFILL]
A normal-size (double-precision) floating-point number.
Permissible values are
-1.7976931348623157E+308
to
-2.2250738585072014E-308
,
0
, and
2.2250738585072014E-308
to
1.7976931348623157E+308
. These are the
theoretical limits, based on the IEEE standard. The actual
range might be slightly smaller depending on your hardware
or operating system.
M
is the total number of digits
and D
is the number of digits
following the decimal point. If M
and D
are omitted, values are
stored to the limits permitted by the hardware. A
double-precision floating-point number is accurate to
approximately 15 decimal places.
UNSIGNED
, if specified, disallows
negative values.
DOUBLE
PRECISION[(
,
M
,D
)]
[UNSIGNED] [ZEROFILL]REAL[(
M
,D
)]
[UNSIGNED] [ZEROFILL]
These types are synonyms for
DOUBLE
. Exception: If the
REAL_AS_FLOAT
SQL mode is
enabled, REAL
is a synonym
for FLOAT
rather than
DOUBLE
.
FLOAT(
p
)
[UNSIGNED] [ZEROFILL]
A floating-point number. p
represents the precision in bits, but MySQL uses this value
only to determine whether to use
FLOAT
or
DOUBLE
for the resulting data
type. If p
is from 0 to 24, the
data type becomes FLOAT
with
no M
or
D
values. If
p
is from 25 to 53, the data type
becomes DOUBLE
with no
M
or D
values. The range of the resulting column is the same as for
the single-precision FLOAT
or
double-precision DOUBLE
data
types described earlier in this section.
As of MySQL 3.23, this data type holds true floating-point
values. In earlier MySQL versions,
FLOAT(
always has two decimals.
p
)
FLOAT(
syntax is provided for ODBC compatibility.
p
)
A summary of the temporal data types follows. For additional information about properties and storage requirements of the temporal types, see Section 10.3, “Date and Time Types”, and Section 10.5, “Data Type Storage Requirements”. For descriptions of functions that operate on temporal values, see Section 11.7, “Date and Time Functions”.
For the DATE
and
DATETIME
range descriptions,
“supported” means that although earlier values
might work, there is no guarantee.
A date. The supported range is
'1000-01-01'
to
'9999-12-31'
. MySQL displays
DATE
values in
'YYYY-MM-DD'
format, but permits
assignment of values to DATE
columns using either strings or numbers.
A date and time combination. The supported range is
'1000-01-01 00:00:00'
to
'9999-12-31 23:59:59'
. MySQL displays
DATETIME
values in
'YYYY-MM-DD HH:MM:SS'
format, but permits
assignment of values to
DATETIME
columns using either
strings or numbers.
A timestamp. The range is '1970-01-01
00:00:01'
UTC to '2038-01-19
03:14:07'
UTC.
TIMESTAMP
values are stored
as the number of seconds since the epoch
('1970-01-01 00:00:00'
UTC). A
TIMESTAMP
cannot represent
the value '1970-01-01 00:00:00'
because
that is equivalent to 0 seconds from the epoch and the value
0 is reserved for representing '0000-00-00
00:00:00'
, the “zero”
TIMESTAMP
value.
MySQL displays TIMESTAMP
values in 'YYYY-MM-DD HH:MM:SS'
format.
To convert the value to a number, add +0
.
A TIMESTAMP
column is useful
for recording the date and time of an
INSERT
or
UPDATE
operation. By default,
the first TIMESTAMP
column in
a table is automatically set to the date and time of the
most recent operation if you do not assign it a value
yourself. You can also set any
TIMESTAMP
column to the
current date and time by assigning it a
NULL
value. Variations on automatic
initialization and update properties are described in
Section 10.3.1.2, “TIMESTAMP Properties as of MySQL 4.1”.
In MySQL 4.1, TIMESTAMP
is
returned as a string with the format 'YYYY-MM-DD
HH:MM:SS'
. Display widths (used as described in
the following paragraphs) are no longer supported; the
display width is fixed at 19 characters. To obtain the value
as a number, add +0
.
In MySQL 4.0 and earlier,
TIMESTAMP
values are
displayed in YYYYMMDDHHMMSS
,
YYMMDDHHMMSS
,
YYYYMMDD
, or YYMMDD
format, depending on whether M
is
14 (or missing), 12, 8, or 6, but permits you to assign
values to TIMESTAMP
columns
using either strings or numbers. The
M
argument affects only how a
TIMESTAMP
column is
displayed, not storage. Its values always are stored using
four bytes each. From MySQL 4.0.12, the
--new
option can be used to make the server
behave as in MySQL 4.1.
Note that
TIMESTAMP(
columns where M
)M
is 8 or 14 are
reported to be numbers, whereas other
TIMESTAMP(
columns are reported to be strings. This is just to ensure
that you can reliably dump and restore the table with these
types.
M
)
The behavior of TIMESTAMP
columns changed considerably in MySQL 4.1. For complete
information on the differences with regard to this data
type in MySQL 4.1 and later versions (as opposed to MySQL
4.0 and earlier versions), be sure to see
Section 10.3.1.1, “TIMESTAMP Properties Prior to MySQL 4.1”, and
Section 10.3.1.2, “TIMESTAMP Properties as of MySQL 4.1”.
A time. The range is '-838:59:59'
to
'838:59:59'
. MySQL displays
TIME
values in
'HH:MM:SS'
format, but permits assignment
of values to TIME
columns
using either strings or numbers.
A year in two-digit or four-digit format. The default is
four-digit format. In four-digit format, the permissible
values are 1901
to
2155
, and 0000
. In
two-digit format, the permissible values are
70
to 69
, representing
years from 1970 to 2069. MySQL displays
YEAR
values in
YYYY
format, but permits assignment of
values to YEAR
columns using
either strings or numbers. The
YEAR
type is unavailable
prior to MySQL 3.22.
The SUM()
and
AVG()
aggregate functions do not
work with temporal values. (They convert the values to numbers,
which loses the part after the first nonnumeric character.) To
work around this problem, convert to numeric units, perform the
aggregate operation, and convert back to a temporal value.
Examples:
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col
))) FROMtbl_name
; SELECT FROM_DAYS(SUM(TO_DAYS(date_col
))) FROMtbl_name
;
A summary of the string data types follows. For additional information about properties and storage requirements of the string types, see Section 10.4, “String Types”, and Section 10.5, “Data Type 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 Section 12.1.5.2, “Silent Column Specification Changes”.
In MySQL 4.1 and up, string data types include some features that you may not have encountered in working with versions of MySQL prior to 4.1:
As of version 4.1, MySQL interprets length specifications in
character column definitions in character units. (Before
MySQL 4.1, column lengths were interpreted in bytes.) This
applies to CHAR
,
VARCHAR
, and the
TEXT
types.
Column definitions for many string data types can include
attributes that specify the character set or collation of
the column. These attributes apply to the
CHAR
,
VARCHAR
, the
TEXT
types,
ENUM
, and
SET
data types:
The CHARACTER SET
attribute specifies
the character set, and the COLLATE
attribute specifies a collation for the character 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.
The rules for assigning the character set and collation
when either or both of the CHARACTER
SET
and COLLATE
attributes
are missing are described in
Section 9.1.3.4, “Column Character Set and Collation”.
CHARSET
is a synonym for
CHARACTER SET
.
From MySQL 4.1.2 on, specifying the CHARACTER
SET binary
attribute for a character data type
causes the column to be created as the corresponding
binary data type: CHAR
becomes BINARY
,
VARCHAR
becomes
VARBINARY
, and
TEXT
becomes
BLOB
. For the
ENUM
and
SET
data types, this does
not occur; they are created as declared. Suppose that
you specify a table using this definition:
CREATE TABLE t ( c1 VARCHAR(10) CHARACTER SET binary, c2 TEXT CHARACTER SET binary, c3 ENUM('a','b','c') CHARACTER SET binary );
The resulting table has this definition:
CREATE TABLE t ( c1 VARBINARY(10), c2 BLOB, c3 ENUM('a','b','c') CHARACTER SET binary );
From MySQL 4.1.0 on, the ASCII
attribute is shorthand for CHARACTER SET
latin1
.
From MySQL 4.1.1 on, the UNICODE
attribute is shorthand for CHARACTER SET
ucs2
.
As of MySQL 4.1.2, 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. (Before MySQL 4.1.2, BINARY
caused a column to store binary strings and sorting and
comparison were based on numeric byte values. This is
the same as using character values for single-byte
character sets, but not for multi-byte character sets.)
Character column sorting and comparison are based on the
character set assigned to the column. (Before MySQL 4.1,
sorting and comparison were based on the collation of the
server character set.) For the
CHAR
,
VARCHAR
,
TEXT
,
ENUM
, and
SET
data types, you can
declare a 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.
Section 9.1, “Character Set Support”, provides additional information about use of character sets in MySQL 4.1 and up.
[NATIONAL] CHAR[(
M
)]
[CHARACTER SET charset_name
]
[COLLATE
collation_name
]
A fixed-length string that is always right-padded with
spaces to the specified length when stored.
M
represents the column length in
characters. The range of M
is 0
to 255. (1 to 255 prior to MySQL 3.23). If
M
is omitted, the length is 1.
Trailing spaces are removed when
CHAR
values are retrieved.
In MySQL 4.1, 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 some
predefined character set. MySQL 4.1 and up uses
utf8
as this predefined character set.
Section 9.1.3.6, “National Character Set”.
From MySQL 4.1.2 on, the CHAR
BYTE
data type is an alias for the
BINARY
data type. This is a
compatibility feature.
MySQL permits 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
column that is defined as CHAR(0) NULL
occupies only one bit and can take only the values
NULL
and ''
(the empty
string).
[NATIONAL] VARCHAR(
M
)
[CHARACTER SET charset_name
]
[COLLATE
collation_name
]
A variable-length string. M
represents the maximum column length in characters. The
range of M
is 1 to 255 before
MySQL 4.0.2, and 0 to 255 as of MySQL 4.0.2.
MySQL stores VARCHAR
values
as a one-byte length prefix plus data. The length prefix
indicates the number of bytes in the value.
Trailing spaces are removed when
VARCHAR
values are stored.
This differs from the standard SQL specification.
In MySQL 4.1, 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
.
NATIONAL VARCHAR
is the
standard SQL way to define that a
VARCHAR
column should use
some predefined character set. MySQL 4.1 and up uses
utf8
as this predefined character set.
Section 9.1.3.6, “National Character Set”. As of MySQL 4.1.1,
NVARCHAR
is shorthand for
NATIONAL VARCHAR
.
The BINARY
type is similar to
the CHAR
type, but stores
binary byte strings rather than nonbinary character strings.
M
represents the column length in
bytes.
This type was added in MySQL 4.1.2.
The VARBINARY
type is similar
to the VARCHAR
type, but
stores binary byte strings rather than nonbinary character
strings. M
represents the maximum
column length in bytes.
This type was added in MySQL 4.1.2.
A BLOB
column with a maximum
length of 255 (28 – 1)
bytes. Each TINYBLOB
value is
stored using a one-byte length prefix that indicates the
number of bytes in the value.
TINYTEXT
[CHARACTER SET
charset_name
]
[COLLATE
collation_name
]
A TEXT
column with a maximum
length of 255 (28 – 1)
characters. The effective maximum length is less if the
value contains multi-byte characters. Each
TINYTEXT
value is stored
using a one-byte length prefix that indicates the number of
bytes in the value.
A BLOB
column with a maximum
length of 65,535 (216 – 1)
bytes. Each BLOB
value is
stored using a two-byte length prefix that indicates the
number of bytes in the value.
Beginning with MySQL 4.1, an optional length
M
can be given for this type.
MySQL creates the column as the smallest
BLOB
type large enough to
hold values M
bytes long.
TEXT[(
M
)]
[CHARACTER SET charset_name
]
[COLLATE
collation_name
]
A TEXT
column with a maximum
length of 65,535 (216 – 1)
characters. The effective maximum length is less if the
value contains multi-byte characters. Each
TEXT
value is stored using a
two-byte length prefix that indicates the number of bytes in
the value.
Beginning with MySQL 4.1, an optional length
M
can be given for this type.
MySQL creates the column as the smallest
TEXT
type large enough to
hold values M
characters long.
A BLOB
column with a maximum
length of 16,777,215 (224 –
1) bytes. Each MEDIUMBLOB
value is stored using a three-byte length prefix that
indicates the number of bytes in the value.
MEDIUMTEXT
[CHARACTER SET
charset_name
]
[COLLATE
collation_name
]
A TEXT
column with a maximum
length of 16,777,215 (224 –
1) characters. The effective maximum length is less if the
value contains multi-byte characters. Each
MEDIUMTEXT
value is stored
using a three-byte length prefix that indicates the number
of bytes in the value.
A BLOB
column with a maximum
length of 4,294,967,295 or 4GB
(232 – 1) bytes. Up to
MySQL 3.23, the client/server protocol and
MyISAM
tables had a limit of 16MB per
communication packet or table row. As of MySQL 4.0, the
effective maximum length of
LONGBLOB
columns depends on
the configured maximum packet size in the client/server
protocol and available memory. Each
LONGBLOB
value is stored
using a four-byte length prefix that indicates the number of
bytes in the value.
LONGTEXT
[CHARACTER SET
charset_name
]
[COLLATE
collation_name
]
A TEXT
column with a maximum
length of 4,294,967,295 or 4GB
(232 – 1) characters. The
effective maximum length is less if the value contains
multi-byte characters. Up to MySQL 3.23, the client/server
protocol and MyISAM
tables had a limit of
16MB per communication packet or table row. As of MySQL 4.0,
the effective maximum length of
LONGTEXT
columns depends on the configured maximum packet size in the
client/server protocol and available memory. Each
LONGTEXT
value is stored using a four-byte length prefix that
indicates the number of bytes in the value.
ENUM('
value1
','value2
',...)
[CHARACTER SET charset_name
]
[COLLATE
collation_name
]
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
',...)
[CHARACTER SET charset_name
]
[COLLATE
collation_name
]
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.
The DEFAULT
clause in a data type specification indicates a default value
for a column. With one exception, the default value must be a
constant; it cannot be a function or an expression. This means,
for example, that you cannot set the default for a date column
to be the value of a function such as
value
NOW()
or
CURRENT_DATE
. The exception is
that you can specify
CURRENT_TIMESTAMP
as the default
for a TIMESTAMP
column as of
MySQL 4.1.2. See Section 10.3.1.2, “TIMESTAMP Properties as of MySQL 4.1”.
If a column definition includes no explicit
DEFAULT
value, MySQL determines the default
value as follows:
If the column can take NULL
as a value, the
column is defined with an explicit DEFAULT
NULL
clause.
If the column cannot take NULL
as the value,
MySQL defines the column with an explicit
DEFAULT
clause, using the implicit default
value for the column data type. Implicit defaults are defined as
follows:
For numeric types, the default is 0
, with
the exception that for integer or floating-point types
declared with the AUTO_INCREMENT
attribute, the default is the next value in the sequence.
For date and time types other than
TIMESTAMP
, the default is the
appropriate “zero” value for the type. For the
first TIMESTAMP
column in a
table, the default value is the current date and time. See
Section 10.3, “Date and Time Types”.
For string types other than
ENUM
, the default value is
the empty string. For ENUM
,
the default is the first enumeration value.
BLOB
and
TEXT
columns cannot be assigned a
default value.
For a given table, you can use the SHOW
CREATE TABLE
statement to see which columns have an
explicit DEFAULT
clause.
SERIAL DEFAULT VALUE
in the definition of an
integer column is an alias for NOT NULL AUTO_INCREMENT
UNIQUE
.
MySQL supports all the standard SQL numeric data types. These
types include the exact numeric data types
(INTEGER
,
SMALLINT
,
DECIMAL
, and
NUMERIC
), as well as the
approximate numeric data types
(FLOAT
,
REAL
, and
DOUBLE PRECISION
). The keyword
INT
is a synonym for
INTEGER
, and the keyword
DEC
is a synonym for
DECIMAL
. MySQL treats
DOUBLE
as a synonym for
DOUBLE PRECISION
(a nonstandard
extension). MySQL also treats REAL
as a synonym for DOUBLE PRECISION
(a nonstandard variation), unless the
REAL_AS_FLOAT
SQL mode is
enabled.
For information about how MySQL handles assignment of out-of-range values to columns and overflow during expression evaluation, see Section 10.2.5, “Out-of-Range and Overflow Handling”.
For information about numeric type storage requirements, see Section 10.5, “Data Type Storage Requirements”.
The data type used for the result of a calculation on numeric operands depends on the types of the operands and the operations performed on them. For more information, see Section 11.6.1, “Arithmetic Operators”.
MySQL supports the SQL standard integer types
INTEGER
(or
INT
) and
SMALLINT
. As an extension to the
standard, MySQL also supports the integer types
TINYINT
,
MEDIUMINT
, and
BIGINT
. The following table shows
the required storage and range for each integer type.
The DECIMAL
and
NUMERIC
types store exact numeric
data values. These types are used when it is important to
preserve exact precision, for example with monetary data. In
MySQL, NUMERIC
is implemented as
DECIMAL
, so the following remarks
about DECIMAL
apply equally to
NUMERIC
.
Through version 4.1, MySQL stores
DECIMAL
values as strings, rather
than in binary format. One character is used for each digit of
the value, the decimal point (if the scale is greater than 0),
and the “-
” sign (for negative
numbers).
In a DECIMAL
column declaration,
the precision and scale can be (and usually is) specified; for
example:
salary DECIMAL(5,2)
In this example, 5
is the precision and
2
is the scale. The precision represents the
number of significant digits that are stored for values, and the
scale represents the number of digits that can be stored
following the decimal point.
Standard SQL requires that DECIMAL(5,2)
be
able to store any value with five digits and two decimals, so
values that can be stored in the salary
column range from -999.99
to
999.99
. In versions up to and including 4.1,
MySQL varies from this limit in two ways due to the use of
string format for value storage:
On the positive end of the range, the column actually can
store numbers up to 9999.99
. For positive
numbers, MySQL uses the byte reserved for the sign to extend
the upper end of the range.
DECIMAL
columns in MySQL
before 3.23 are stored differently and cannot represent all
the values required by standard SQL. This is because for a
type of
DECIMAL(
,
the value of M
,D
)M
includes the bytes
for the sign and the decimal point. The range of the
salary
column before MySQL 3.23 would be
-9.99
to 99.99
.
In standard SQL, the syntax
DECIMAL(
is
equivalent to
M
)DECIMAL(
.
Similarly, the syntax M
,0)DECIMAL
is
equivalent to
DECIMAL(
,
where the implementation is permitted to decide the value of
M
,0)M
. As of MySQL 3.23.6, both of these
variant forms of DECIMAL
syntax
are supported. The default value of M
is 10. Before 3.23.6, M
and
D
both must be specified explicitly.
If the scale is 0, DECIMAL
values
contain no decimal point or fractional part.
The maximum range of DECIMAL
values is the same as for DOUBLE
,
but the actual range for a given
DECIMAL
column can be constrained
by the precision or scale for a given column. When such a column
is assigned a value with more digits following the decimal point
than are permitted by the specified scale, the value is
converted to that scale. (The precise behavior is operating
system-specific, but generally the effect is truncation to the
permissible number of digits.)
The FLOAT
and
DOUBLE
types represent
approximate numeric data values. MySQL uses four bytes for
single-precision values and eight bytes for double-precision
values.
For FLOAT
, the SQL standard
permits an optional specification of the precision (but not the
range of the exponent) in bits following the keyword
FLOAT
in parentheses. MySQL also
supports this optional precision specification, but the
precision value is used only to determine storage size. A
precision from 0 to 23 results in a four-byte single-precision
FLOAT
column. A precision from 24
to 53 results in an eight-byte double-precision
DOUBLE
column.
MySQL permits a nonstandard syntax:
FLOAT(
or
M
,D
)REAL(
or M
,D
)DOUBLE
PRECISION(
.
Here,
“M
,D
)(
”
means than values can be stored with up to
M
,D
)M
digits in total, of which
D
digits may be after the decimal
point. For example, a column defined as
FLOAT(7,4)
will look like
-999.9999
when displayed. MySQL performs
rounding when storing values, so if you insert
999.00009
into a
FLOAT(7,4)
column, the approximate result is
999.0001
.
Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies. For more information, see Section B.5.5.8, “Problems with Floating-Point Values”
For maximum portability, code requiring storage of approximate
numeric data values should use
FLOAT
or
DOUBLE PRECISION
with no
specification of precision or number of digits.
MySQL supports an extension for optionally specifying the
display width of integer data types in parentheses following the
base keyword for the type. For example,
INT(4)
specifies an
INT
with a display width of four
digits. This optional display width may be used by applications
to display integer values having a width less than the width
specified for the column by left-padding them with spaces. (That
is, this width is present in the metadata returned with result
sets. Whether it is used or not is up to the application.)
The display width does not constrain the
range of values that can be stored in the column. Nor does it
prevent values wider than the column display width from being
displayed correctly. For example, a column specified as
SMALLINT(3)
has the usual
SMALLINT
range of
-32768
to 32767
, and
values outside the range permitted by three digits are displayed
in full using more than three digits.
When used in conjunction with the optional (nonstandard)
attribute ZEROFILL
, the default padding of
spaces is replaced with zeros. For example, for a column
declared as INT(4) ZEROFILL
, a
value of 5
is retrieved as
0005
.
The ZEROFILL
attribute is ignored when a
column is involved in expressions or
UNION
queries.
If you store values larger than the display width in an
integer column that has the ZEROFILL
attribute, you may experience problems when MySQL generates
temporary tables for some complicated joins. In these cases,
MySQL assumes that the data values fit within the column
display width.
All integer types can have an optional (nonstandard) attribute
UNSIGNED
. Unsigned type can be used to permit
only nonnegative numbers in a column or when you need a larger
upper numeric range for the column. For example, if an
INT
column is
UNSIGNED
, the size of the column's range is
the same but its endpoints shift from
-2147483648
and 2147483647
up to 0
and 4294967295
.
As of MySQL 4.0.2, floating-point and fixed-point types also can
be UNSIGNED
. As with integer types, this
attribute prevents negative values from being stored in the
column. Unlike the integer types, the upper range of column
values remains the same.
If you specify ZEROFILL
for a numeric column,
MySQL automatically adds the UNSIGNED
attribute to the column.
Integer or floating-point data types can have the additional
attribute AUTO_INCREMENT
. When you insert a
value of NULL
(recommended) or
0
into an indexed
AUTO_INCREMENT
column, the column is set to
the next sequence value. Typically this is
, where
value
+1value
is the largest value for the
column currently in the table. AUTO_INCREMENT
sequences begin with 1
.
When MySQL stores a value in a numeric column that is outside the permissible range of the column data type, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead.
For example, when an out-of-range value is assigned to an
integer column, MySQL stores the value representing the
corresponding endpoint of the column data type range. If you
store 256 into a TINYINT
or
TINYINT UNSIGNED
column, MySQL stores 127 or
255, respectively. When a floating-point or fixed-point column
is assigned a value that exceeds the range implied by the
specified (or default) precision and scale, MySQL stores the
value representing the corresponding endpoint of that range.
Column-assignment conversions that occur due to clipping are
reported as warnings for ALTER
TABLE
, LOAD
DATA INFILE
, UPDATE
,
and multiple-row INSERT
statements.
In MySQL 4.1, overflow handling during numeric expression evaluation depends on the types of the operands:
Integer overflow results in silent wraparound.
DECIMAL
overflow results in a truncated
result and a warning.
Floating-point overflow produces a NULL
result. Overflow for some operations can result in
+INF
, -INF
, or
NaN
.
For example, the largest signed
BIGINT
value is
9223372036854775807, so the following expression wraps around to
the minimum BIGINT
value:
mysql> SELECT 9223372036854775807 + 1;
+-------------------------+
| 9223372036854775807 + 1 |
+-------------------------+
| -9223372036854775808 |
+-------------------------+
To enable the operation to succeed in this case, convert the value to unsigned;
mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;
+-------------------------------------------+
| CAST(9223372036854775807 AS UNSIGNED) + 1 |
+-------------------------------------------+
| 9223372036854775808 |
+-------------------------------------------+
Whether overflow occurs depends on the range of the operands, so
another way to handle the preceding expression is to use
exact-value arithmetic because
DECIMAL
values have a larger
range than integers:
mysql> SELECT 9223372036854775807.0 + 1;
+---------------------------+
| 9223372036854775807.0 + 1 |
+---------------------------+
| 9223372036854775808.0 |
+---------------------------+
Subtraction between integer values, where one is of type
UNSIGNED
, produces an unsigned result by
default. If the result would otherwise have been negative, it
becomes the maximum integer value. If the
NO_UNSIGNED_SUBTRACTION
SQL
mode is enabled, the result is negative.
mysql>SET sql_mode = '';
mysql>SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+ | CAST(0 AS UNSIGNED) - 1 | +-------------------------+ | 18446744073709551615 | +-------------------------+ mysql>SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
mysql>SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+ | CAST(0 AS UNSIGNED) - 1 | +-------------------------+ | -1 | +-------------------------+
If the result of such an operation is used to update an
UNSIGNED
integer column, the result is
clipped to the maximum value for the column type, or clipped to
0 if NO_UNSIGNED_SUBTRACTION
is enabled.
The date and time types for representing temporal values are
DATE
,
TIME
,
DATETIME
,
TIMESTAMP
, and
YEAR
. Each temporal type has a
range of legal values, as well as a “zero” value that
is used when you specify an illegal value that MySQL cannot
represent. The TIMESTAMP
type has
special automatic updating behavior, described later on. For
temporal type storage requirements, see
Section 10.5, “Data Type Storage Requirements”.
MySQL versions through 4.1 accept certain “illegal”
values for dates, such as '2009-11-31'
. This is
useful when you want to store a possibly incorrect value specified
by a user (for example, in a web form) in the database for future
processing. MySQL verifies only that the month is in the range
from 0 to 12 and that the day is in the range from 0 to 31. These
ranges are defined to include zero because MySQL permits you to
store dates where the day or month and day are zero in a
DATE
or
DATETIME
column. This is extremely
useful for applications that need to store a birthdate for which
you do not know the exact date. In this case, you simply store the
date as '2009-00-00'
or
'2009-01-00'
. If you store dates such as these,
you should not expect to get correct results for functions such as
DATE_SUB()
or
DATE_ADD()
that require complete
dates.
MySQL also permits you to store '0000-00-00'
as
a “dummy date.” This is in some cases more convenient
(and uses less data and index space) than storing
NULL
values.
Keep in mind these general considerations when working with date and time types:
MySQL retrieves values for a given date or time type in a standard output format, but it attempts to interpret a variety of formats for input values that you supply (for example, when you specify a value to be assigned to or compared to a date or time type). For a description of the permitted formats for date and time types, see Section 8.1.3, “Date and Time Literals”. It is expected that you supply legal values. Unpredictable results may occur if you use values in other formats.
Although MySQL tries to interpret values in several formats,
date parts must always be given in year-month-day order (for
example, '98-09-04'
), rather than in the
month-day-year or day-month-year orders commonly used
elsewhere (for example, '09-04-98'
,
'04-09-98'
).
Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using these rules:
Year values in the range 70-99
are
converted to 1970-1999
.
Year values in the range 00-69
are
converted to 2000-2069
.
Conversion of values from one temporal type to another occurs according to the rules in Section 10.3.5, “Conversion Between Date and Time Types”.
MySQL automatically converts a date or time value to a number if the value is used in a numeric context and vice versa.
By default, when MySQL encounters a value for a date or time
type that is out of range or otherwise illegal for the type,
it converts the value to the “zero” value for
that type. The exception is that out-of-range
TIME
values are clipped to the
appropriate endpoint of the
TIME
range.
“Zero” date or time values used through MyODBC
are converted automatically to NULL
in
MyODBC 2.50.12 and above, because ODBC cannot handle such
values.
The following table shows the format of the “zero”
value for each type. The “zero” values are special,
but you can store or refer to them explicitly using the values
shown in the table. You can also do this using the values
'0'
or 0
, which are easier
to write.
Data Type | “Zero” Value |
---|---|
DATE | '0000-00-00' |
TIME | '00:00:00' |
DATETIME | '0000-00-00 00:00:00' |
TIMESTAMP (4.1 and up) | '0000-00-00 00:00:00' |
TIMESTAMP (before 4.1) | 00000000000000 |
YEAR | 0000 |
The DATE
,
DATETIME
, and
TIMESTAMP
types are related. This
section describes their characteristics, how they are similar,
and how they differ.
The DATE
type is used for values
with a date part but no time part. MySQL retrieves and displays
DATE
values in
'YYYY-MM-DD'
format. The supported range is
'1000-01-01'
to
'9999-12-31'
.
The DATETIME
type is used for
values that contain both date and time parts. MySQL retrieves
and displays DATETIME
values in
'YYYY-MM-DD HH:MM:SS'
format. The supported
range is '1000-01-01 00:00:00'
to
'9999-12-31 23:59:59'
.
For the DATE
and
DATETIME
range descriptions,
“supported” means that although earlier values
might work, there is no guarantee.
The TIMESTAMP
data type is used
for values that contain both date and time parts.
TIMESTAMP
has a range of
'1970-01-01 00:00:01'
UTC to
'2038-01-19 03:14:07'
UTC. Its properties are
described in more detail in Section 10.3.1.2, “TIMESTAMP Properties as of MySQL 4.1”.
MySQL recognizes DATE
,
DATETIME
, and
TIMESTAMP
values in several
formats, described in Section 8.1.3, “Date and Time Literals”.
A DATETIME
or
TIMESTAMP
value can include a
trailing fractional seconds part in up to microseconds (6
digits) precision. Although this fractional part is recognized,
it is discarded from values stored into
DATETIME
or
TIMESTAMP
columns. For
information about fractional seconds support in MySQL, see
Section 10.3.4, “Fractional Seconds in Time Values”.
Illegal DATE
,
DATETIME
, or
TIMESTAMP
values are converted to
the “zero” value of the appropriate type
('0000-00-00'
, '0000-00-00
00:00:00'
, or 00000000000000
).
Be aware of certain problems when specifying date values:
MySQL permits a “relaxed” format for values
specified as strings, in which any punctuation character may
be used as the delimiter between date parts or time parts.
In some cases, this syntax can be deceiving. For example, a
value such as '10:11:12'
might look like
a time value because of the
“:
” delimiter, but is
interpreted as the year '2010-11-12'
if
used in a date context. The value
'10:45:15'
is converted to
'0000-00-00'
because
'45'
is not a legal month.
The MySQL server performs only basic checking on the
validity of a date: The ranges for year, month, and day are
1000 to 9999, 00 to 12, and 00 to 31, respectively. Any date
containing parts not within these ranges is subject to
conversion to '0000-00-00'
. Please note
that this still permits you to store invalid dates such as
'2002-04-31'
. To ensure that a date is
valid, perform a check in your application.
Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using these rules:
Year values in the range 00-69
are
converted to 2000-2069
.
Year values in the range 70-99
are
converted to 1970-1999
.
TIMESTAMP
values are converted
from the current time zone to UTC for storage, and converted
back from UTC to the current time zone for retrieval. (This
occurs only for the TIMESTAMP
data type, not for other types such as
DATETIME
.)
The TIMESTAMP
data type
provides a type that you can use to automatically mark
INSERT
or
UPDATE
operations with the
current date and time. If you have multiple
TIMESTAMP
columns in a table,
only the first one is updated automatically. (From MySQL 4.1.2
on, you can specify which
TIMESTAMP
column updates; see
Section 10.3.1.2, “TIMESTAMP Properties as of MySQL 4.1”.)
Automatic updating of the first
TIMESTAMP
column in a table
occurs under any of the following conditions:
You explicitly set the column to NULL
.
The column is not specified explicitly in an
INSERT
or
LOAD DATA
INFILE
statement.
The column is not specified explicitly in an
UPDATE
statement and some
other column changes value. An
UPDATE
that sets a column
to the value it does not cause the
TIMESTAMP
column to be
updated; if you set a column to its current value, MySQL
ignores the update for efficiency.
A TIMESTAMP
column other than
the first also can be assigned the current date and time by
setting it to NULL
or to any function that
produces the current date and time
(NOW()
,
CURRENT_TIMESTAMP
).
Note that the information in the following discussion applies
to TIMESTAMP
columns only for
tables not created with
MAXDB
mode enabled, because
such columns are created as
DATETIME
columns.
You can set any TIMESTAMP
column to a value different from the current date and time by
setting it explicitly to the desired value. This is true even
for the first TIMESTAMP
column.
You can use this property if, for example, you want a
TIMESTAMP
to be set to the
current date and time when you create a row, but not to be
changed whenever the row is updated later:
Let MySQL set the column when the row is created. This initializes it to the current date and time.
When you perform subsequent updates to other columns in
the row, set the TIMESTAMP
column explicitly to its current value:
UPDATEtbl_name
SETtimestamp_col
=timestamp_col
,other_col1
=new_value1
,other_col2
=new_value2
, ...
Another way to maintain a column that records row-creation
time is to use a DATETIME
column that you initialize to
NOW()
when the row is created
and do not modify for subsequent updates.
TIMESTAMP
values may range from
the beginning of 1970 to partway through the year 2038, with a
resolution of one second. Values are displayed as numbers.
When you store a value in a
TIMESTAMP
column, it is assumed
to be represented in the current time zone, and is converted
to UTC for storage. When you retrieve the value, it is
converted from UTC back to the local time zone for display.
Before MySQL 4.1.3, the server has a single time zone. As of
4.1.3, clients can set their own time zones on a
per-connection basis, as described in
Section 9.7, “MySQL Server Time Zone Support”.
Prior to version 4.1, the format in which MySQL retrieves and
displays TIMESTAMP
values
depends on the display size, as illustrated in the following
table. The “full”
TIMESTAMP
format is 14 digits,
but TIMESTAMP
columns may be
created with shorter display sizes.
Data 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.
TIMESTAMP
columns store legal
values using the full precision with which the value was
specified, regardless of the display size. This has several
implications:
Always specify year, month, and day, even if your column
types are TIMESTAMP(4)
or
TIMESTAMP(2)
. Otherwise, the value is
not a legal date and 0
is stored.
If you use ALTER TABLE
to
widen a narrow TIMESTAMP
column, information is displayed that previously was
“hidden.”
Similarly, narrowing a
TIMESTAMP
column does not
cause information to be lost, except in the sense that
less information is shown when the values are displayed.
If you are planning to use mysqldump
for the database, do not use
TIMESTAMP(4)
or
TIMESTAMP(2)
. The display format for
these data types are not legal dates and
0
will be stored instead. This
inconsistency is fixed starting with MySQL 4.1, where
display width is ignored. To prepare for transition to
versions after 4.0, you should change to use display
widths of 6 or more, which will produce a legal display
format. You can change the display width of
TIMESTAMP
data types,
without losing any information, by using
ALTER TABLE
as indicated
above.
If you need to print the timestamps for external
applications, you can use
MID()
to extract the
relevant part of the timestamp: for example, to imitate
the TIMESTAMP(4)
display format.
Although 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 a function 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.
In MySQL 4.1, TIMESTAMP
display
format changes to be the same as
DATETIME
, that is, as a string
in 'YYYY-MM-DD HH:MM:SS'
format rather than
as a number in YYYYMMDDHHMMSS
format. To
test applications written for MySQL 4.0 for compatibility with
this change, you can set the
new
system variable to 1.
This variable is available beginning with MySQL 4.0.12. It can
be set at server startup by specifying the
--new
option to mysqld. At
runtime, a user who has the
SUPER
privilege can set the
global value with a
SET
statement:
mysql> SET GLOBAL new = 1;
Any client can set its session value of
new
as follows:
mysql> SET new = 1;
The general effect of setting
new
to 1 is that values for
existing TIMESTAMP
columns
display as strings rather than as numbers. Also,
DESCRIBE
displays the column
definition as TIMESTAMP(19)
,
rather than as TIMESTAMP(14)
.
The effect differs somewhat for
TIMESTAMP
columns that are
created while new
is set to
1. In this case, column values display as strings and
DESCRIBE
shows the definition
as TIMESTAMP(19)
, regardless of
the current value of new
.
In other words, with new=1
, all
TIMESTAMP
values display as
strings and DESCRIBE
shows a
display width of 19. For columns created while
new=1
, they continue to display as strings
and to have a display width of 19 even if
new
is set to 0.
For a TIMESTAMP
column that
displays as a string, you can display it as a number by
retrieving it as
.
col_name
+0
In MySQL 4.1 and up, the properties of the
TIMESTAMP
data type changed in
several ways. The following discussion describes the revised
syntax and behavior.
Beginning with MySQL 4.1.3, the default current time zone for
each connection is the server's time. The time zone can be set
on a per-connection basis, as described in
Section 9.7, “MySQL Server Time Zone Support”.
TIMESTAMP
values still are
stored in UTC, but are converted from the current time zone
for storage, and converted back to the current time zone for
retrieval. As long as the time zone setting remains constant,
you get back the same value you store. If you store a
TIMESTAMP
value, and then
change the time zone and retrieve the value, the retrieved
value is different from the value you stored. This occurs
because the same time zone was not used for conversion in both
directions. The current time zone is available as the value of
the time_zone
system
variable.
From MySQL 4.1.0 on, TIMESTAMP
display format differs from that of earlier MySQL releases:
TIMESTAMP
columns are
displayed in the same format as
DATETIME
columns. In other
words, the display width is fixed at 19 characters, and
the format is 'YYYY-MM-DD HH:MM:SS'
.
Display widths (used as described in the preceding
section) are no longer supported. In other words, for
declarations such as TIMESTAMP(2)
,
TIMESTAMP(4)
, and so on, the display
width is ignored.
The following items summarize
TIMESTAMP
initialization and
updating properties prior to MySQL 4.1.2:
The first TIMESTAMP
column
in table row automatically is set to the current timestamp
when the record is created if the column is set to
NULL
or is not specified at all.
The first TIMESTAMP
column
in table row automatically is updated to the current
timestamp when the value of any other column in the row is
changed, unless the
TIMESTAMP
column explicitly
is assigned a value other than NULL
.
If a DEFAULT
value is specified for the
first TIMESTAMP
column when
the table is created, it is silently ignored.
Other TIMESTAMP
columns in
the table can be set to the current
TIMESTAMP
by assigning
NULL
to them, but they do not update
automatically.
Beginning with MySQL 4.1.2, you have more flexible control
over when automatic TIMESTAMP
initialization and updating occur and which column should have
those behaviors:
For one TIMESTAMP
column in
a table, you can assign the current timestamp as the
default value and the auto-update value. It is possible to
have the current timestamp be the default value for
initializing the column, for the auto-update value, or
both. It is not possible to have the current timestamp be
the default value for one column and the auto-update value
for another column.
Any single TIMESTAMP
column
in a table can be used as the one that is initialized to
the current date and time, or updated automatically. This
need not be the first
TIMESTAMP
column.
In a CREATE TABLE
statement, the first
TIMESTAMP
column can be
declared in any of the following ways:
With both DEFAULT CURRENT_TIMESTAMP
and ON UPDATE CURRENT_TIMESTAMP
clauses, the column has the current timestamp for its
default value, and is automatically updated.
With neither DEFAULT
nor
ON UPDATE
clauses, it is the same
as DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP
.
With a DEFAULT CURRENT_TIMESTAMP
clause and no ON UPDATE
clause, the
column has the current timestamp for its default value
but is not automatically updated.
With no DEFAULT
clause and with an
ON UPDATE CURRENT_TIMESTAMP
clause,
the column has a default of 0 and is automatically
updated.
With a constant DEFAULT
value, the
column has the given default and is not automatically
initialized to the current timestamp. If the column
also has an ON UPDATE
CURRENT_TIMESTAMP
clause, it is
automatically updated; otherwise, it has a constant
default and is not automatically updated.
In other words, you can use the current timestamp for both
the initial value and the auto-update value, or either
one, or neither. (For example, you can specify ON
UPDATE
to enable auto-update without also having
the column auto-initialized.) The following column
definitions demonstrate each possibility:
Auto-initialization and auto-update:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Auto-initialization only:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
Auto-update only:
ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
Neither:
ts TIMESTAMP DEFAULT 0
To specify automatic default or updating for a
TIMESTAMP
column other than
the first one, you must suppress the automatic
initialization and update behaviors for the first
TIMESTAMP
column by
explicitly assigning it a constant
DEFAULT
value (for example,
DEFAULT 0
or DEFAULT
'2003-01-01 00:00:00'
). Then, for the other
TIMESTAMP
column, the rules
are the same as for the first
TIMESTAMP
column, except
that if you omit both of the DEFAULT
and ON UPDATE
clauses, no automatic
initialization or updating occurs.
Example:
CREATE TABLE t ( ts1 TIMESTAMP DEFAULT 0, ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
CURRENT_TIMESTAMP
or any of
its synonyms
(CURRENT_TIMESTAMP()
,
NOW()
,
LOCALTIME
,
LOCALTIME()
,
LOCALTIMESTAMP
, or
LOCALTIMESTAMP()
) can be
used in the DEFAULT
and ON
UPDATE
clauses. They all mean “the current
timestamp.”
UTC_TIMESTAMP
is not
permitted. Its range of values does not align with those
of the TIMESTAMP
column
except when the current time zone is
UTC
.
The order of the DEFAULT
and
ON UPDATE
clauses does not matter. If
both DEFAULT
and ON
UPDATE
are specified for a
TIMESTAMP
column, either
can precede the other. For example, these statements are
equivalent:
CREATE TABLE t (ts TIMESTAMP); CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
The following rules describe the changes in MySQL 4.1
regarding TIMESTAMP
and
handling of NULL
values:
Before MySQL 4.1.2,
TIMESTAMP
columns are
NOT NULL
. They cannot contain
NULL
values, and assigning
NULL
assigns the current timestamp. Any
DEFAULT
clause is ignored.
From MySQL 4.1.2 to 4.1.5,
TIMESTAMP
columns are
NOT NULL
. They cannot contain
NULL
values, and assigning
NULL
assigns the current timestamp. A
DEFAULT NULL
clause can be specified,
but it is treated as DEFAULT
CURRENT_TIMESTAMP
for the first
TIMESTAMP
column and as
DEFAULT 0
for other
TIMESTAMP
columns.
As of MySQL 4.1.6,
TIMESTAMP
columns are
NOT NULL
by default, cannot contain
NULL
values, and assigning
NULL
assigns the current timestamp.
However, a TIMESTAMP
column
can be permitted to contain NULL
by
declaring it with the NULL
attribute.
In this case, the default value also becomes
NULL
unless overridden with a
DEFAULT
clause that specifies a
different default value. DEFAULT NULL
can be used to explicitly specify NULL
as the default value. (For a
TIMESTAMP
column not
declared with the NULL
attribute,
DEFAULT NULL
is illegal.) If a
TIMESTAMP
column permits
NULL
values, assigning
NULL
sets it to
NULL
, not to the current timestamp.
The following table contains several
TIMESTAMP
columns that permit
NULL
values:
CREATE TABLE t ( ts1 TIMESTAMP NULL DEFAULT NULL, ts2 TIMESTAMP NULL DEFAULT 0, ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP );
Note that a TIMESTAMP
column
that permits NULL
values will
not take on the current timestamp except
under one of the following conditions:
Its default value is defined as
CURRENT_TIMESTAMP
NOW()
or
CURRENT_TIMESTAMP
is
inserted into the column
In other words, a TIMESTAMP
column defined as NULL
will auto-initialize
only if it is created using a definition such as the
following:
CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
Otherwise—that is, if the
TIMESTAMP
column is defined to
permit NULL
values but not using
DEFAULT CURRENT_TIMESTAMP
, as shown
here…
CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT NULL); CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
…then you must explicitly insert a value corresponding to the current date and time. For example:
INSERT INTO t1 VALUES (NOW()); INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
Beginning with MySQL 4.1.1, the MySQL server can be run with
the MAXDB
SQL mode
enabled. When the server runs with this mode enabled,
TIMESTAMP
is identical with
DATETIME
. That is, if this
mode is enabled at the time that a table is created,
TIMESTAMP
columns are created
as DATETIME
columns. As a
result, such columns use
DATETIME
display format, have
the same range of values, and there is no automatic
initialization or updating to the current date and time.
To enable MAXDB
mode, set
the server SQL mode to MAXDB
at startup using the
--sql-mode=MAXDB
server option
or by setting the global
sql_mode
variable at runtime:
mysql> SET GLOBAL sql_mode=MAXDB;
A client can cause the server to run in
MAXDB
mode for its own
connection as follows:
mysql> SET SESSION sql_mode=MAXDB;
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 hours part may be so large
because the TIME
type can 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).
MySQL recognizes TIME
values in
several formats, described in
Section 8.1.3, “Date and Time Literals”. Some of these formats
can include a trailing fractional seconds part in up to
microseconds (6 digits) precision. Although this fractional part
is recognized, it is discarded from values stored into
TIME
columns. For information
about fractional seconds support in MySQL, see
Section 10.3.4, “Fractional Seconds in Time Values”.
Be careful about assigning abbreviated values to a
TIME
column. MySQL interprets
abbreviated TIME
values with
colons as time of the day. That is, '11:12'
means '11:12:00'
, not
'00:11:12'
. MySQL interprets abbreviated
values without colons using the assumption that the two
rightmost digits represent seconds (that is, as elapsed time
rather than as time of day). For example, you might think of
'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'
.
By default, values that lie outside the
TIME
range but are otherwise
legal are clipped to the closest 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 because
'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.
For more restrictive treatment of invalid
TIME
values, enable strict SQL
mode to cause errors to occur. See
Section 5.1.6, “Server SQL Modes”.
The YEAR
type is a one-byte type
used for representing years. It can be declared as
YEAR(2)
or YEAR(4)
to
specify a display width of two or four characters. The default
is four characters if no width is given.
For four-digit format, MySQL displays
YEAR
values in
YYYY
format, with a range of
1901
to 2155
, or
0000
. For two-digit format, MySQL displays
only the last two (least significant) digits; for example,
70
(1970 or 2070) or 69
(2069).
You can specify input YEAR
values
in a variety of formats:
As a four-digit string in the range
'1901'
to '2155'
.
As a four-digit number in the range 1901
to 2155
.
As a two-digit string in the range '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
.
As a two-digit number in the range 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
from the range for two-digit strings, because 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 is
interpreted as 0000
.
As the result of a function that returns a value that is
acceptable in a YEAR
context,
such as NOW()
.
Illegal YEAR
values are converted
to 0000
.
A trailing fractional seconds part is permissible for temporal values in contexts such as literal values, and in the arguments to or return values from some temporal functions. Example:
mysql> SELECT MICROSECOND('2010-12-10 14:12:09.019473');
+-------------------------------------------+
| MICROSECOND('2010-12-10 14:12:09.019473') |
+-------------------------------------------+
| 19473 |
+-------------------------------------------+
However, when MySQL stores a value into a column of any temporal data type, it discards any fractional part and does not store it.
To some extent, you can convert a value from one temporal type
to another. However, there may be some alteration of the value
or loss of information. In all cases, conversion between
temporal types is subject to the range of legal values for the
resulting type. For example, although
DATE
,
DATETIME
, and
TIMESTAMP
values all can be
specified using the same set of formats, the types do not all
have the same range of values.
TIMESTAMP
values cannot be
earlier than 1970
UTC or later than
'2038-01-19 03:14:07'
UTC. This means that a
date such as '1968-01-01'
, while legal as a
DATE
or
DATETIME
value, is not valid as a
TIMESTAMP
value and is converted
to 0
.
Conversion of DATE
values:
Conversion of DATETIME
and
TIMESTAMP
values:
Conversion of TIME
values:
MySQL converts a time value to a date or date-and-time value by
parsing the string value of the time as a date or date-and-time.
This is unlikely to be useful. For example,
'23:12:31'
interpreted as a date becomes
'2032-12-31'
. Time values not valid as dates
become '0000-00-00'
or
NULL
.
As of MySQL 4.1.13, conversion of
TIME
or
DATETIME
values to numeric form
(for example, by adding +0
) results in a
double-precision value with a microseconds part of
.000000
:
mysql>SELECT CURTIME(), CURTIME()+0;
+-----------+---------------+ | CURTIME() | CURTIME()+0 | +-----------+---------------+ | 10:41:36 | 104136.000000 | +-----------+---------------+ mysql>SELECT NOW(), NOW()+0;
+---------------------+-----------------------+ | NOW() | NOW()+0 | +---------------------+-----------------------+ | 2007-11-30 10:41:47 | 20071130104147.000000 | +---------------------+-----------------------+
Before MySQL 4.1.13, the conversion results in an integer value with no microseconds part.
Date values with two-digit years are ambiguous because the century is unknown. Such values must be interpreted into four-digit form because MySQL stores years internally using four digits.
For DATETIME
,
DATE
,
TIMESTAMP
, and
YEAR
types, MySQL interprets
dates specified with ambiguous year values using these rules:
Year values in the range 00-69
are
converted to 2000-2069
.
Year values in the range 70-99
are
converted to 1970-1999
.
Remember that these rules are only heuristics that provide reasonable guesses as to what your data values mean. If the rules used by MySQL do not produce the values you require, you must provide unambiguous input containing four-digit year values.
In MySQL, the YEAR
data type can
store the years 0
and 1901
to 2155
in one byte and display them using
two or four digits. All two-digit years are considered to be in
the range 1970
to 2069
,
which means that if you store 01
in a
YEAR
column, MySQL Server treats
it as 2001
.
ORDER BY
properly sorts
YEAR
values that have two-digit
years.
Some functions like MIN()
and
MAX()
convert a
YEAR
to a number. This means that
a value with a two-digit year does not work properly with these
functions. The fix in this case is to convert the
YEAR
to four-digit year format.
The string types are CHAR
,
VARCHAR
,
BINARY
,
VARBINARY
,
BLOB
,
TEXT
,
ENUM
, and
SET
. This section describes how
these types work and how to use them in your queries. For string
type storage requirements, see
Section 10.5, “Data Type Storage Requirements”.
The CHAR
and
VARCHAR
types are similar, but
differ in the way they are stored and retrieved.
The CHAR
and
VARCHAR
types are declared with a
length that indicates the maximum number of characters you want
to store. For example, CHAR(30)
can hold up
to 30 characters. (Before MySQL 4.1, the length is interpreted
as number of bytes.)
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 from 0 to 255. (Before MySQL 3.23,
the length of CHAR
may be from 1
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. The length can be specified as a value
from 1 to 255 before MySQL 4.0.2 and 0 to 255 as of MySQL 4.0.2.
In contrast to CHAR
,
VARCHAR
values are stored as a
one-byte length prefix plus data. The length prefix indicates
the number of bytes in the value.
If you assign a value to a CHAR
or VARCHAR
column that exceeds
the column's maximum length, the value is truncated to fit. If
the truncated characters are not spaces, a warning is generated.
VARCHAR
values are not padded
when they are stored. Trailing spaces in MySQL version up to and
including 4.1 are removed from values when stored in a
VARCHAR
column; this also means
that the spaces are absent from retrieved values.
If you need a data type for which trailing spaces are not
removed, consider using a BLOB
or
TEXT
type. If you want to store
binary values such as results from an encryption or compression
function that might contain arbitrary byte values, use a
BLOB
column rather than a
CHAR
or
VARCHAR
column, to avoid
potential problems with trailing space removal that would change
data values.
The following table illustrates the differences between
CHAR
and
VARCHAR
by showing the result of
storing various string values into CHAR(4)
and VARCHAR(4)
columns (assuming that the
column uses a single-byte character set such as
latin1
).
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 |
If a given value is stored into the CHAR(4)
and VARCHAR(4)
columns, the values retrieved
from the columns are not always the same because trailing spaces
are removed from CHAR
columns
upon retrieval.
As of MySQL 4.1, values in CHAR
and VARCHAR
columns are sorted
and compared according to the character set collation assigned
to the column. Before MySQL 4.1, sorting and comparison are
based on the collation of the server character set; you can
declare the column with the BINARY
attribute
to cause sorting and comparison to be based on the numeric
values of the bytes in column values. BINARY
does not affect how column values are stored or retrieved.
All MySQL collations are of type PADSPACE
.
This means that all CHAR
and
VARCHAR
values in MySQL are
compared without regard to any trailing spaces. For example:
mysql>CREATE TABLE names (myname CHAR(10), yourname VARCHAR(10));
Query OK, 0 rows affected (0.09 sec) mysql>INSERT INTO names VALUES ('Monty ', 'Monty ');
Query OK, 1 row affected (0.00 sec) mysql>SELECT myname = 'Monty ', yourname = 'Monty ' FROM names;
+--------------------+----------------------+ | myname = 'Monty ' | yourname = 'Monty ' | +--------------------+----------------------+ | 1 | 1 | +--------------------+----------------------+ 1 row in set (0.00 sec)
This is true for all MySQL versions, and it makes no difference
whether your version trims trailing spaces from
VARCHAR
values before storing
them. Nor does the server SQL mode make any difference in this
regard.
For more information about MySQL character sets and collations, see Section 9.1, “Character Set Support”.
For those cases where trailing pad characters are stripped or
comparisons ignore them, if a column has an index that requires
unique values, inserting into the column values that differ only
in number of trailing pad characters will result in a
duplicate-key error. For example, if a table contains
'a'
, an attempt to store
'a '
causes a duplicate-key error.
The BINARY
attribute is sticky. This means
that if a column marked BINARY
is used in an
expression, the whole expression is treated as a
BINARY
value.
MySQL may silently change the type of a
CHAR
or
VARCHAR
column at table creation
time. See Section 12.1.5.2, “Silent Column Specification Changes”.
The BINARY
and
VARBINARY
types are similar to
CHAR
and
VARCHAR
, except that they contain
binary strings rather than nonbinary strings. That is, they
contain byte strings rather than character strings. This means
that they have no character set, and sorting and comparison are
based on the numeric values of the bytes in the values.
The permissible maximum length is the same for
BINARY
and
VARBINARY
as it is for
CHAR
and
VARCHAR
, except that the length
for BINARY
and
VARBINARY
is a length in bytes
rather than in characters.
Before MySQL 4.1.2,
BINARY(
and
M
)VARBINARY(
are
treated as M
)CHAR(
and
M
)
BINARYVARCHAR(
.
As of MySQL 4.1.2, the M
) BINARYBINARY
and
VARBINARY
data types are distinct
from the CHAR BINARY
and VARCHAR
BINARY
data types. For the latter types, the
BINARY
attribute does not cause the column to
be treated as a binary string column. Instead, it causes the
binary collation for the column character set to be used, and
the column itself contains nonbinary character strings rather
than binary byte strings. For example, in 4.1.2 and up,
CHAR(5) BINARY
is treated as CHAR(5)
CHARACTER SET latin1 COLLATE latin1_bin
, assuming that
the default character set is latin1
. This
differs from BINARY(5)
, which stores 5-bytes
binary strings that have no character set or collation. For
information about differences between nonbinary string binary
collations and binary strings, see
Section 9.1.7.6, “The _bin and binary Collations”.
If you assign a value to a BINARY
or VARBINARY
column that exceeds
the column's maximum length, the value is truncated to fit. If
the truncated characters are not spaces, a warning is generated.
The handling of trailing spaces is the same for
BINARY
and
VARBINARY
as it is for
CHAR
and
VARCHAR
. When
BINARY
values are stored, they
are right-padded with spaces to the specified length. When
BINARY
values are retrieved,
trailing spaces are removed. For
VARBINARY
, trailing spaces are
removed when values are stored.
For those cases where trailing pad bytes are stripped or
comparisons ignore them, if a column has an index that requires
unique values, inserting into the column values that differ only
in number of trailing pad bytes will result in a duplicate-key
error. For example, if a table contains 'a'
,
an attempt to store 'a '
causes a
duplicate-key error. Trailing spaces are significant in
comparisons.
You should consider the preceding padding and stripping
characteristics carefully if you plan to use one of these data
types for storing binary data and you require that the value
retrieved be exactly the same as the value stored. The following
example illustrates how space-padding of
BINARY
values affects column
value comparisons:
mysql>CREATE TABLE t (c BINARY(3));
Query OK, 0 rows affected (0.00 sec) mysql>INSERT INTO t SET c = 'a ';
Query OK, 1 row affected (0.00 sec) mysql>SELECT HEX(c), c = 'a', c = 'a ' from t;
+--------+---------+-----------+ | HEX(c) | c = 'a' | c = 'a ' | +--------+---------+-----------+ | 61 | 1 | 0 | +--------+---------+-----------+ 1 row in set (0.00 sec)
If the value retrieved must be the same as the value specified
for storage with no padding, it might be preferable to use one
of the BLOB
data types instead.
A BLOB
is a binary large object
that can hold a variable amount of data. The four
BLOB
types are
TINYBLOB
,
BLOB
,
MEDIUMBLOB
, and
LONGBLOB
. These differ only in
the maximum length of the values they can hold. The four
TEXT
types are
TINYTEXT
,
TEXT
,
MEDIUMTEXT
, and
LONGTEXT
. These
correspond to the four BLOB
types
and have the same maximum lengths and storage requirements. See
Section 10.5, “Data Type Storage Requirements”.
BLOB
values are treated as binary
strings (byte strings). They have no character set, and sorting
and comparison are based on the numeric values of the bytes in
column values. TEXT
values are
treated as nonbinary strings (character strings). They have a
character set, and values are sorted and compared based on the
collation of the character set assigned to the column as of
MySQL 4.1. Before 4.1, TEXT
sorting and comparison are based on the collation of the server
character set.
If you assign a value to a BLOB
or TEXT
column that exceeds the
data type's maximum length, the value is truncated to fit and a
warning is generated.
If a TEXT
or
BLOB
column is indexed, index
entry comparisons are not space-padded at the end. This means
that, if the index requires unique values, duplicate-key errors
will not occur for values that differ only in the number of
trailing spaces. For example, if a table contains
'a'
, an attempt to store
'a '
does not cause a duplicate-key
error. (This behavior changes in MySQL 5.0 for
TEXT
columns, such that
comparisons are space-padded.)
In most respects, you can regard a
BLOB
column as a
VARBINARY
column that can be as
big as you like. Similarly, you can regard a
TEXT
column as a
VARCHAR
column.
BLOB
and
TEXT
differ from
VARBINARY
and
VARCHAR
in the following ways:
There is no trailing-space removal for
BLOB
and
TEXT
columns when values are
stored or retrieved. This differs from
VARBINARY
and
VARCHAR
, for which trailing
spaces are removed when values are stored.
On comparisons, TEXT
is space
extended to fit the compared object, exactly like
CHAR
and
VARCHAR
.
You can have indexes on BLOB
and TEXT
columns only as of
MySQL 3.23.2 for MyISAM
tables or MySQL
4.0.14 for InnoDB
tables. Previous
versions of MySQL did not support indexing these data types.
For indexes on BLOB
and
TEXT
columns, you must
specify an index prefix length. For
CHAR
and
VARCHAR
, a prefix length is
optional. See Section 7.4.1, “Column Indexes”.
From MySQL 4.1.0 on, LONG
and LONG
VARCHAR
map to the
MEDIUMTEXT
data type. This is a
compatibility feature. If you use the BINARY
attribute with a TEXT
data type,
the column is assigned the binary collation of the column
character set.
MySQL Connector/ODBC defines BLOB
values as LONGVARBINARY
and
TEXT
values as
LONGVARCHAR
.
Because BLOB
and
TEXT
values can be extremely
long, you might encounter some constraints in using them:
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
--max_sort_length=
option when starting the mysqld server.
See Section 5.1.3, “Server System Variables”.
N
As of MySQL 4.0.3, you can make more bytes significant in
sorting or grouping by increasing the value of
max_sort_length
at runtime.
Any client can change the value of its session
max_sort_length
variable:
mysql>SET max_sort_length = 2000;
mysql>SELECT id, comment FROM t
->ORDER BY comment;
Another way to use GROUP BY
or
ORDER BY
on a
BLOB
or
TEXT
column containing long
values when you want more than
max_sort_length
bytes to be
significant is to convert the column value into a
fixed-length object. The standard way to do this is with the
SUBSTRING()
function. For
example, the following statement causes 2000 bytes of the
comment
column to be taken into account
for sorting:
mysql>SELECT id, SUBSTRING(comment,1,2000) FROM t
->ORDER BY SUBSTRING(comment,1,2000);
Before MySQL 3.23.2, you can group on an expression
involving BLOB
or
TEXT
values by using a column
alias or by specifying the column position:
mysql>SELECT id, SUBSTRING(comment,1,2000) AS b
->FROM
mysql>tbl_name
GROUP BY b;SELECT id, SUBSTRING(comment,1,2000)
->FROM
tbl_name
GROUP BY 2;
Instances of BLOB
or
TEXT
columns in the result of
a query that is processed using a temporary table causes the
server to use a table on disk rather than in memory because
the MEMORY
storage engine does not
support those data types (see
Section 7.7.4, “How MySQL Uses Internal Temporary Tables”). Use of disk
incurs a performance penalty, so include
BLOB
or
TEXT
columns in the query
result only if they are really needed. For example, avoid
using SELECT
*
, which selects all columns.
The maximum size of a BLOB
or
TEXT
object is determined by
its type, but the largest value you actually can 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 by changing the value
of the max_allowed_packet
variable, but you must do so for both the server and your
client program. For example, both mysql
and mysqldump enable you to change the
client-side
max_allowed_packet
value.
See Section 7.8.2, “Tuning Server Parameters”,
Section 4.5.1, “mysql — The MySQL Command-Line Tool”, and Section 4.5.4, “mysqldump — A Database Backup Program”.
You may also want to compare the packet sizes and the size
of the data objects you are storing with the storage
requirements, see Section 10.5, “Data Type Storage Requirements”
Each BLOB
or
TEXT
value is represented
internally by a separately allocated object. This is in contrast
to all other data types, for which storage is allocated once per
column when the table is opened.
In some cases, it may be desirable to store binary data such as
media files in BLOB
or
TEXT
columns. You may find
MySQL's string handling functions useful for working with such
data. See Section 11.5, “String Functions”. For security and
other reasons, it is usually preferable to do so using
application code rather than giving application users the
FILE
privilege. You can discuss
specifics for various languages and platforms in the MySQL
Forums (http://forums.mysql.com/).
An ENUM
is a string object with a
value chosen from a list of permitted values that are enumerated
explicitly in the column specification at table creation time.
An enumeration value must be a quoted string literal; it may not
be an expression, even one that evaluates to a string value. For
example, you can create a table with an
ENUM
column like this:
CREATE TABLE sizes ( name ENUM('small', 'medium', 'large') );
However, this version of the previous
CREATE TABLE
statement does
not work:
CREATE TABLE sizes ( c1 ENUM('small', CONCAT('med','ium'), 'large') );
You also may not employ a user variable as an enumeration value. This pair of statements do not work:
SET @mysize = 'medium'; CREATE TABLE sizes ( name ENUM('small', @mysize, 'large') );
If you wish to use a number as an enumeration value, you must enclose it in quotation marks.
The value may also be the empty string (''
)
or NULL
under certain circumstances:
If you insert an invalid value into an
ENUM
(that is, a string not
present in the list of permitted values), the empty string
is inserted instead as a special error value. This string
can be distinguished from a “normal” empty
string by the fact that this string has the numeric value 0.
More about this later.
If strict SQL mode is enabled, attempts to insert invalid
ENUM
values result in an
error.
If an ENUM
column is declared
to permit NULL
, the
NULL
value is a legal value for the
column, and the default value is NULL
. If
an ENUM
column is declared
NOT NULL
, its default value is the first
element of the list of permitted values.
Each enumeration value has an index:
Values from the list of permissible elements in the column specification are numbered beginning with 1.
The index value of the empty string error value is 0. This
means that you can use the following
SELECT
statement to find rows
into which invalid ENUM
values were assigned:
mysql> SELECT * FROM tbl_name
WHERE enum_col
=0;
The index of the NULL
value is
NULL
.
The term “index” here refers only to position within the list of enumeration values. It has nothing to do with table indexes.
For example, a column specified as ENUM('one', 'two',
'three')
can have any of the values shown here. 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 65,535 elements.
Starting from MySQL 3.23.51, trailing spaces are automatically
deleted from ENUM
member values
in the table definition when a table is created.
When retrieved, values stored into an
ENUM
column are displayed using
the lettercase that was used in the column definition. Before
MySQL 4.1.1, lettercase is irrelevant when you assign values to
an ENUM
column. As of 4.1.1,
ENUM
columns can be assigned a
character set and collation. For binary or case-sensitive
collations, lettercase does matter when you assign values to the
column.
If you retrieve an ENUM
value in
a numeric context, the column value's index is returned. For
example, you can retrieve numeric values from an
ENUM
column like this:
mysql> SELECT enum_col
+0 FROM tbl_name
;
If you store a number into an
ENUM
column, the number is
treated as the index into the possible values, and the value
stored is the enumeration member with that index. (However, this
does not work with
LOAD DATA
, which treats all input
as strings.) If the numeric value is quoted, it is still
interpreted as an index if there is no matching string in the
list of enumeration values. For these reasons, it is not
advisable to define an ENUM
column with enumeration values that look like numbers, because
this can easily become confusing. For example, the following
column has enumeration members with string values of
'0'
, '1'
, and
'2'
, but numeric index values of
1
, 2
, and
3
:
numbers ENUM('0','1','2')
If you store 2
, it is interpreted as an index
value, and becomes '1'
(the value with index
2). If you store '2'
, it matches an
enumeration value, so it is stored as '2'
. If
you store '3'
, it does not match any
enumeration value, so it is treated as an index and becomes
'2'
(the value with index 3).
mysql>INSERT INTO t (numbers) VALUES(2),('2'),('3');
mysql>SELECT * FROM t;
+---------+ | numbers | +---------+ | 1 | | 2 | | 2 | +---------+
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 nonempty strings, and
NULL
values sort before all other enumeration
values. If you expect sorting to be done alphabetically, you
should specify the ENUM
list in
alphabetic order. You can also use GROUP BY CAST(col AS
CHAR)
or GROUP BY CONCAT(col)
to
make sure that the column is sorted lexically rather than by
index number.
Functions such as SUM()
or
AVG()
that expect a numeric
argument cast the argument to a number if necessary. For
ENUM
values, the cast operation
causes the index number to be used.
If you want to determine all possible values for an
ENUM
column, use SHOW
COLUMNS FROM
and parse the
tbl_name
LIKE
enum_col
ENUM
definition in the
Type
column of the output.
A SET
is a string object that can
have zero or more values, each of which must be chosen from a
list of permitted 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
should not 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.
Starting from MySQL 3.23.51, trailing spaces are automatically
deleted from SET
member values in
the table definition when a table is created.
When retrieved, values stored into a
SET
column are displayed using
the lettercase that was used in the column definition. Before
MySQL 4.1.1, lettercase is irrelevant when you assign values to
an SET
column. As of 4.1.1,
SET
columns can be assigned a
character set and collation. For binary or case-sensitive
collations, lettercase does matter when you assign values to the
column.
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. For example, you can
retrieve numeric values from a
SET
column like this:
mysql> SELECT set_col
+0 FROM tbl_name
;
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. For a
column specified as SET('a','b','c','d')
, the
members have the following decimal and binary 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, the first and fourth
SET
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 does not matter how many times a given element is listed
in the value. When the value is retrieved later, each element in
the value appears once, with elements listed according to the
order in which they were specified at table creation time. For
example, suppose that a column is specified as
SET('a','b','c','d')
:
mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
If you insert the values 'a,d'
,
'd,a'
, 'a,d,d'
,
'a,d,a'
, and 'd,a,d'
:
mysql> INSERT INTO myset (col) VALUES
-> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
Then all these values appear as 'a,d'
when
retrieved:
mysql> SELECT col FROM myset;
+------+
| col |
+------+
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
+------+
5 rows in set (0.04 sec)
If you set a SET
column to an
unsupported value, the value is ignored and a warning is issued:
mysql>INSERT INTO myset (col) VALUES ('a,d,d,s');
Query OK, 1 row affected, 1 warning (0.03 sec) mysql>SHOW WARNINGS;
+---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1265 | Data truncated for column 'col' at row 1 | +---------+------+------------------------------------------+ 1 row in set (0.04 sec) mysql>SELECT col FROM myset;
+------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 6 rows in set (0.01 sec)
If strict SQL mode is enabled, attempts to insert invalid
SET
values result in an error.
SET
values are sorted
numerically. NULL
values sort before
non-NULL
SET
values.
Functions such as SUM()
or
AVG()
that expect a numeric
argument cast the argument to a number if necessary. For
SET
values, the cast operation
causes the numeric value to be used.
Normally, you search for SET
values using the FIND_IN_SET()
function or the LIKE
operator:
mysql>SELECT * FROM
mysql>tbl_name
WHERE FIND_IN_SET('value
',set_col
)>0;SELECT * FROM
tbl_name
WHEREset_col
LIKE '%value
%';
The first statement finds rows where
set_col
contains the
value
set member. The second is
similar, but not the same: It finds rows where
set_col
contains
value
anywhere, even as a substring
of another set member.
The following statements also are legal:
mysql>SELECT * FROM
mysql>tbl_name
WHEREset_col
& 1;SELECT * FROM
tbl_name
WHEREset_col
= 'val1
,val2
';
The first of these statements looks for values containing the
first set member. The second looks for an exact match. Be
careful with comparisons of the second type. Comparing set
values to
'
returns different results than comparing values to
val1
,val2
''
.
You should specify the values in the same order in which they
are listed in the column definition.
val2
,val1
'
If you want to determine all possible values for a
SET
column, use SHOW
COLUMNS FROM
and parse the
tbl_name
LIKE
set_col
SET
definition in the
Type
column of the output.
The storage requirements for each data type supported by MySQL are listed here by category.
The maximum size of a row in a MyISAM
table is
65,535 bytes. (However, each BLOB
or TEXT
column contributes only 9
to 12 bytes toward this size.) This limitation may be shared by
other storage engines as well. See
Chapter 13, Storage Engines, for more information.
For tables using the NDBCLUSTER
storage engine, there is the factor of 4-byte
alignment to be taken into account when calculating
storage requirements. This means that all
NDB
data storage is done in
multiples of 4 bytes. Thus, a column value that would take 15
bytes in a table using a storage engine other than
NDB
requires 16 bytes in an
NDB
table. This requirement applies
in addition to any other considerations that are discussed in
this section. For example, in
NDBCLUSTER
tables, the
TINYINT
,
SMALLINT
,
MEDIUMINT
, and
INTEGER
(INT
) column types each require 4
bytes storage per record due to the alignment factor.
An exception to this rule is the
BIT
type, which is
not 4-byte aligned. In MySQL Cluster
tables, a BIT(
column takes M
)M
bits of storage space.
However, if a table definition contains 1 or more
BIT
columns (up to 32
BIT
columns), then
NDBCLUSTER
reserves 4 bytes (32
bits) per row for these. If a table definition contains more
than 32 BIT
columns (up to 64
such columns), then NDBCLUSTER
reserves 8 bytes (that is, 64 bits) per row.
In addition, while a NULL
itself does not
require any storage space,
NDBCLUSTER
reserves 4 bytes per row
if the table definition contains any columns defined as
NULL
, up to 32 NULL
columns. (If a MySQL Cluster table is defined with more than 32
NULL
columns up to 64 NULL
columns, then 8 bytes per row is reserved.)
When calculating storage requirements for MySQL Cluster tables,
you must also remember that every table using the
NDBCLUSTER
storage engine requires a
primary key; if no primary key is defined by the user, then a
“hidden” primary key will be created by
NDB
. This hidden primary key consumes
31-35 bytes per table record.
You may find the ndb_size.pl
utility to be
useful for estimating NDB
storage
requirements. This Perl script connects to a current MySQL
(non-Cluster) database and creates a report on how much space that
database would require if it used the
NDBCLUSTER
storage engine. See
Section 15.4.18, “ndb_size.pl — NDBCLUSTER Size Requirement Estimator”, for more
information.
Data Type | Storage Required |
---|---|
TINYINT | 1 byte |
SMALLINT | 2 bytes |
MEDIUMINT | 3 bytes |
INT ,
INTEGER | 4 bytes |
BIGINT | 8 bytes |
FLOAT( | 4 bytes if 0 <= p <= 24, 8 bytes if 25
<= p <= 53 |
FLOAT | 4 bytes |
DOUBLE [PRECISION] ,
REAL | 8 bytes |
DECIMAL( ,
NUMERIC( | Varies; see following discussion |
In MySQL versions up to and including 4.1,
DECIMAL
columns are represented as
strings and their storage requirements are:
M
+2 bytes, if
D
> 0
bytes, if
M
+1D
= 0
D
+2, if
M
<
D
For details about internal representation of temporal values, see MySQL Internals: Important Algorithms and Structures.
In the following table, M
represents
the declared column length in characters for nonbinary string
types and bytes for binary string types.
L
represents the actual length in bytes
of a given string value.
Data Type | Storage Required |
---|---|
CHAR( | M × w bytes,
0 <= 255, where w is
the number of bytes required for the maximum-length
character in the character set |
BINARY( | M bytes, 0 <=
255 |
VARCHAR( ,
VARBINARY( | L + 1 bytes, 0 <=
255 |
TINYBLOB ,
TINYTEXT | L + 1 bytes, where
L <
28 |
BLOB , TEXT | L + 2 bytes, where
L <
216 |
MEDIUMBLOB ,
MEDIUMTEXT | L + 3 bytes, where
L <
224 |
LONGBLOB ,
LONGTEXT | L + 4 bytes, where
L <
232 |
ENUM(' | 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum) |
SET(' | 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum) |
Variable-length string types are stored using a length prefix plus
data. The length prefix requires from one to four bytes depending
on the data type, and the value of the prefix is
L
(the byte length of the string). For
example, storage for a MEDIUMTEXT
value requires L
bytes to store the
value plus three bytes to store the length of the value.
As of MySQL 4.1, to calculate the number of bytes used to store a
particular CHAR
,
VARCHAR
, or
TEXT
column value, you must take
into account the character set used for that column and whether
the value contains multi-byte characters. In particular, when
using the utf8
Unicode character set, you must
keep in mind that not all characters use the same number of bytes
and can require up to three bytes per character. For a breakdown
of the storage used for different categories of
utf8
characters, see
Section 9.1.9, “Unicode Support”.
VARCHAR
and the
BLOB
and
TEXT
types are variable-length
types. For each, the storage requirements depend on the actual
length of column values (represented by
L
in the preceding table), rather than
on the type's maximum possible size. For example, a
VARCHAR(10)
column can hold a string with a
maximum length of 10 characters. The actual storage required is
the length of the string (L
), plus one
byte to record the length of the string. For the string
'abcd'
, L
is 4 and
the storage requirement is five bytes.
The NDBCLUSTER
engine supports only
fixed-width columns. This means that a
VARCHAR
column from a table in a
MySQL Cluster will behave almost as if it were of type
CHAR
(except that each record
still has one extra byte overhead). For example, in an
NDB
table,
each record in a column declared as
VARCHAR(100)
will take up 101 bytes for
storage, regardless of the length of the string actually stored
in any given record.
TEXT
and
BLOB
columns are implemented
differently in the NDBCLUSTER
storage
engine, wherein each record in a
TEXT
column is made up of two
separate parts. One of these is of fixed size (256 bytes), and is
actually stored in the original table. The other consists of any
data in excess of 256 bytes, which is stored in a hidden table.
The records in this second table are always 2,000 bytes long. This
means that the size of a TEXT
column is 256 if size
<= 256 (where
size
represents the size of the
record); otherwise, the size is 256 +
.
size
+ (2000 -
(size
- 256) % 2000)
The size of an ENUM
object is
determined by the number of different enumeration values. One byte
is used for enumerations with up to 255 possible values. Two bytes
are used for enumerations having between 256 and 65,535 possible
values. See Section 10.4.4, “The ENUM Type”.
The size of a SET
object is
determined by the number of different set members. If the set size
is N
, the object occupies
(
bytes,
rounded up to 1, 2, 3, 4, or 8 bytes. A
N
+ 7)/8SET
can have a maximum of 64
members. See Section 10.4.5, “The SET Type”.
For the most efficient use of storage, try to use the most precise
type in all cases. For example, if an integer column is used for
values in the range from 1
to
99999
, MEDIUMINT UNSIGNED
is
the best type. Of the types that represent all the required
values, it uses the least amount of storage.
For earlier MySQL versions, accurate representation of monetary
values was a common problem. In these MySQL versions, you should
also use the DECIMAL
type. In this
case the value is stored as a string, so no loss of accuracy
should occur on storage. However, calculations on these
DECIMAL
values are done using
double-precision operations. If accuracy is not too important or
if speed is 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 enables
you to do all calculations with 64-bit integers and then convert
results back to floating-point values only when necessary.
PROCEDURE ANALYSE
can be used to obtain
suggestions for optimal column data types. For more information,
see Section 18.3.1, “PROCEDURE ANALYSE”.
To make it easier to use code written for SQL implementations from other vendors, MySQL maps data types as shown in the following table. These mappings make it easier to import table definitions from other database systems into MySQL.
Other Vendor Type | MySQL Type |
---|---|
BINARY( | CHAR( (before
MySQL 4.1.2) |
BOOL | TINYINT |
BOOLEAN | TINYINT |
CHARACTER VARYING( | VARCHAR( |
FIXED | DECIMAL (MySQL 4.1.0 on) |
FLOAT4 | FLOAT |
FLOAT8 | DOUBLE |
INT1 | TINYINT |
INT2 | SMALLINT |
INT3 | MEDIUMINT |
INT4 | INT |
INT8 | BIGINT |
LONG VARBINARY | MEDIUMBLOB |
LONG VARCHAR | MEDIUMTEXT |
LONG | MEDIUMTEXT (MySQL 4.1.0 on) |
MIDDLEINT | MEDIUMINT |
NUMERIC | DECIMAL |
VARBINARY( | VARCHAR( (before
MySQL 4.1.2) |
As of MySQL 4.1.2, BINARY
and
VARBINARY
are distinct data types
and are not converted to CHAR BINARY
and
VARCHAR BINARY
.
Data type mapping occurs at table creation time, after which the
original type specifications are discarded. If you create a table
with types used by other vendors and then issue a
DESCRIBE
statement, MySQL reports the table structure using the equivalent
MySQL types. For example:
tbl_name
mysql>CREATE TABLE t (a BOOL, b FLOAT8, c LONG VARCHAR, d NUMERIC);
Query OK, 0 rows affected (0.00 sec) mysql>DESCRIBE t;
+-------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+-------+ | a | tinyint(1) | YES | | NULL | | | b | double | YES | | NULL | | | c | mediumtext | YES | | NULL | | | d | decimal(10,0) | YES | | NULL | | +-------+---------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)