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.
Several of the 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聽11.2, 鈥淣umeric Types鈥. Storage requirements are given in Section聽11.5, 鈥淒ata 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聽11.2, 鈥淣umeric 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
.
Warning: 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聽12.9, 鈥淐ast 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, 鈥淪olving 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.
Note: 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聽11.3, 鈥淒ate and Time Types鈥. Storage requirements are given in Section聽11.5, 鈥淒ata Type Storage Requirements鈥. Functions that operate on temporal values are described at Section聽12.6, 鈥淒ate 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 partway through the year
2038
. 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聽11.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
)
Note: 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聽11.3.1.1, 鈥TIMESTAMP
Properties Prior to MySQL 4.1鈥, and
Section聽11.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聽11.4, 鈥淪tring Types鈥. Storage requirements are given in Section聽11.5, 鈥淒ata 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聽13.1.5.1, 鈥淪ilent 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.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.
Chapter聽10, 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.
The range of M
is 0 to 255
characters (1 to 255 prior to MySQL 3.23).
Note: 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聽10.3.6, 鈥淣ational 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).
CHAR [CHARACTER SET
charset_name
] [COLLATE
collation_name
]
This type is a synonym for CHAR(1)
.
[NATIONAL] VARCHAR(
M
)
[CHARACTER SET charset_name
]
[COLLATE
collation_name
]
A variable-length string. M
represents the maximum column length. The range of
M
is 1 to 255 before MySQL 4.0.2,
and 0 to 255 as of MySQL 4.0.2.
Note: 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
.
The BINARY
type is similar to the
CHAR
type, but stores binary byte strings
rather than non-binary character strings.
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.
This type was added in MySQL 4.1.2.
A BLOB
column with a maximum length of
255 (28 鈥 1) bytes.
TINYTEXT [CHARACTER SET
charset_name
] [COLLATE
collation_name
]
A TEXT
column with a maximum length of
255 (28 鈥 1) characters.
A BLOB
column with a maximum length of
65,535 (216 鈥 1) bytes.
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.
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.
MEDIUMTEXT [CHARACTER SET
charset_name
] [COLLATE
collation_name
]
A TEXT
column with a maximum length of
16,777,215 (224 鈥 1)
characters.
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. From MySQL 4.0, the
maximum allowed length of LONGBLOB
columns depends on the configured maximum packet size in the
client/server protocol and available memory.
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. Up to MySQL 3.23, the client/server protocol
and MyISAM
tables had a limit of 16MB per
communication packet or table row. From MySQL 4.0, the
maximum allowed length of LONGTEXT
columns depends on the configured maximum packet size in the
client/server protocol and available memory.
ENUM('
value1
','value2
',...)
[CHARACTER SET charset_name
]
[COLLATE
collation_name
]
An enumeration. A string object that can have only one
value, chosen from the list of values
'
,
value1
''
,
value2
'...
, NULL
or the
special ''
error value. An
ENUM
column can have a maximum of 65,535
distinct values. ENUM
values are
represented internally as integers.
SET('
value1
','value2
',...)
[CHARACTER SET charset_name
]
[COLLATE
collation_name
]
A set. A string object that can have zero or more values,
each of which must be chosen from the list of values
'
,
value1
''
,
value2
'...
A SET
column can
have a maximum of 64 members. SET
values
are represented internally as integers.
The DEFAULT
clause in a data type specification indicates a default value
for a column. With one exception, the default value must be a
constant; it cannot be a function or an expression. This means,
for example, that you cannot set the default for a date column
to be the value of a function such as value
NOW()
or CURRENT_DATE
. The exception is that you
can specify CURRENT_TIMESTAMP
as the default
for a TIMESTAMP
column as of MySQL 4.1.2. See
Section聽11.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 other than integer types declared with the
AUTO_INCREMENT
attribute, the default is
0
. For an
AUTO_INCREMENT
column, the default value
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聽11.3, 鈥淒ate 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聽11.5, 鈥淒ata Type Storage Requirements鈥.
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.
Note: The
ZEROFILL
attribute is stripped 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.
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 temporary type storage
requirements, see Section聽11.5, 鈥淒ata Type Storage Requirements鈥.
MySQL version 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 varying
properties, depending on the MySQL version. These properties are
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 4.1.13, conversion of DATETIME
values to numeric form (for example, by adding
+0
) results in a double value with a
microseconds part of .000000
:
mysql> SELECT NOW(), NOW()+0;
+---------------------+-----------------------+
| NOW() | NOW()+0 |
+---------------------+-----------------------+
| 2007-04-23 14:21:52 | 20070423142152.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
or later than
2038
. 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
.
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聽11.3.1.2, 鈥TIMESTAMP
Properties as of MySQL 4.1鈥.)
Automatic updating of the first TIMESTAMP
column in a table occurs under any of the following
conditions:
You explicitly set the column to NULL
.
The column is not specified explicitly in an
INSERT
or LOAD DATA
INFILE
statement.
The column is not specified explicitly in an
UPDATE
statement and some other column
changes value. An UPDATE
that sets a
column to the value it does not cause the
TIMESTAMP
column to be updated; if you
set a column to its current value, MySQL ignores the
update for efficiency.
A TIMESTAMP
column other than the first
also can be assigned the current date and time by setting it
to NULL
or to any function that produces
the current date and time (NOW()
,
CURRENT_TIMESTAMP
).
Note that the information in the following discussion applies
to TIMESTAMP
columns only for tables not
created with MAXDB
mode enabled, because
such columns are created as DATETIME
columns.
You can set any TIMESTAMP
column to a value
different from the current date and time by setting it
explicitly to the desired value. This is true even for the
first TIMESTAMP
column. You can use this
property if, for example, you want a
TIMESTAMP
to be set to the current date and
time when you create a row, but not to be changed whenever the
row is updated later:
Let MySQL set the column when the row is created. This initializes it to the current date and time.
When you perform subsequent updates to other columns in
the row, set the TIMESTAMP
column
explicitly to its current value:
UPDATEtbl_name
SETtimestamp_col
=timestamp_col
,other_col1
=new_value1
,other_col2
=new_value2
, ...
Another way to maintain a column that records row-creation
time is to use a DATETIME
column that you
initialize to NOW()
when the row is created
and do not modify for subsequent updates.
TIMESTAMP
values may range from the
beginning of 1970 to partway through the year 2038, with a
resolution of one second. Values are displayed as numbers.
When you store a value in a TIMESTAMP
column, it is assumed to be represented in the current time
zone, and is converted to UTC for storage. When you retrieve
the value, it is converted from UTC back to the local time
zone for display. Before MySQL 4.1.3, the server has a single
time zone. As of 4.1.3, clients can set their own time zones
on a per-connection basis, as described in
Section聽5.10.8, 鈥淢ySQL 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 a TIMESTAMP
column display
as strings rather than as numbers. Also,
DESCRIBE
displays the column definition as
timestamp(19)
, rather than as
timestamp(14)
.
However, 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 change in the ways
described in this section.
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.
Beginning with MySQL 4.1.1, the MySQL server can be also 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;
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.
You can specify which TIMESTAMP
column
to automatically initialize or update to the current date
and time. This need not be the first
TIMESTAMP
column.
The following discussion describes the revised syntax and
behavior. Note that this information applies only to
TIMESTAMP
columns for tables not created
with MAXDB
mode enabled. As noted earlier
in this section, MAXDB
mode causes columns
to be created as DATETIME
columns.
The following items summarize the pre-4.1.2 properties for
TIMESTAMP
initialization and updating:
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.
As of 4.1.2, you have more flexibility in deciding which
TIMESTAMP
column automatically is
initialized and updated to the current timestamp. The rules
are as follows:
If a DEFAULT
value is specified for the
first TIMESTAMP
column in a table, it is
not ignored. The default can be
CURRENT_TIMESTAMP
or a constant date and
time value.
DEFAULT NULL
is the same as
DEFAULT CURRENT_TIMESTAMP
for the
first TIMESTAMP
column. For any other TIMESTAMP
column,
DEFAULT NULL
is treated as DEFAULT
0
.
Any single TIMESTAMP
column in a table can
be used as the one that is initialized to the current
timestamp or updated automatically.
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. If the column has an
ON UPDATE CURRENT_TIMESTAMP
clause, it
is automatically updated, otherwise not.
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 get auto-update without also having the
column auto-initialized.)
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);
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. These statements are equivalent:
CREATE TABLE t ( ts1 TIMESTAMP DEFAULT 0, ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); CREATE TABLE t ( ts1 TIMESTAMP DEFAULT 0, ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Beginning with MySQL 4.1.3, you can set the current time zone
on a per-connection basis, as described in
Section聽5.10.8, 鈥淢ySQL 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
than 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.
Beginning with MySQL 4.1.6, you can include the
NULL
attribute in the definition of a
TIMESTAMP
column to allow the column to
contain NULL
values. For example:
CREATE TABLE t ( ts1 TIMESTAMP NULL DEFAULT NULL, ts2 TIMESTAMP NULL DEFAULT 0, ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP );
Before MySQL 4.1.6 (and even as of 4.1.6 if the
NULL
attribute is not specified), setting
the column to NULL
sets it to the current
timestamp. Note that a TIMESTAMP
column
which 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 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');
鈥hen 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);
Note that, in MySQL 4.1 and later,
TIMESTAMP
columns are NOT
NULL
by default.
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聽11.3.1, 鈥淭he 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.2.6, 鈥淪QL Modes鈥.
The YEAR
type is a one-byte type used for
representing years.
MySQL retrieves and displays YEAR
values in
YYYY
format. The range is
1901
to 2155
.
You can specify YEAR
values in a variety of
formats:
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聽11.5, 鈥淒ata Type Storage Requirements鈥.
The CHAR
and VARCHAR
types
are similar, but differ in the way they are stored and
retrieved. No lettercase conversion takes place during storage
or retrieval.
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 using only as many
characters as are needed, plus one byte to record the length
(two bytes for columns that are declared with a length longer
than 255).
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 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.
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:
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.
Note that 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)
Note that 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聽13.1.5.1, 鈥淪ilent 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.
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聽11.5, 鈥淒ata Type Storage Requirements鈥. No lettercase conversion
for TEXT
or BLOB
columns
takes place during storage or retrieval.
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 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.)
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. If the truncated
characters are not spaces, a warning is generated. See
Section聽5.2.6, 鈥淪QL Modes鈥.
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.
Note that TEXT
is on comparison 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, 鈥淐olumn 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.2.3, 鈥淪ystem Variables鈥.
N
As of MySQL 4.0.3, you can make more bytes significant in
sorting or grouping by increasing the value of
max_sort_length
at runtime. Any client
can change the value of its session
max_sort_length
variable:
mysql>SET max_sort_length = 2000;
mysql>SELECT id, comment FROM t
->ORDER BY comment;
Another way to use GROUP BY
or
ORDER BY
on a BLOB
or
TEXT
column containing long values when
you want more than max_sort_length
bytes
to be significant is to convert the column value into a
fixed-length object. The standard way to do this is with the
SUBSTRING()
function. For example, the
following statement causes 2000 bytes of the
comment
column to be taken into account
for sorting:
mysql>SELECT id, SUBSTRING(comment,1,2000) FROM t
->ORDER BY SUBSTRING(comment,1,2000);
Before MySQL 3.23.2, you can group on an expression
involving BLOB
or TEXT
values by using a column alias or by specifying the column
position:
mysql>SELECT id, SUBSTRING(comment,1,2000) AS b
->FROM
mysql>tbl_name
GROUP BY b;SELECT id, SUBSTRING(comment,1,2000)
->FROM
tbl_name
GROUP BY 2;
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, 鈥淭uning Server Parameters鈥,
Section聽8.7, 鈥mysql 鈥 The MySQL Command-Line Tool鈥, and Section聽8.12, 鈥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聽11.5, 鈥淒ata 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聽12.4, 鈥淪tring 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. This means that you also may not employ a user variable as an enumeration value.
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.
If you want to determine all possible values for an
ENUM
column, use SHOW COLUMNS FROM
and parse the
tbl_name
LIKE
enum_col
ENUM
definition in the
Type
column of the output.
A SET
is a string object that can have zero
or more values, each of which must be chosen from a list of
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.
Normally, you search for SET
values using the
FIND_IN_SET()
function or the
LIKE
operator:
mysql>SELECT * FROM
mysql>tbl_name
WHERE FIND_IN_SET('value
',set_col
)>0;SELECT * FROM
tbl_name
WHEREset_col
LIKE '%value
%';
The first statement finds rows where
set_col
contains the
value
set member. The second is
similar, but not the same: It finds rows where
set_col
contains
value
anywhere, even as a substring
of another set member.
The following statements also are legal:
mysql>SELECT * FROM
mysql>tbl_name
WHEREset_col
& 1;SELECT * FROM
tbl_name
WHEREset_col
= 'val1
,val2
';
The first of these statements looks for values containing the
first set member. The second looks for an exact match. Be
careful with comparisons of the second type. Comparing set
values to
'
returns different results than comparing values to
val1
,val2
''
.
You should specify the values in the same order in which they
are listed in the column definition.
val2
,val1
'
If you want to determine all possible values for a
SET
column, use SHOW COLUMNS FROM
and parse the
tbl_name
LIKE
set_col
SET
definition in the Type
column of the output.
The storage requirements for each of the data types supported by MySQL are listed here by category.
The maximum size of a row in a MyISAM
table is
65,534 bytes. Each BLOB
and
TEXT
column accounts for only five to nine
bytes toward this size.
Important: 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 鈥 in a table using a
storage engine other than NDB
鈥 would
take 15 bytes for storage, 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.
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 also find the ndb_size.pl
utility to
be useful in such cases. 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.9.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
bytes, if
M
+1D
= 0
D
+2, if
M
<
D
Storage Requirements for Date and Time Types
Data Type | Storage Required |
DATE | 3 bytes |
DATETIME | 8 bytes |
TIMESTAMP | 4 bytes |
TIME | 3 bytes |
YEAR | 1 byte |
Storage Requirements for String Types
Data Type | Storage Required |
CHAR( | bytes, 0 <=
255 |
VARCHAR( | L +1 bytes, where
and 0
<= 255 |
BINARY( | bytes, 0 <=
255 |
VARBINARY( | L +1 bytes, where
and 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) |
For the CHAR
, VARCHAR
, and
TEXT
types, L
and
M
in the preceding table should be
interpreted as number of bytes before MySQL 4.1 and as number of
characters thereafter. Lengths for these types in columns
specifications indicate number of characters from MySQL 4.1 on.
The number of extra bytes for recording lengths for
variable-length data types is unchanged. For example,
L
+1 bytes to store a
TINYTEXT
value before MySQL 4.1 becomes
L
characters + 1 byte to store the
length as of MySQL 4.1.
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. 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.
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. 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. For a breakdown of the storage used for
different categories of utf8
characters, see
Section聽10.7, 鈥淯nicode Support鈥.
Note: The NDB
Cluster
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.
The BLOB
and TEXT
types
require 1, 2, 3, or 4 bytes to record the length of the column
value, depending on the maximum possible length of the type. See
Section聽11.4.3, 鈥淭he BLOB
and TEXT
Types鈥.
TEXT
and BLOB
columns are
implemented differently in the NDB Cluster
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聽11.4.4, 鈥淭he 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聽11.4.5, 鈥淭he 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. Calculations on these DECIMAL
values are however 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.
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)