Table of Contents
MySQL supports a number of data types in several categories: numeric types, date and time types, and string (character) types. This chapter first gives an overview of these data types, and then provides 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 allowable formats in which you can specify values.
MySQL 4.1 and up also supports extensions for handing spatial data. Chapter 16, Spatial Extensions, provides information about these data types.
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. For string types, M is
the maximum length. The maximum allowable 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 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, see Section 10.2, “Numeric Types”. Storage requirements are given in 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 allow the UNSIGNED
attribute also allow 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.9, “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. Non-zero 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.
We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release.
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.
FLOAT[(
M,D)]
[UNSIGNED] [ZEROFILL]
A small (single-precision) floating-point number. Allowable
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 allowed 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 A.1.5.7, “Solving Problems with No Matching Rows”.
DOUBLE[(
M,D)]
[UNSIGNED] [ZEROFILL]
A normal-size (double-precision) floating-point number.
Allowable 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 allowed 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)
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.
A summary of the temporal data types follows. For additional information, see Section 10.3, “Date and Time Types”. Storage requirements are given in Section 10.5, “Data Type Storage Requirements”. Functions that operate on temporal values are described at Section 11.6, “Date and Time Functions”.
For the DATETIME and DATE
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 allows
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 allows 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-09
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.
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, you should add +0 to the
timestamp column.
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 allows 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 allows 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 allowable
values are 1901 to
2155, and 0000. In
two-digit format, the allowable values are
70 to 69, representing
years from 1970 to 2069. MySQL displays
YEAR values in YYYY
format, but allows you to assign 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 non-numeric character.) To
work around this problem, you can 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, see Section 10.4, “String Types”. Storage requirements are given in 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.1, “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.
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 was disallowed for the TEXT
types. For CHAR and
VARCHAR, 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 then 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 allows 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 non-binary 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 non-binary 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
valueNOW() 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 of 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. For numeric type
storage requirements, see Section 10.5, “Data Type Storage Requirements”.
The numeric types used for the results of calculations depends on the operations being performed and the numeric types of the operands; for more information, see Section 11.5.1, “Arithmetic Operators”.
As an extension to the SQL standard, MySQL also supports the
integer types TINYINT,
MEDIUMINT, and BIGINT. The
following table shows the required storage and range for each of
the integer types.
| Type | Bytes | Minimum Value | Maximum Value |
| (Signed/Unsigned) | (Signed/Unsigned) | ||
TINYINT | 1 | -128 | 127 |
0 | 255 | ||
SMALLINT | 2 | -32768 | 32767 |
0 | 65535 | ||
MEDIUMINT | 3 | -8388608 | 8388607 |
0 | 16777215 | ||
INT | 4 | -2147483648 | 2147483647 |
0 | 4294967295 | ||
BIGINT | 8 | -9223372036854775808 | 9223372036854775807 |
0 | 18446744073709551615 |
Another extension is supported by MySQL for optionally specifying
the display width of integer data types in parentheses following
the base keyword for the type (for example,
INT(4)). This optional display width is used to
display integer values having a width less than the width
specified for the column by left-padding them with spaces.
The display width does not constrain the
range of values that can be stored in the column, nor the number
of digits that are displayed for values having a width exceeding
that specified for the column. For example, a column specified as
SMALLINT(3) has the usual
SMALLINT range of -32768 to
32767, and values outside the range allowed by
three characters are displayed using more than three characters.
When used in conjunction with the optional extension attribute
ZEROFILL, the default padding of spaces is
replaced with zeros. For example, for a column declared as
INT(5) ZEROFILL, a value of
4 is retrieved as 00004.
Note that if you store larger values than the display width in an
integer column, you may experience problems when MySQL generates
temporary tables for some complicated joins, because in these
cases MySQL assumes that the data fits into the original column
width.
The ZEROFILL attribute is ignored when a
column is involved in expressions or UNION
queries.
All integer types can have an optional (non-standard) attribute
UNSIGNED. Unsigned values can be used when you
want to allow only non-negative numbers in a column and 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. However, 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
value+1value is the largest value for the
column currently in the table. AUTO_INCREMENT
sequences begin with 1.
For floating-point data types, MySQL uses four bytes for single-precision values and eight bytes for double-precision values.
The FLOAT and DOUBLE data
types are used to represent approximate numeric data values. For
FLOAT the SQL standard allows 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 allows a non-standard 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.
MySQL treats DOUBLE as a synonym for
DOUBLE PRECISION (a non-standard extension).
MySQL also treats REAL as a synonym for
DOUBLE PRECISION (a non-standard variation),
unless the REAL_AS_FLOAT SQL mode is enabled.
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.
The DECIMAL and NUMERIC data
types are used to store exact numeric data values. In MySQL,
NUMERIC is implemented as
DECIMAL. These types are used to store values
for which it is important to preserve exact precision, for example
with monetary data.
Through version 4.1, MySQL stores DECIMAL and
NUMERIC 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).
When declaring a DECIMAL or
NUMERIC column, 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. If the scale is 0, DECIMAL
and NUMERIC values contain no decimal point or
fractional part.
Standard SQL requires that the salary column be
able to store any value with five digits and two decimals. In this
case, therefore, the range of values that can be stored in the
salary column is 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 allowed to decide the value of
M,0)M. As of MySQL 3.23.6, both of these
variant forms of the DECIMAL and
NUMERIC data types are supported. The default
value of M is 10. Before 3.23.6,
M and D both
must be specified explicitly.
The maximum range of DECIMAL and
NUMERIC values is the same as for
DOUBLE, but the actual range for a given
DECIMAL or NUMERIC 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 allowed 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 allowable number of digits.)
When asked to store a value in a numeric column that is outside the data type's allowable range, 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.
Conversions that occur due to clipping are reported as
“warnings” for ALTER TABLE,
LOAD DATA INFILE, UPDATE,
and multiple-row INSERT statements.
The date and time types for representing temporal values are
DATETIME, DATE,
TIMESTAMP, TIME, 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 '1999-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 allows 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 '1999-00-00' or
'1999-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 allows 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.
Here are some general considerations to keep in mind 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). Only the formats described in the following sections are supported. It is expected that you supply legal values. Unpredictable results may occur if you use values in other formats.
Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using the following 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.
Although MySQL tries to interpret values in several formats,
dates always must 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').
MySQL automatically converts a date or time type 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
(as described at the beginning of this section), 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.
| Data Type | “Zero” Value |
DATETIME | '0000-00-00 00:00:00' |
DATE | '0000-00-00' |
TIMESTAMP (4.1 and up) | '0000-00-00 00:00:00' |
TIMESTAMP (before 4.1) | 00000000000000 |
TIME | '00:00:00' |
YEAR | 0000 |
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.
“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 DATETIME, DATE, and
TIMESTAMP types are related. This section
describes their characteristics, how they are similar, and how
they differ.
The DATETIME type is used when you need
values that contain both date and time information. 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'.
The DATE type is used when you need only a
date value, without a time part. MySQL retrieves and displays
DATE values in
'YYYY-MM-DD' format. The supported range is
'1000-01-01' to
'9999-12-31'.
For the DATETIME and DATE
range descriptions, “supported” means that although
earlier values might work, there is no guarantee.
The TIMESTAMP data type has a range of
'1970-01-01 00:00:01' UTC to
'2038-01-09 03:14:07' UTC. It has varying
properties that depend on the MySQL version, as described later
in this section.
You can specify DATETIME,
DATE, and TIMESTAMP values
using any of a common set of formats:
As a string in either 'YYYY-MM-DD
HH:MM:SS' or 'YY-MM-DD
HH:MM:SS' format. A “relaxed” syntax
is allowed: Any punctuation character may be used as the
delimiter between date parts or time parts. For example,
'98-12-31 11:30:45', '98.12.31
11+30+45', '98/12/31 11*30*45',
and '98@12@31 11^30^45' are equivalent.
As a string in either 'YYYY-MM-DD' or
'YY-MM-DD' format. A
“relaxed” syntax is allowed here, too. For
example, '98-12-31',
'98.12.31',
'98/12/31', and
'98@12@31' are equivalent.
As a string with no delimiters in either
'YYYYMMDDHHMMSS' or
'YYMMDDHHMMSS' format, provided that the
string makes sense as a date. For example,
'19970523091528' and
'970523091528' are interpreted as
'1997-05-23 09:15:28', but
'971122129015' is illegal (it has a
nonsensical minute part) and becomes '0000-00-00
00:00:00'.
As a string with no delimiters in either
'YYYYMMDD' or 'YYMMDD'
format, provided that the string makes sense as a date. For
example, '19970523' and
'970523' are interpreted as
'1997-05-23', but
'971332' is illegal (it has nonsensical
month and day parts) and becomes
'0000-00-00'.
As a number in either YYYYMMDDHHMMSS or
YYMMDDHHMMSS format, provided that the
number makes sense as a date. For example,
19830905132800 and
830905132800 are interpreted as
'1983-09-05 13:28:00'.
As a number in either YYYYMMDD or
YYMMDD format, provided that the number
makes sense as a date. For example,
19830905 and 830905
are interpreted as '1983-09-05'.
As the result of a function that returns a value that is
acceptable in a DATETIME,
DATE, or TIMESTAMP
context, such as NOW() or
CURRENT_DATE.
A microseconds part is allowable in temporal values in some
contexts, such as in literal values, and in the arguments to or
return values from some temporal functions. Microseconds are
specified as a trailing .uuuuuu part in the
value. Example:
mysql> SELECT MICROSECOND('2010-12-10 14:12:09.019473');
+-------------------------------------------+
| MICROSECOND('2010-12-10 14:12:09.019473') |
+-------------------------------------------+
| 19473 |
+-------------------------------------------+
However, microseconds cannot be stored into a column of any temporal data type. Any microseconds part is discarded.
As of MySQL 5.0.8, conversion of TIME or
DATETIME values to numeric form (for example,
by adding +0) results in a double 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.
Illegal DATETIME, DATE, or
TIMESTAMP values are converted to the
“zero” value of the appropriate type
('0000-00-00 00:00:00',
'0000-00-00', or
00000000000000).
For values specified as strings that include date part
delimiters, it is not necessary to specify two digits for month
or day values that are less than 10.
'1979-6-9' is the same as
'1979-06-09'. Similarly, for values specified
as strings that include time part delimiters, it is not
necessary to specify two digits for hour, minute, or second
values that are less than 10.
'1979-10-30 1:2:3' is the same as
'1979-10-30 01:02:03'.
Values specified as numbers should be 6, 8, 12, or 14 digits
long. If a number is 8 or 14 digits long, it is assumed to be in
YYYYMMDD or YYYYMMDDHHMMSS
format and that the year is given by the first 4 digits. If the
number is 6 or 12 digits long, it is assumed to be in
YYMMDD or YYMMDDHHMMSS
format and that the year is given by the first 2 digits. Numbers
that are not one of these lengths are interpreted as though
padded with leading zeros to the closest length.
Values specified as non-delimited strings are interpreted using
their length as given. If the string is 8 or 14 characters long,
the year is assumed to be given by the first 4 characters.
Otherwise, the year is assumed to be given by the first 2
characters. The string is interpreted from left to right to find
year, month, day, hour, minute, and second values, for as many
parts as are present in the string. This means you should not
use strings that have fewer than 6 characters. For example, if
you specify '9903', thinking that represents
March, 1999, MySQL inserts a “zero” date into your
table. This occurs because the year and month values are
99 and 03, but the day
part is completely missing, so the value is not a legal date.
However, as of MySQL 3.23, you can explicitly specify a value of
zero to represent missing month or day parts. For example, you
can use '990300' to insert the value
'1999-03-00'.
You can to some extent assign values of one date type to an object of a different date type. However, there may be some alteration of the value or loss of information:
If you assign a DATE value to a
DATETIME or TIMESTAMP
object, the time part of the resulting value is set to
'00:00:00' because the
DATE value contains no time information.
If you assign a DATETIME or
TIMESTAMP value to a
DATE object, the time part of the
resulting value is deleted because the
DATE type stores no time information.
Remember that although DATETIME,
DATE, and TIMESTAMP
values all can be specified using the same set of formats,
the types do not all have the same range of values. For
example, TIMESTAMP values cannot be
earlier than 1970 UTC or later than
'2038-01-09 03:14:07' UTC. This means
that a date such as '1968-01-01', while
legal as a DATETIME or
DATE value, is not valid as a
TIMESTAMP value and is converted to
0.
Be aware of certain pitfalls when specifying date values:
The relaxed format allowed for values specified as strings
can be deceiving. For example, a value such as
'10:11:12' might look like a time value
because of the “:”
delimiter, but if used in a date context is interpreted as
the year '2010-11-12'. 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 allows 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 the following 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_nameSETtimestamp_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 of the possiblities:
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
allowed. Its range of values does not align with those of
the TIMESTAMP column anyway unless the
current time zone is UTC.)
The order of the DEFAULT and
ON UPDATE attributes 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
allowed 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 allows
NULL values, assigning
NULL sets it to
NULL, not to the current timestamp.
The following table contains several
TIMESTAMP columns that allow
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 allows
NULL values 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 allow 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).
You can specify TIME values in a variety of
formats:
As a string in 'D HH:MM:SS.fraction'
format. You can also use one of the following
“relaxed” syntaxes:
'HH:MM:SS.fraction',
'HH:MM:SS', 'HH:MM',
'D HH:MM:SS', 'D
HH:MM', 'D HH', or
'SS'. Here D
represents days and can have a value from 0 to 34. Note that
MySQL does not store the fraction part.
As a string with no delimiters in
'HHMMSS' format, provided that it makes
sense as a time. For example, '101112' is
understood as '10:11:12', but
'109712' is illegal (it has a nonsensical
minute part) and becomes '00:00:00'.
As a number in HHMMSS format, provided
that it makes sense as a time. For example,
101112 is understood as
'10:11:12'. The following alternative
formats are also understood: SS,
MMSS, HHMMSS,
HHMMSS.fraction. Note that MySQL does not
store the fractional part.
As the result of a function that returns a value that is
acceptable in a TIME context, such as
CURRENT_TIME.
A trailing .uuuuuu microseconds part of
TIME values is allowed under the same
conditions as for other temporal values, as described in
Section 10.3.1, “The DATETIME, DATE, and
TIMESTAMP Types”. This includes the property that any
microseconds part is discarded from values stored into
TIME columns.
For TIME values specified as strings that
include a time part delimiter, it is not necessary to specify
two digits for hours, minutes, or seconds values that are less
than 10. '8:3:2' is the
same as '08:03:02'.
Be careful about assigning “short”
TIME values to a TIME
column. Without colons, MySQL interprets values using the
assumption that the rightmost digits represent seconds. (MySQL
interprets TIME values as elapsed time rather
than as time of day.) For example, you might think of
'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'. TIME values
with colons, by contrast, are always treated as time of the day.
That is '11:12' means
'11:12:00', not
'00:11: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.7, “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. For
two-digit format, MySQL displays values with a range of
70 (1970) to 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.
MySQL Server itself has no problems with Year 2000 (Y2K) compliance:
MySQL Server uses Unix time functions that handle dates into
the year 2038 for
TIMESTAMP values. For
DATE and DATETIME
values, dates through the year 9999 are
accepted.
All MySQL date functions are implemented in one source file,
sql/time.cc, and are coded very
carefully to be year 2000-safe.
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.
Although MySQL Server itself is Y2K-safe, you may run into
problems if you use it with applications that are not Y2K-safe.
For example, many old applications store or manipulate years
using two-digit values (which are ambiguous) rather than
four-digit values. This problem may be compounded by
applications that use values such as 00 or
99 as “missing” value
indicators. Unfortunately, these problems may be difficult to
fix because different applications may be written by different
programmers, each of whom may use a different set of conventions
and date-handling functions.
Thus, even though MySQL Server has no Y2K problems, it is the application's responsibility to provide unambiguous input. Any value containing a two-digit year is 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 with ambiguous year values using the
following 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 correct values, you should provide unambiguous input containing four-digit year values.
ORDER BY properly sorts
TIMESTAMP or YEAR values
that have two-digit years.
Some functions like MIN() and
MAX() convert a
TIMESTAMP or 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 TIMESTAMP or
YEAR to four-digit year format or use
something like
MIN(DATE_ADD(timestamp,INTERVAL 0
DAY)).
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 is not affected by
the trimming of trailing spaces from VARCHAR
values before storing them. Nor does the server SQL mode make
any difference in this regard.
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.1, “Silent Column Specification Changes”.
The BINARY and VARBINARY
types are similar to CHAR and
VARCHAR, except that they contain binary
strings rather than non-binary 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 allowable 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 non-binary 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.
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 columns are treated as binary strings
(byte strings). TEXT columns are treated as
non-binary strings (character strings). BLOB
columns have no character set, and sorting and comparison are
based on the numeric values of the bytes in column values.
TEXT columns 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.3, “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, “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->FROMmysql>tbl_nameGROUP BY b;SELECT id, SUBSTRING(comment,1,2000)->FROMtbl_nameGROUP BY 2;
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 allow you to change the
client-side max_allowed_packet value. See
Section 7.5.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.4, “String Functions”. For security and other
reasons, it is usually preferable to do so using application
code rather than allowing 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 allowed 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 quotes.
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 allowed 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 numerical 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 allow
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 allowed values.
Each enumeration value has an index:
Values from the list of allowable 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 non-empty 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 alphabetical 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_colENUM 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
allowed values specified when the table is created.
SET column values that consist of multiple
set members are specified with members separated by commas
(“,”). A consequence of this is
that SET member values 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 of 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 * FROMmysql>tbl_nameWHERE FIND_IN_SET('value',set_col)>0;SELECT * FROMtbl_nameWHEREset_colLIKE '%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 * FROMmysql>tbl_nameWHEREset_col& 1;SELECT * FROMtbl_nameWHEREset_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_colSET definition in the Type
column of the output.
The storage requirements for each of the data types supported by MySQL are listed here by category.
The maximum size of a row in a MySQL table is 65,535 bytes. Each
BLOB and TEXT column
accounts for only nine to twelve bytes toward this size. This
limitation may be shared by other storage engines as well.
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.
In addition, when calculating storage requirements for Cluster
tables, you must 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.10.14, “ndb_size.pl — NDBCLUSTER Size Requirement Estimator”,
for more information.
Storage Requirements for Numeric Types
| 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
M+1D = 0
D+2, if
M <
D
Storage Requirements for Date and Time Types
| Data Type | Storage Required |
DATE | 3 bytes |
TIME | 3 bytes |
DATETIME | 8 bytes |
TIMESTAMP | 4 bytes |
YEAR | 1 byte |
The storage requirements shown in the table arise from the way that MySQL represents temporal values:
DATE: A three-byte integer packed as
DD + MM×32
+ YYYY×16×32
TIME: A three-byte integer packed as
DD×24×3600 +
HH×3600 +
MM×60 + SS
DATETIME: Eight bytes:
A four-byte integer packed as
YYYY×10000 +
MM×100 +
DD
A four-byte integer packed as
HH×10000 +
MM×100 +
SS
TIMESTAMP: A four-byte integer representing
seconds UTC since the epoch ('1970-01-01
00:00:00' UTC)
YEAR: A one-byte integer
Storage Requirements for String Types
In the following table, M represents
the declared column length in characters for non-binary 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 utf8 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.7, “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 allows 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 19.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)