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 also supports extensions for handing spatial data. Section 11.12, “Spatial Extensions”, provides information about these data types.
Data type descriptions use these conventions:
M indicates the maximum display width
for integer types. For floating-point and fixed-point types,
M is the total number of digits that
can be stored. For string types, M is
the maximum length. The maximum allowable value of
M depends on the data type.
D applies to floating-point and
fixed-point types and indicates the number of digits following
the decimal point. The maximum possible value is 30, but should
be no greater than M–2.
Square brackets (“[” and
“]”) indicate optional parts of
type definitions.
A summary of the numeric data types follows. For additional information about properties of the numeric types, see Section 10.2, “Numeric Types”. Storage requirements are given in Section 10.5, “Data Type Storage Requirements”.
M indicates the maximum display width
for integer types. The maximum legal display width is 255.
Display width is unrelated to the range of values a type can
contain, as described in Section 10.2, “Numeric Types”. For
floating-point and fixed-point types,
M is the total number of digits that
can be stored.
If you specify ZEROFILL for a numeric column,
MySQL automatically adds the UNSIGNED
attribute to the column.
Numeric data types that allow the UNSIGNED
attribute also allow SIGNED. However, these
data types are signed by default, so the
SIGNED attribute has no effect.
SERIAL is an alias for BIGINT
UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
SERIAL DEFAULT VALUE in the definition of an
integer column is an alias for NOT NULL AUTO_INCREMENT
UNIQUE.
When you use subtraction between integer values where one is
of type UNSIGNED, the result is unsigned
unless the
NO_UNSIGNED_SUBTRACTION SQL
mode is enabled. See Section 11.9, “Cast Functions and Operators”.
A bit-field type. M indicates the
number of bits per value, from 1 to 64. The default is 1 if
M is omitted.
This data type was added in MySQL 5.0.3 for
MyISAM, and extended in 5.0.5 to
MEMORY, InnoDB,
BDB, and
NDBCLUSTER. Before 5.0.3,
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).
A value of zero is considered false. Nonzero values are
considered true:
mysql>SELECT IF(0, 'true', 'false');+------------------------+ | IF(0, 'true', 'false') | +------------------------+ | false | +------------------------+ mysql>SELECT IF(1, 'true', 'false');+------------------------+ | IF(1, 'true', 'false') | +------------------------+ | true | +------------------------+ mysql>SELECT IF(2, 'true', 'false');+------------------------+ | IF(2, 'true', 'false') | +------------------------+ | true | +------------------------+
However, the values TRUE and
FALSE are merely aliases for
1 and 0, respectively,
as shown here:
mysql>SELECT IF(0 = FALSE, 'true', 'false');+--------------------------------+ | IF(0 = FALSE, 'true', 'false') | +--------------------------------+ | true | +--------------------------------+ mysql>SELECT IF(1 = TRUE, 'true', 'false');+-------------------------------+ | IF(1 = TRUE, 'true', 'false') | +-------------------------------+ | true | +-------------------------------+ mysql>SELECT IF(2 = TRUE, 'true', 'false');+-------------------------------+ | IF(2 = TRUE, 'true', 'false') | +-------------------------------+ | false | +-------------------------------+ mysql>SELECT IF(2 = FALSE, 'true', 'false');+--------------------------------+ | IF(2 = FALSE, 'true', 'false') | +--------------------------------+ | false | +--------------------------------+
The last two statements display the results shown because
2 is equal to neither
1 nor 0.
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.
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 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 B.1.5.7, “Solving Problems with No Matching Rows”.
DOUBLE[(
M,D)]
[UNSIGNED] [ZEROFILL]
A normal-size (double-precision) floating-point number.
Allowable values are
-1.7976931348623157E+308 to
-2.2250738585072014E-308,
0, and
2.2250738585072014E-308 to
1.7976931348623157E+308. These are the
theoretical limits, based on the IEEE standard. The actual
range might be slightly smaller depending on your hardware
or operating system.
M is the total number of digits
and D is the number of digits
following the decimal point. If M
and D are omitted, values are
stored to the limits allowed by the hardware. A
double-precision floating-point number is accurate to
approximately 15 decimal places.
UNSIGNED, if specified, disallows
negative values.
DOUBLE
PRECISION[(,
M,D)]
[UNSIGNED] [ZEROFILL]REAL[(
M,D)]
[UNSIGNED] [ZEROFILL]
These types are synonyms for
DOUBLE. Exception: If the
REAL_AS_FLOAT SQL mode is
enabled, REAL is a synonym
for FLOAT rather than
DOUBLE.
FLOAT(
p) [UNSIGNED]
[ZEROFILL]
A floating-point number. p
represents the precision in bits, but MySQL uses this value
only to determine whether to use
FLOAT or
DOUBLE for the resulting data
type. If p is from 0 to 24, the
data type becomes FLOAT with
no M or
D values. If
p is from 25 to 53, the data type
becomes DOUBLE with no
M or D
values. The range of the resulting column is the same as for
the single-precision FLOAT or
double-precision DOUBLE data
types described earlier in this section.
DECIMAL[(
M[,D])]
[UNSIGNED] [ZEROFILL]
For MySQL 5.0.3 and above:
A packed “exact” fixed-point number.
M is the total number of digits
(the precision) and D is the
number of digits after the decimal point (the scale). The
decimal point and (for negative numbers) the
“-” sign are not counted in
M. If
D is 0, values have no decimal
point or fractional part. The maximum number of digits
(M) for
DECIMAL is 65 (64 from 5.0.3
to 5.0.5). The maximum number of supported decimals
(D) is 30. If
D is omitted, the default is 0.
If M is omitted, the default is
10.
UNSIGNED, if specified, disallows
negative values.
All basic calculations (+, -, *, /) with
DECIMAL columns are done with
a precision of 65 digits.
Before MySQL 5.0.3:
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.
The behavior used by the server for
DECIMAL columns in a table
depends on the version of MySQL used to create the table. If
your server is from MySQL 5.0.3 or higher, but you have
DECIMAL columns in tables
that were created before 5.0.3, the old behavior still
applies to those columns. To convert the tables to the newer
DECIMAL format, dump them
with mysqldump and reload them.
DEC[(,
M[,D])]
[UNSIGNED] [ZEROFILL]NUMERIC[(,
M[,D])]
[UNSIGNED] [ZEROFILL]FIXED[(
M[,D])]
[UNSIGNED] [ZEROFILL]
These types are synonyms for
DECIMAL. The
FIXED synonym is available
for compatibility with other database systems.
A summary of the temporal data types follows. For additional information about properties of the temporal types, see Section 10.3, “Date and Time Types”. Storage requirements are given in Section 10.5, “Data Type Storage Requirements”. Functions that operate on temporal values are described at Section 11.6, “Date and Time Functions”.
For the DATETIME and
DATE range descriptions,
“supported” means that although earlier values
might work, there is no guarantee.
A date. The supported range is
'1000-01-01' to
'9999-12-31'. MySQL displays
DATE values in
'YYYY-MM-DD' format, but allows
assignment of values to DATE
columns using either strings or numbers.
A date and time combination. The supported range is
'1000-01-01 00:00:00' to
'9999-12-31 23:59:59'. MySQL displays
DATETIME values in
'YYYY-MM-DD HH:MM:SS' format, but allows
assignment of values to
DATETIME columns using either
strings or numbers.
A timestamp. The range is '1970-01-01
00:00:01' UTC to '2038-01-09
03:14:07' UTC.
TIMESTAMP values are stored
as the number of seconds since the epoch
('1970-01-01 00:00:00' UTC). A
TIMESTAMP cannot represent
the value '1970-01-01 00:00:00' because
that is equivalent to 0 seconds from the epoch and the value
0 is reserved for representing '0000-00-00
00:00:00', the “zero”
TIMESTAMP value.
A TIMESTAMP column is useful
for recording the date and time of an
INSERT or
UPDATE operation. By default,
the first TIMESTAMP column in
a table is automatically set to the date and time of the
most recent operation if you do not assign it a value
yourself. You can also set any
TIMESTAMP column to the
current date and time by assigning it a
NULL value. Variations on automatic
initialization and update properties are described in
Section 10.3.1.1, “TIMESTAMP Properties”.
A TIMESTAMP value is returned
as a string in the format 'YYYY-MM-DD
HH:MM:SS' with a display width fixed at 19
characters. To obtain the value as a number, you should add
+0 to the timestamp column.
The TIMESTAMP format that
was used prior to MySQL 4.1 is not supported in MySQL
5.0; see MySQL 3.23, 4.0, 4.1
Reference Manual for information regarding the
old format.
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 SUM() and
AVG() aggregate functions do not
work with temporal values. (They convert the values to numbers,
which loses the part after the first nonnumeric character.) To
work around this problem, 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 about properties of the string types, see Section 10.4, “String Types”. Storage requirements are given in Section 10.5, “Data Type Storage Requirements”.
In some cases, MySQL may change a string column to a type
different from that given in a CREATE
TABLE or ALTER TABLE
statement. See Section 12.1.10.1, “Silent Column Specification Changes”.
In MySQL 4.1 and up, string data types include some features that you may not have encountered in working with versions of MySQL prior to 4.1:
MySQL interprets length specifications in character column
definitions in character units. (Before MySQL 4.1, column
lengths were interpreted in bytes.) This applies to
CHAR,
VARCHAR, and the
TEXT types.
Column definitions for many string data types can include
attributes that specify the character set or collation of
the column. These attributes apply to the
CHAR,
VARCHAR, the
TEXT types,
ENUM, and
SET data types:
The CHARACTER SET attribute specifies
the character set, and the COLLATE
attribute specifies a collation for the character set.
For example:
CREATE TABLE t
(
c1 VARCHAR(20) CHARACTER SET utf8,
c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);
This table definition creates a column named
c1 that has a character set of
utf8 with the default collation for
that character set, and a column named
c2 that has a character set of
latin1 and a case-sensitive
collation.
The rules for assigning the character set and collation
when either or both of the CHARACTER
SET and COLLATE attributes
are missing are described in
Section 9.1.3.4, “Column Character Set and Collation”.
CHARSET is a synonym for
CHARACTER SET.
Specifying the CHARACTER SET binary
attribute for a character data type causes the column to
be created as the corresponding binary data type:
CHAR becomes
BINARY,
VARCHAR becomes
VARBINARY, and
TEXT becomes
BLOB. For the
ENUM and
SET data types, this does
not occur; they are created as declared. Suppose that
you specify a table using this definition:
CREATE TABLE t
(
c1 VARCHAR(10) CHARACTER SET binary,
c2 TEXT CHARACTER SET binary,
c3 ENUM('a','b','c') CHARACTER SET binary
);
The resulting table has this definition:
CREATE TABLE t
(
c1 VARBINARY(10),
c2 BLOB,
c3 ENUM('a','b','c') CHARACTER SET binary
);
The ASCII attribute is shorthand for
CHARACTER SET latin1.
The UNICODE attribute is shorthand
for CHARACTER SET ucs2.
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,
BINARY caused a column to store
binary strings and sorting and comparison were based on
numeric byte values. This is the same as using character
values for single-byte character sets, but not for
multi-byte character sets.)
Character column sorting and comparison are based on the
character set assigned to the column. (Before MySQL 4.1,
sorting and comparison were based on the collation of the
server character set.) For the
CHAR,
VARCHAR,
TEXT,
ENUM, and
SET data types, you can
declare a column with a binary collation or the
BINARY attribute to cause sorting and
comparison to use the underlying character code values
rather than a lexical ordering.
Section 9.1, “Character Set Support”, provides additional information about use of character sets in MySQL.
[NATIONAL] CHAR[(
M)]
[CHARACTER SET charset_name]
[COLLATE
collation_name]
A fixed-length string that is always right-padded with
spaces to the specified length when stored.
M represents the column length in
characters. The range of M is 0
to 255. If M is omitted, the
length is 1.
Trailing spaces are removed when
CHAR values are retrieved.
Before MySQL 5.0.3, 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. However, this
conversion causes the column to become a variable-length
column, and also affects trailing-space removal.
In MySQL 5.0.3 and later, a
CHAR length greater than 255
is illegal and fails with an error:
mysql> CREATE TABLE c1 (col1 INT, col2 CHAR(500));
ERROR 1074 (42000): Column length too big for column 'col' (max = 255);
use BLOB or TEXT instead
CHAR is shorthand for
CHARACTER.
NATIONAL CHAR (or its
equivalent short form, NCHAR)
is the standard SQL way to define that a
CHAR column should use some
predefined character set. MySQL 4.1 and up uses
utf8 as this predefined character set.
Section 9.1.3.6, “National Character Set”.
The CHAR BYTE data type is an
alias for the BINARY data
type. This is a compatibility feature.
MySQL allows you to create a column of type
CHAR(0). This is useful primarily when
you have to be compliant with old applications that depend
on the existence of a column but that do not actually use
its value. CHAR(0) is also quite nice
when you need a column that can take only two values: A
column that is defined as CHAR(0) NULL
occupies only one bit and can take only the values
NULL and '' (the empty
string).
[NATIONAL] VARCHAR(
M)
[CHARACTER SET charset_name]
[COLLATE
collation_name]
A variable-length string. M
represents the maximum column length in characters. In MySQL
5.0, the range of M
is 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in MySQL
5.0.3 and later. The effective maximum length of a
VARCHAR in MySQL 5.0.3 and
later is subject to the maximum row size (65,535 bytes,
which is shared among all columns) and the character set
used. For example, utf8 characters can
require up to three bytes per character, so a
VARCHAR column that uses the
utf8 character set can be declared to be
a maximum of 21,844 characters.
MySQL stores VARCHAR values
as a one-byte or two-byte length prefix plus data. The
length prefix indicates the number of bytes in the value. A
VARCHAR column uses one
length byte if values require no more than 255 bytes, two
length bytes if values may require more than 255 bytes.
Before 5.0.3, trailing spaces were removed when
VARCHAR values were stored,
which differs from the standard SQL specification.
Prior to MySQL 5.0.3, 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. However, this
conversion affects trailing-space removal.
VARCHAR is shorthand for
CHARACTER VARYING.
NATIONAL VARCHAR is the
standard SQL way to define that a
VARCHAR column should use
some predefined character set. MySQL 4.1 and up uses
utf8 as this predefined character set.
Section 9.1.3.6, “National Character Set”.
NVARCHAR is shorthand for
NATIONAL VARCHAR.
The BINARY type is similar to
the CHAR type, but stores
binary byte strings rather than nonbinary character strings.
M represents the column length in
bytes.
The VARBINARY type is similar
to the VARCHAR type, but
stores binary byte strings rather than nonbinary character
strings. M represents the maximum
column length in bytes.
A BLOB column with a maximum
length of 255 (28 – 1)
bytes. Each TINYBLOB value is
stored using a one-byte length prefix that indicates the
number of bytes in the value.
TINYTEXT [CHARACTER SET
charset_name] [COLLATE
collation_name]
A TEXT column with a maximum
length of 255 (28 – 1)
characters. The effective maximum length is less if the
value contains multi-byte characters. Each
TINYTEXT value is stored
using a one-byte length prefix that indicates the number of
bytes in the value.
A BLOB column with a maximum
length of 65,535 (216 – 1)
bytes. Each BLOB value is
stored using a two-byte length prefix that indicates the
number of bytes in the value.
An optional length M can be given
for this type. If this is done, MySQL creates the column as
the smallest BLOB type large
enough to hold values M bytes
long.
TEXT[(
M)] [CHARACTER SET
charset_name] [COLLATE
collation_name]
A TEXT column with a maximum
length of 65,535 (216 – 1)
characters. The effective maximum length is less if the
value contains multi-byte characters. Each
TEXT value is stored using a
two-byte length prefix that indicates the number of bytes in
the value.
An optional length M can be given
for this type. If this is done, MySQL creates the column as
the smallest TEXT type large
enough to hold values M
characters long.
A BLOB column with a maximum
length of 16,777,215 (224 –
1) bytes. Each MEDIUMBLOB
value is stored using a three-byte length prefix that
indicates the number of bytes in the value.
MEDIUMTEXT [CHARACTER SET
charset_name] [COLLATE
collation_name]
A TEXT column with a maximum
length of 16,777,215 (224 –
1) characters. The effective maximum length is less if the
value contains multi-byte characters. Each
MEDIUMTEXT value is stored
using a three-byte length prefix that indicates the number
of bytes in the value.
A BLOB column with a maximum
length of 4,294,967,295 or 4GB
(232 – 1) bytes. The
effective maximum length of
LONGBLOB columns depends on
the configured maximum packet size in the client/server
protocol and available memory. Each
LONGBLOB value is stored
using a four-byte length prefix that indicates the number of
bytes in the value.
LONGTEXT [CHARACTER SET
charset_name] [COLLATE
collation_name]
A TEXT column with a maximum
length of 4,294,967,295 or 4GB
(232 – 1) characters. The
effective maximum length is less if the value contains
multi-byte characters. The effective maximum length of
LONGTEXT columns also depends
on the configured maximum packet size in the client/server
protocol and available memory. Each
LONGTEXT value is stored
using a four-byte length prefix that indicates the number of
bytes in the value.
ENUM('
value1','value2',...)
[CHARACTER SET charset_name]
[COLLATE
collation_name]
An enumeration. A string object that can have only one
value, chosen from the list of values
',
value1'',
value2'..., NULL or the
special '' error value. An
ENUM column can have a
maximum of 65,535 distinct values.
ENUM values are represented
internally as integers.
SET('
value1','value2',...)
[CHARACTER SET charset_name]
[COLLATE
collation_name]
A set. A string object that can have zero or more values,
each of which must be chosen from the list of values
',
value1'',
value2'... A SET
column can have a maximum of 64 members.
SET values are represented
internally as integers.
The DEFAULT
clause in a data type specification indicates a default value
for a column. With one exception, the default value must be a
constant; it cannot be a function or an expression. This means,
for example, that you cannot set the default for a date column
to be the value of a function such as
valueNOW() or
CURRENT_DATE. The exception is
that you can specify
CURRENT_TIMESTAMP as the default
for a TIMESTAMP column. See
Section 10.3.1.1, “TIMESTAMP Properties”.
Prior to MySQL 5.0.2, if a column definition includes no
explicit DEFAULT value, MySQL determines the
default value as follows:
If the column can take NULL as a value, the
column is defined with an explicit DEFAULT
NULL clause.
If the column cannot take NULL as the value,
MySQL defines the column with an explicit
DEFAULT clause, using the implicit default
value for the column data type. Implicit defaults are defined as
follows:
For numeric types, the default is 0, with
the exception that for integer or floating-point types
declared with the AUTO_INCREMENT
attribute, the default is the next value in the sequence.
For date and time types other than
TIMESTAMP, the default is the
appropriate “zero” value for the type. For the
first TIMESTAMP column in a
table, the default value is the current date and time. See
Section 10.3, “Date and Time Types”.
For string types other than
ENUM, the default value is
the empty string. For ENUM,
the default is the first enumeration value.
BLOB and
TEXT columns cannot be assigned a
default value.
As of MySQL 5.0.2, 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. This is the same as before 5.0.2.
If the column cannot take NULL as the value,
MySQL defines the column with no explicit
DEFAULT clause. For data entry, if an
INSERT or
REPLACE statement includes no
value for the column, or an
UPDATE statement sets the column
to NULL, MySQL handles the column according
to the SQL mode in effect at the time:
If strict SQL mode is not enabled, MySQL sets the column to the implicit default value for the column data type.
If strict mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.
Suppose that a table t is defined as follows:
CREATE TABLE t (i INT NOT NULL);
In this case, i has no explicit default, so
in strict mode each of the following statements produce an error
and no row is inserted. When not using strict mode, only the
third statement produces an error; the implicit default is
inserted for the first two statements, but the third fails
because DEFAULT(i) cannot produce
a value:
INSERT INTO t VALUES(); INSERT INTO t VALUES(DEFAULT); INSERT INTO t VALUES(DEFAULT(i));
See Section 5.1.7, “Server SQL Modes”.
For a given table, you can use the SHOW
CREATE TABLE statement to see which columns have an
explicit DEFAULT clause.
SERIAL DEFAULT VALUE in the definition of an
integer column is an alias for NOT NULL AUTO_INCREMENT
UNIQUE.
MySQL supports all of the standard SQL numeric data types. These
types include the exact numeric data types
(INTEGER,
SMALLINT,
DECIMAL, and
NUMERIC), as well as the
approximate numeric data types
(FLOAT,
REAL, and
DOUBLE PRECISION). The keyword
INT is a synonym for
INTEGER, and the keyword
DEC is a synonym for
DECIMAL. For numeric type storage
requirements, see Section 10.5, “Data Type Storage Requirements”.
The numeric types used for the results of calculations depends on the operations being performed and the numeric types of the operands; for more information, see Section 11.5.1, “Arithmetic Operators”.
As of MySQL 5.0.3, a BIT data type
is available for storing bit-field values. (Before 5.0.3, MySQL
interprets BIT as
TINYINT(1).) In MySQL 5.0.3,
BIT is supported only for
MyISAM. MySQL 5.0.5 extends
BIT support to
MEMORY, InnoDB,
BDB, and
NDBCLUSTER.
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 may be
used by applications to display integer values having a width less
than the width specified for the column by left-padding them with
spaces. (That is, this width is present in the metadata returned
with result sets. Whether it is used or not is up to the
application.)
The display width does not constrain the
range of values that can be stored in the column, nor the number
of digits that are displayed for values having a width exceeding
that specified for the column. For example, a column specified as
SMALLINT(3) has the usual
SMALLINT range of
-32768 to 32767, and values
outside the range allowed by three characters are displayed using
more than three characters.
When used in conjunction with the optional extension attribute
ZEROFILL, the default padding of spaces is
replaced with zeros. For example, for a column declared as
INT(5) ZEROFILL, a value of
4 is retrieved as 00004.
Note that if you store larger values than the display width in an
integer column, you may experience problems when MySQL generates
temporary tables for some complicated joins, because in these
cases MySQL assumes that the data fits into the original column
width.
The ZEROFILL attribute is ignored when a
column is involved in expressions or
UNION queries.
All integer types can have an optional (nonstandard) attribute
UNSIGNED. Unsigned values can be used when you
want to allow only nonnegative 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.
Floating-point and fixed-point types also can be
UNSIGNED. As with integer types, this attribute
prevents negative values from being stored in the column. However,
unlike the integer types, the upper range of column values remains
the same.
If you specify ZEROFILL for a numeric column,
MySQL automatically adds the UNSIGNED attribute
to the column.
Integer or floating-point data types can have the additional
attribute AUTO_INCREMENT. When you insert a
value of NULL (recommended) or
0 into an indexed
AUTO_INCREMENT column, the column is set to the
next sequence value. Typically this is
value+1value is the largest value for the
column currently in the table. AUTO_INCREMENT
sequences begin with 1.
For floating-point data types, MySQL uses four bytes for single-precision values and eight bytes for double-precision values.
The FLOAT and
DOUBLE data types are used to
represent approximate numeric data values. For
FLOAT the SQL standard allows an
optional specification of the precision (but not the range of the
exponent) in bits following the keyword
FLOAT in parentheses. MySQL also
supports this optional precision specification, but the precision
value is used only to determine storage size. A precision from 0
to 23 results in a four-byte single-precision
FLOAT column. A precision from 24
to 53 results in an eight-byte double-precision
DOUBLE column.
MySQL allows a nonstandard syntax:
FLOAT(
or
M,D)REAL(
or M,D)DOUBLE
PRECISION(.
Here,
“M,D)(”
means than values can be stored with up to
M,D)M digits in total, of which
D digits may be after the decimal
point. For example, a column defined as
FLOAT(7,4) will look like
-999.9999 when displayed. MySQL performs
rounding when storing values, so if you insert
999.00009 into a FLOAT(7,4)
column, the approximate result is 999.0001.
MySQL treats DOUBLE as a synonym
for DOUBLE PRECISION (a nonstandard
extension). MySQL also treats REAL
as a synonym for DOUBLE PRECISION
(a nonstandard variation), unless the
REAL_AS_FLOAT SQL mode is
enabled.
For 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.
As of MySQL 5.0.3, DECIMAL and
NUMERIC values are stored in binary
format. Previously, they were stored as strings, with one
character used for each digit of the value, the decimal point (if
the scale is greater than 0), and the
“-” sign (for negative numbers).
See Section 11.13, “Precision Math”.
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. MySQL
enforces this limit as of MySQL 5.0.3. Before 5.0.3, on the
positive end of the range, the column could actually store numbers
up to 9999.99. (For positive numbers, MySQL
5.0.2 and earlier used the byte reserved for the sign to extend
the upper end of the range.)
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. MySQL supports both of these variant
forms of the DECIMAL and
NUMERIC syntax. The default value
of M is 10.
The maximum number of digits for
DECIMAL or
NUMERIC is 65 (64 from MySQL 5.0.3
to 5.0.5). Before MySQL 5.0.3, 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.)
As of MySQL 5.0.3, the BIT data
type is used to store bit-field values. A type of
BIT( allows for
storage of M)M-bit values.
M can range from 1 to 64.
To specify bit values,
b' notation
can be used. value'value is a binary value
written using zeros and ones. For example,
b'111' and b'10000000'
represent 7 and 128, respectively. See
Section 8.1.5, “Bit-Field Values”.
If you assign a value to a
BIT( column that
is less than M)M bits long, the value is
padded on the left with zeros. For example, assigning a value of
b'101' to a BIT(6) column
is, in effect, the same as assigning b'000101'.
When asked to store a value in a numeric column that is outside the data type's allowable range, MySQL's behavior depends on the SQL mode in effect at the time. For example, if no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead. However, if strict SQL mode is enabled, MySQL rejects a value that is out of range with an error, and the insert fails, in accordance with the SQL standard.
In nonstrict mode, 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.
Subtraction between integer values, where one is of type
UNSIGNED, produces an unsigned result by
default. If the result would otherwise have been negative, it
becomes the maximum integer value. If the
NO_UNSIGNED_SUBTRACTION SQL mode
is enabled, the result is negative.
mysql>SET SQL_MODE = '';mysql>SELECT CAST(0 AS UNSIGNED) - 1;+-------------------------+ | CAST(0 AS UNSIGNED) - 1 | +-------------------------+ | 18446744073709551615 | +-------------------------+ mysql>SET SQL_MODE = 'NO_UNSIGNED_SUBTRACTION';mysql>SELECT CAST(0 AS UNSIGNED) - 1;+-------------------------+ | CAST(0 AS UNSIGNED) - 1 | +-------------------------+ | -1 | +-------------------------+
If the result of such an operation is used to update an
UNSIGNED integer column, the result is clipped
to the maximum value for the column type, or clipped to 0 if
NO_UNSIGNED_SUBTRACTION is
enabled. If strict SQL mode is enabled, an error occurs and the
column remains unchanged.
Conversions that occur due to clipping when MySQL is not operating
in strict mode are reported as warnings for
ALTER TABLE,
LOAD DATA
INFILE, UPDATE, and
multiple-row INSERT statements.
When MySQL is operating in strict mode, these statements fail, and
some or all of the values will not be inserted or changed,
depending on whether the table is a transactional table and other
factors. For details, see Section 5.1.7, “Server SQL Modes”.
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
may be used when you specify an illegal value that MySQL cannot
represent. The TIMESTAMP type has
special automatic updating behavior, described later on. For
temporal type storage requirements, see
Section 10.5, “Data Type Storage Requirements”.
Starting from MySQL 5.0.2, MySQL gives warnings or errors if you
try to insert an illegal date. By setting the SQL mode to the
appropriate value, you can specify more exactly what kind of dates
you want MySQL to support. (See
Section 5.1.7, “Server SQL Modes”.) You can get MySQL to accept
certain dates, such as '2009-11-31', by using
the ALLOW_INVALID_DATES SQL
mode. (Before 5.0.2, this mode was the default behavior for
MySQL.) This is useful when you want to store a “possibly
wrong” value which the user has specified (for example, in
a web form) in the database for future processing. Under this
mode, 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 '2009-00-00' or
'2009-01-00'. If you store dates such as these,
you should not expect to get correct results for functions such as
DATE_SUB() or
DATE_ADD() that require complete
dates. (If you do not want to allow zero in
dates, you can use the
NO_ZERO_IN_DATE SQL mode).
Prior to MySQL 5.0.42, when DATE
values are compared with DATETIME
values, the time portion of the
DATETIME value is ignored, or the
comparison could be performed as a string compare. Starting from
MySQL 5.0.42, a DATE value is
coerced to the DATETIME type by
adding the time portion as '00:00:00'. To mimic
the old behavior, use the CAST()
function to cause the comparison operands to be treated as
previously. For example:
date_col = CAST(NOW() AS DATE)
MySQL also allows you to store '0000-00-00' as
a “dummy date” (if you are not using the
NO_ZERO_DATE SQL mode). This is
in some cases more convenient (and uses less data and index space)
than using 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 70-99 are
converted to 1970-1999.
Year values in the range 00-69 are
converted to 2000-2069.
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.
The following table shows the format of the
“zero” value for each type. Note that the use of
these values produces warnings if the
NO_ZERO_DATE SQL mode is
enabled.
The “zero” values are special, but you can store
or refer to them explicitly using the values shown in the
table. You can also do this using the values
'0' or 0, which are
easier to write.
“Zero” date or time values used through MyODBC
are converted automatically to NULL in
MyODBC 2.50.12 and above, because ODBC cannot handle such
values.
The DATETIME,
DATE, and
TIMESTAMP types are related. This
section describes their characteristics, how they are similar,
and how they differ.
The DATETIME type is used when
you need values that contain both date and time information.
MySQL retrieves and displays
DATETIME values in
'YYYY-MM-DD HH:MM:SS' format. The supported
range is '1000-01-01 00:00:00' to
'9999-12-31 23:59:59'.
The DATE type is used when you
need only a date value, without a time part. MySQL retrieves and
displays DATE values in
'YYYY-MM-DD' format. The supported range is
'1000-01-01' to
'9999-12-31'.
For the DATETIME and
DATE range descriptions,
“supported” means that although earlier values
might work, there is no guarantee.
The TIMESTAMP data type has a
range of '1970-01-01 00:00:01' UTC to
'2038-01-09 03:14:07' UTC. It has varying
properties, depending on the MySQL version and the SQL mode the
server is running in. 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,
'20070523091528' and
'070523091528' are interpreted as
'2007-05-23 09:15:28', but
'071122129015' 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, '20070523' and
'070523' are interpreted as
'2007-05-23', but
'071332' is illegal (it has nonsensical
month and day parts) and becomes
'0000-00-00'.
As a number in either YYYYMMDDHHMMSS or
YYMMDDHHMMSS format, provided that the
number makes sense as a date. For example,
19830905132800 and
830905132800 are interpreted as
'1983-09-05 13:28:00'.
As a number in either YYYYMMDD or
YYMMDD format, provided that the number
makes sense as a date. For example,
19830905 and 830905
are interpreted as '1983-09-05'.
As the result of a function that returns a value that is
acceptable in a DATETIME,
DATE, or
TIMESTAMP context, such as
NOW() or
CURRENT_DATE.
A microseconds part is allowable in temporal values in some
contexts, such as in literal values, and in the arguments to or
return values from some temporal functions. Microseconds are
specified as a trailing .uuuuuu part in the
value. Example:
mysql> SELECT MICROSECOND('2010-12-10 14:12:09.019473');
+-------------------------------------------+
| MICROSECOND('2010-12-10 14:12:09.019473') |
+-------------------------------------------+
| 19473 |
+-------------------------------------------+
However, microseconds cannot be stored into a column of any temporal data type. Any microseconds part is discarded.
As of MySQL 5.0.8, conversion of
TIME or
DATETIME values to numeric form
(for example, by adding +0) results in a
double value with a microseconds part of
.000000:
mysql>SELECT CURTIME(), CURTIME()+0;+-----------+---------------+ | CURTIME() | CURTIME()+0 | +-----------+---------------+ | 10:41:36 | 104136.000000 | +-----------+---------------+ mysql>SELECT NOW(), NOW()+0;+---------------------+-----------------------+ | NOW() | NOW()+0 | +---------------------+-----------------------+ | 2007-11-30 10:41:47 | 20071130104147.000000 | +---------------------+-----------------------+
Before MySQL 5.0.8, 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' or
'0000-00-00').
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 nondelimited 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 value 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, you can explicitly specify a value of zero to represent
missing month or day parts. For example, you can use
'990300' to insert the value
'1999-03-00'.
You can to some extent assign values of one date type to an object of a different date type. However, there may be some alteration of the value or loss of information:
If you assign a DATE value to
a DATETIME or
TIMESTAMP object, the time
part of the resulting value is set to
'00:00:00' because the
DATE value contains no time
information.
If you assign a DATETIME or
TIMESTAMP value to a
DATE object, the time part of
the resulting value is deleted because the
DATE type stores no time
information.
Remember that although
DATETIME,
DATE, and
TIMESTAMP values all can be
specified using the same set of formats, the types do not
all have the same range of values. For example,
TIMESTAMP values cannot be
earlier than 1970 UTC or later than
'2038-01-09 03:14:07' UTC. This means
that a date such as '1968-01-01', while
legal as a DATETIME or
DATE value, is not valid as a
TIMESTAMP value and is
converted to 0.
Be aware of certain problems 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.
As of 5.0.2, the server requires that month and day values
be legal, and not merely in the range 1 to 12 and 1 to 31,
respectively. With strict mode disabled, invalid dates such
as '2004-04-31' are converted to
'0000-00-00' and a warning is generated.
With strict mode enabled, invalid dates generate an error.
To allow such dates, enable
ALLOW_INVALID_DATES. See
Section 5.1.7, “Server SQL Modes”, for more information.
Before MySQL 5.0.2, 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, you should perform a check in your application.
As of MySQL 5.0.2, MySQL does not accept timestamp values
that include a zero in the day or month column or values
that are not a valid date. The sole exception to this rule
is the special value '0000-00-00
00:00:00'.
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.
In older versions of MySQL (prior to 4.1), the properties of
the TIMESTAMP data type
differ significantly in several ways from what is described
in this section. See the MySQL 3.23, 4.0, 4.1
Reference Manual for details.
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'.
TIMESTAMP values are converted
from the current time zone to UTC for storage, and converted
back from UTC to the current time zone for retrieval. (This
occurs only for the TIMESTAMP
data type, not for other types such as
DATETIME.) By default, the
current time zone for each connection is the server's time.
The time zone can be set on a per-connection basis, as
described in Section 9.7, “MySQL Server Time Zone Support”. As long as
the time zone setting remains constant, you get back the same
value you store. If you store a
TIMESTAMP value, and then
change the time zone and retrieve the value, the retrieved
value is different from the value you stored. This occurs
because the same time zone was not used for conversion in both
directions. The current time zone is available as the value of
the time_zone system
variable.
The TIMESTAMP data type offers
automatic initialization and updating. You can choose whether
to use these properties and which column should have them:
For one TIMESTAMP column in
a table, you can assign the current timestamp as the
default value and the auto-update value. It is possible to
have the current timestamp be the default value for
initializing the column, for the auto-update value, or
both. It is not possible to have the current timestamp be
the default value for one column and the auto-update value
for another column.
Any single TIMESTAMP column
in a table can be used as the one that is initialized to
the current date and time, or updated automatically. This
need not be the first
TIMESTAMP column.
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.
In a CREATE TABLE
statement, the first
TIMESTAMP column can be
declared in any of the following ways:
With both DEFAULT CURRENT_TIMESTAMP
and ON UPDATE CURRENT_TIMESTAMP
clauses, the column has the current timestamp for its
default value, and is automatically updated.
With neither DEFAULT nor
ON UPDATE clauses, it is the same
as DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP.
With a DEFAULT CURRENT_TIMESTAMP
clause and no ON UPDATE clause, the
column has the current timestamp for its default value
but is not automatically updated.
With no DEFAULT clause and with an
ON UPDATE CURRENT_TIMESTAMP clause,
the column has a default of 0 and is automatically
updated.
With a constant DEFAULT value, the
column has the given default and is not automatically
initialized to the current timestamp. If the column
also has an ON UPDATE
CURRENT_TIMESTAMP clause, it is
automatically updated; otherwise, it has a constant
default and is not automatically updated.
In other words, you can use the current timestamp for both
the initial value and the auto-update value, or either
one, or neither. (For example, you can specify ON
UPDATE to enable auto-update without also having
the column auto-initialized.) The following column
definitions demonstrate each of the possibilities:
Auto-initialization and auto-update:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Auto-initialization only:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
Auto-update only:
ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
Neither:
ts TIMESTAMP DEFAULT 0
To specify automatic default or updating for a
TIMESTAMP column other than
the first one, you must suppress the automatic
initialization and update behaviors for the first
TIMESTAMP column by
explicitly assigning it a constant
DEFAULT value (for example,
DEFAULT 0 or DEFAULT
'2003-01-01 00:00:00'). Then, for the other
TIMESTAMP column, the rules
are the same as for the first
TIMESTAMP column, except
that if you omit both of the DEFAULT
and ON UPDATE clauses, no automatic
initialization or updating occurs.
Example:
CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CURRENT_TIMESTAMP or any of
its synonyms
(CURRENT_TIMESTAMP(),
NOW(),
LOCALTIME,
LOCALTIME(),
LOCALTIMESTAMP, or
LOCALTIMESTAMP()) can be
used in the DEFAULT and ON
UPDATE clauses. They all mean “the current
timestamp.”
(UTC_TIMESTAMP is not
allowed. Its range of values does not align with those of
the TIMESTAMP column anyway
unless the current time zone is UTC.)
The order of the DEFAULT and
ON UPDATE attributes does not matter.
If both DEFAULT and ON
UPDATE are specified for a
TIMESTAMP column, either
can precede the other. For example, these statements are
equivalent:
CREATE TABLE t (ts TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DEFAULT CURRENT_TIMESTAMP);
The examples that use DEFAULT 0 will not
work if the NO_ZERO_DATE
SQL mode is enabled because that mode causes
“zero” date values (specified as
0, '0000-00-00, or
'0000-00-00 00:00:00') to be rejected. Be
aware that the TRADITIONAL
SQL mode includes
NO_ZERO_DATE.
TIMESTAMP columns are
NOT NULL by default, cannot contain
NULL values, and assigning
NULL assigns the current timestamp.
However, a TIMESTAMP column can
be allowed to contain NULL by declaring it
with the NULL attribute. In this case, the
default value also becomes NULL unless
overridden with a DEFAULT clause that
specifies a different default value. DEFAULT
NULL can be used to explicitly specify
NULL as the default value. (For a
TIMESTAMP column not declared
with the NULL attribute, DEFAULT
NULL is illegal.) If a
TIMESTAMP column allows
NULL values, assigning
NULL sets it to NULL,
not to the current timestamp.
The following table contains several
TIMESTAMP columns that allow
NULL values:
CREATE TABLE t ( ts1 TIMESTAMP NULL DEFAULT NULL, ts2 TIMESTAMP NULL DEFAULT 0, ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP );
Note that a TIMESTAMP column
that allows NULL values will
not take on the current timestamp except
under one of the following conditions:
Its default value is defined as
CURRENT_TIMESTAMP
NOW() or
CURRENT_TIMESTAMP is
inserted into the column
In other words, a TIMESTAMP
column defined as NULL will auto-initialize
only if it is created using a definition such as the
following:
CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
Otherwise — that is, if the
TIMESTAMP column is defined to
allow NULL values but not using
DEFAULT CURRENT_TIMESTAMP, as shown
here…
CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT NULL); CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
…then you must explicitly insert a value corresponding to the current date and time. For example:
INSERT INTO t1 VALUES (NOW()); INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
The MySQL server can be run with the
MAXDB SQL mode enabled.
When the server runs with this mode enabled,
TIMESTAMP is identical with
DATETIME. That is, if this
mode is enabled at the time that a table is created,
TIMESTAMP columns are created
as DATETIME columns. As a
result, such columns use
DATETIME display format, have
the same range of values, and there is no automatic
initialization or updating to the current date and time.
To enable MAXDB mode, set
the server SQL mode to MAXDB
at startup using the
--sql-mode=MAXDB server option
or by setting the global
sql_mode variable at runtime:
mysql> SET GLOBAL sql_mode=MAXDB;
A client can cause the server to run in
MAXDB mode for its own
connection as follows:
mysql> SET SESSION sql_mode=MAXDB;
MySQL retrieves and displays TIME
values in 'HH:MM:SS' format (or
'HHH:MM:SS' format for large hours values).
TIME values may range from
'-838:59:59' to
'838:59:59'. The hours part may be so large
because the TIME type can be used
not only to represent a time of day (which must be less than 24
hours), but also elapsed time or a time interval between two
events (which may be much greater than 24 hours, or even
negative).
You can specify TIME values in a
variety of formats:
As a string in 'D HH:MM:SS.fraction'
format. You can also use one of the following
“relaxed” syntaxes:
'HH:MM:SS.fraction',
'HH:MM:SS', 'HH:MM',
'D HH:MM:SS', 'D
HH:MM', 'D HH', or
'SS'. Here D
represents days and can have a value from 0 to 34. Note that
MySQL does not store the fraction part.
As a string with no delimiters in
'HHMMSS' format, provided that it makes
sense as a time. For example, '101112' is
understood as '10:11:12', but
'109712' is illegal (it has a nonsensical
minute part) and becomes '00:00:00'.
As a number in HHMMSS format, provided
that it makes sense as a time. For example,
101112 is understood as
'10:11:12'. The following alternative
formats are also understood: SS,
MMSS, HHMMSS,
HHMMSS.fraction. Note that MySQL does not
store the fraction part.
As the result of a function that returns a value that is
acceptable in a TIME context,
such as CURRENT_TIME.
A trailing .uuuuuu microseconds part of
TIME values is allowed under the
same conditions as for other temporal values, as described in
Section 10.3.1, “The DATETIME,
DATE, and
TIMESTAMP Types”. This includes the property that any
microseconds part is discarded from values stored into
TIME columns.
For TIME values specified as
strings that include a time part delimiter, it is not necessary
to specify two digits for hours, minutes, or seconds values that
are less than 10. '8:3:2'
is the same as '08:03:02'.
Be careful about assigning abbreviated values to a
TIME column. Without colons,
MySQL interprets values using the assumption that the two
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' mean '11:12:00',
not '00:11:12'.
By default, values that lie outside the
TIME range but are otherwise
legal are clipped to the closest endpoint of the range. For
example, '-850:00:00' and
'850:00:00' are converted to
'-838:59:59' and
'838:59:59'. Illegal
TIME values are converted to
'00:00:00'. Note that because
'00:00:00' is itself a legal
TIME value, there is no way to
tell, from a value of '00:00:00' stored in a
table, whether the original value was specified as
'00:00:00' or whether it was illegal.
For more restrictive treatment of invalid
TIME values, enable strict SQL
mode to cause errors to occur. See
Section 5.1.7, “Server SQL Modes”.
The YEAR type is a one-byte type
used for representing years. It can be declared as
YEAR(2) or YEAR(4) to
specify a display width of two or four characters. The default
is four characters if no width is given.
For four-digit format, MySQL displays
YEAR values in
YYYY format, with a range of
1901 to 2155. For
two-digit format, MySQL displays values with a range of
70 (1970) to 69 (2069).
You can specify input YEAR values
in a variety of formats:
As a four-digit string in the range
'1901' to '2155'.
As a four-digit number in the range 1901
to 2155.
As a two-digit string in the range '00'
to '99'. Values in the ranges
'00' to '69' and
'70' to '99' are
converted to YEAR values in
the ranges 2000 to
2069 and 1970 to
1999.
As a two-digit number in the range 1 to
99. Values in the ranges
1 to 69 and
70 to 99 are converted
to YEAR values in the ranges
2001 to 2069 and
1970 to 1999. Note
that the range for two-digit numbers is slightly different
from the range for two-digit strings, because you cannot
specify zero directly as a number and have it be interpreted
as 2000. You must specify it as a string
'0' or '00' or it is
interpreted as 0000.
As the result of a function that returns a value that is
acceptable in a YEAR context,
such as NOW().
Illegal YEAR values are converted
to 0000.
MySQL Server itself has no problems with Year 2000 (Y2K) compliance:
MySQL Server uses Unix time functions that handle dates into
the year 2038 for
TIMESTAMP values. For
DATE and
DATETIME values, dates
through the year 9999 are accepted.
All MySQL date functions are implemented in one source file,
sql/time.cc, and are coded very
carefully to be year 2000-safe.
In MySQL, the YEAR data type
can store the years 0 and
1901 to 2155 in one
byte and display them using two or four digits. All
two-digit years are considered to be in the range
1970 to 2069, which
means that if you store 01 in a
YEAR column, MySQL Server
treats it as 2001.
Although MySQL Server itself is Y2K-safe, you may run into
problems if you use it with applications that are not Y2K-safe.
For example, many old applications store or manipulate years
using two-digit values (which are ambiguous) rather than
four-digit values. This problem may be compounded by
applications that use values such as 00 or
99 as “missing” value
indicators. Unfortunately, these problems may be difficult to
fix because different applications may be written by different
programmers, each of whom may use a different set of conventions
and date-handling functions.
Thus, even though MySQL Server has no Y2K problems, it is the application's responsibility to provide unambiguous input. Any value containing a two-digit year is ambiguous, because the century is unknown. Such values must be interpreted into four-digit form because MySQL stores years internally using four digits.
For DATETIME,
DATE,
TIMESTAMP, and
YEAR types, MySQL interprets
dates with ambiguous year values using the following rules:
Year values in the range 00-69 are
converted to 2000-2069.
Year values in the range 70-99 are
converted to 1970-1999.
Remember that these rules are only heuristics that provide reasonable guesses as to what your data values mean. If the rules used by MySQL do not produce the correct values, you should provide unambiguous input containing four-digit year values.
ORDER BY properly sorts
YEAR values that have two-digit
years.
Some functions like MIN() and
MAX() convert a
YEAR to a number. This means that
a value with a two-digit year does not work properly with these
functions. The fix in this case is to convert the
TIMESTAMP or
YEAR to four-digit year format.
The string types are CHAR,
VARCHAR,
BINARY,
VARBINARY,
BLOB,
TEXT,
ENUM, and
SET. This section describes how
these types work and how to use them in your queries. For string
type storage requirements, see
Section 10.5, “Data Type Storage Requirements”.
The CHAR and
VARCHAR types are similar, but
differ in the way they are stored and retrieved. As of MySQL
5.0.3, they also differ in maximum length and in whether
trailing spaces are retained.
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.
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. 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 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and
later versions. The effective maximum length of a
VARCHAR in MySQL 5.0.3 and later
is subject to the maximum row size (65,535 bytes, which is
shared among all columns) and the character set used.
In contrast to CHAR,
VARCHAR values are stored as a
one-byte or two-byte length prefix plus data. The length prefix
indicates the number of bytes in the value. A column uses one
length byte if values require no more than 255 bytes, two length
bytes if values may require more than 255 bytes.
If strict SQL mode is not enabled and you assign a value to a
CHAR or
VARCHAR column that exceeds the
column's maximum length, the value is truncated to fit and a
warning is generated. For truncation of nonspace characters, you
can cause an error to occur (rather than a warning) and suppress
insertion of the value by using strict SQL mode. See
Section 5.1.7, “Server SQL Modes”.
For VARCHAR columns, trailing
spaces in excess of the column length are truncated prior to
insertion and a warning is generated, regardless of the SQL mode
in use. For CHAR columns,
truncation of excess trailing spaces from inserted values is
performed silently regardless of the SQL mode.
VARCHAR values are not padded
when they are stored. Handling of trailing spaces is
version-dependent. As of MySQL 5.0.3, trailing spaces are
retained when values are stored and retrieved, in conformance
with standard SQL. Before MySQL 5.0.3, trailing spaces are
removed from values when they are stored into a
VARCHAR column; this means that
the spaces also are absent from retrieved values.
Before MySQL 5.0.3, if you need a data type for which trailing
spaces are not removed, consider using a
BLOB or
TEXT type. Also, if you want to
store binary values such as results from an encryption or
compression function that might contain arbitrary byte values,
use a BLOB column rather than a
CHAR or
VARCHAR column, to avoid
potential problems with trailing space removal that would change
data values.
The following table illustrates the differences between
CHAR and
VARCHAR by showing the result of
storing various string values into CHAR(4)
and VARCHAR(4) columns (assuming that the
column uses a single-byte character set such as
latin1).
| Value | CHAR(4) | Storage Required | VARCHAR(4) | Storage Required |
'' | ' ' | 4 bytes | '' | 1 byte |
'ab' | 'ab ' | 4 bytes | 'ab' | 3 bytes |
'abcd' | 'abcd' | 4 bytes | 'abcd' | 5 bytes |
'abcdefgh' | 'abcd' | 4 bytes | 'abcd' | 5 bytes |
The values shown as stored in the last row of the table apply only when not using strict mode; if MySQL is running in strict mode, values that exceed the column length are not stored, and an error results.
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. The following example illustrates this
difference:
mysql>CREATE TABLE vc (v VARCHAR(4), c CHAR(4));Query OK, 0 rows affected (0.01 sec) mysql>INSERT INTO vc VALUES ('ab ', 'ab ');Query OK, 1 row affected (0.00 sec) mysql>SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;+---------------------+---------------------+ | CONCAT('(', v, ')') | CONCAT('(', c, ')') | +---------------------+---------------------+ | (ab ) | (ab) | +---------------------+---------------------+ 1 row in set (0.06 sec)
Values in CHAR and
VARCHAR columns are sorted and
compared according to the character set collation assigned to
the column.
All MySQL collations are of type PADSPACE.
This means that all CHAR and
VARCHAR values in MySQL are
compared without regard to any trailing spaces. For example:
mysql>CREATE TABLE names (myname CHAR(10), yourname VARCHAR(10));Query OK, 0 rows affected (0.09 sec) mysql>INSERT INTO names VALUES ('Monty ', 'Monty ');Query OK, 1 row affected (0.00 sec) mysql>SELECT myname = 'Monty ', yourname = 'Monty ' FROM names;+--------------------+----------------------+ | myname = 'Monty ' | yourname = 'Monty ' | +--------------------+----------------------+ | 1 | 1 | +--------------------+----------------------+ 1 row in set (0.00 sec)
This is true for all MySQL versions, and it makes no difference
whether your version trims trailing spaces from
VARCHAR values before storing
them. Nor does the server SQL mode make any difference in this
regard.
For 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 and
VARBINARY types are similar to
CHAR and
VARCHAR, except that they contain
binary strings rather than nonbinary strings. That is, they
contain byte strings rather than character strings. This means
that they have no character set, and sorting and comparison are
based on the numeric values of the bytes in the values.
The 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.
The BINARY and
VARBINARY data types are distinct
from the CHAR BINARY and VARCHAR
BINARY data types. For the latter types, the
BINARY attribute does not cause the column to
be treated as a binary string column. Instead, it causes the
binary collation for the column character set to be used, and
the column itself contains nonbinary character strings rather
than binary byte strings. For example, CHAR(5)
BINARY is treated as CHAR(5) CHARACTER SET
latin1 COLLATE latin1_bin, assuming that the default
character set is latin1. This differs from
BINARY(5), which stores 5-bytes binary
strings that have no character set or collation. For information
about differences between nonbinary string binary collations and
binary strings, see Section 9.1.6.4, “The _bin and binary Collations”.
If strict SQL mode is not enabled and you assign a value to a
BINARY or
VARBINARY column that exceeds the
column's maximum length, the value is truncated to fit and a
warning is generated. For cases of truncation, you can cause an
error to occur (rather than a warning) and suppress insertion of
the value by using strict SQL mode. See
Section 5.1.7, “Server SQL Modes”.
When BINARY values are stored,
they are right-padded with the pad value to the specified
length. The pad value and how it is handled is version specific:
As of MySQL 5.0.15, the pad value is 0x00
(the zero byte). Values are right-padded with
0x00 on insert, and no trailing bytes are
removed on select. All bytes are significant in comparisons,
including ORDER BY and
DISTINCT operations.
0x00 bytes and spaces are different in
comparisons, with 0x00 < space.
Example: For a BINARY(3) column,
'a ' becomes
'a \0' when inserted.
'a\0' becomes 'a\0\0'
when inserted. Both inserted values remain unchanged when
selected.
Before MySQL 5.0.15, the pad value is space. Values are
right-padded with space on insert, and trailing spaces are
removed on select. Trailing spaces are ignored in
comparisons, including ORDER BY and
DISTINCT operations.
0x00 bytes and spaces are different in
comparisons, with 0x00 < space.
Example: For a BINARY(3) column,
'a ' becomes
'a ' when inserted and
'a' when selected.
'a\0' becomes
'a\0 ' when inserted and
'a\0' when selected.
For VARBINARY, there is no
padding on insert and no bytes are stripped on select. All bytes
are significant in comparisons, including ORDER
BY and DISTINCT operations.
0x00 bytes and spaces are different in
comparisons, with 0x00 < space.
(Exceptions: Before MySQL 5.0.3, trailing spaces are removed
when values are stored. Before MySQL 5.0.15, trailing 0x00 bytes
are removed for ORDER BY operations.)
Note: The InnoDB storage engine continues to
preserve trailing spaces in
BINARY and
VARBINARY column values through
MySQL 5.0.18. Beginning with MySQL 5.0.19,
InnoDB uses trailing space characters in
making comparisons as do other MySQL storage engines.
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\0' causes a
duplicate-key error.
You should consider the preceding padding and stripping
characteristics carefully if you plan to use the
BINARY data type for storing
binary data and you require that the value retrieved be exactly
the same as the value stored. The following example illustrates
how 0x00-padding of
BINARY values affects column
value comparisons:
mysql>CREATE TABLE t (c BINARY(3));Query OK, 0 rows affected (0.01 sec) mysql>INSERT INTO t SET c = 'a';Query OK, 1 row affected (0.01 sec) mysql>SELECT HEX(c), c = 'a', c = 'a\0\0' from t;+--------+---------+-------------+ | HEX(c) | c = 'a' | c = 'a\0\0' | +--------+---------+-------------+ | 610000 | 0 | 1 | +--------+---------+-------------+ 1 row in set (0.09 sec)
If the value retrieved must be the same as the value specified
for storage with no padding, it might be preferable to use
VARBINARY or one of the
BLOB data types instead.
A BLOB is a binary large object
that can hold a variable amount of data. The four
BLOB types are
TINYBLOB,
BLOB,
MEDIUMBLOB, and
LONGBLOB. These differ only in
the maximum length of the values they can hold. The four
TEXT types are
TINYTEXT,
TEXT,
MEDIUMTEXT, and
LONGTEXT. These correspond to the
four BLOB types and have the same
maximum lengths and storage requirements. See
Section 10.5, “Data Type Storage Requirements”.
BLOB columns are treated as
binary strings (byte strings).
TEXT columns are treated as
nonbinary 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.
If strict SQL mode is not enabled and you assign a value to a
BLOB or
TEXT column that exceeds the
column's maximum length, the value is truncated to fit and a
warning is generated. For truncation of nonspace characters, you
can cause an error to occur (rather than a warning) and suppress
insertion of the value by using strict SQL mode. See
Section 5.1.7, “Server SQL Modes”.
Beginning with MySQL 5.0.60, truncation of excess trailing
spaces from values to be inserted into
TEXT columns always generates a
warning, regardless of the SQL mode. (Bug#30059)
If a TEXT column is indexed,
index entry comparisons are space-padded at the end. This means
that, if the index requires unique values, duplicate-key errors
will occur for values that differ only in the number of trailing
spaces. For example, if a table contains 'a',
an attempt to store 'a ' causes a
duplicate-key error. This is not true for
BLOB columns.
In most respects, you can regard a
BLOB column as a
VARBINARY column that can be as
large 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. Before MySQL 5.0.3, this differs from
VARBINARY and
VARCHAR, for which trailing
spaces are removed when values are stored.
On comparisons, TEXT is space
extended to fit the compared object, exactly like
CHAR and
VARCHAR.
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.2, “Column Indexes”.
LONG and LONG VARCHAR map
to the MEDIUMTEXT data type. This
is a compatibility feature. If you use the
BINARY attribute with a
TEXT data type, the column is
assigned the binary collation of the column character set.
MySQL Connector/ODBC defines BLOB
values as LONGVARBINARY and
TEXT values as
LONGVARCHAR.
Because BLOB and
TEXT values can be extremely
long, you might encounter some constraints in using them:
Only the first
max_sort_length bytes of
the column are used when sorting. The default value of
max_sort_length is 1024.
This value can be changed using the
--max_sort_length=
option when starting the mysqld server.
See Section 5.1.3, “Server System Variables”.
N
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);
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.3, “Tuning Server Parameters”,
Section 4.5.1, “mysql — The MySQL Command-Line Tool”, and Section 4.5.4, “mysqldump — A Database Backup Program”.
You may also want to compare the packet sizes and the size
of the data objects you are storing with the storage
requirements, see Section 10.5, “Data Type Storage Requirements”
Each BLOB or
TEXT value is represented
internally by a separately allocated object. This is in contrast
to all other data types, for which storage is allocated once per
column when the table is opened.
In some cases, it may be desirable to store binary data such as
media files in BLOB or
TEXT columns. You may find
MySQL's string handling functions useful for working with such
data. See Section 11.4, “String Functions”. For security and
other reasons, it is usually preferable to do so using
application code rather than allowing application users the
FILE privilege. You can discuss
specifics for various languages and platforms in the MySQL
Forums (http://forums.mysql.com/).
An ENUM is a string object with a
value chosen from a list of allowed values that are enumerated
explicitly in the column specification at table creation time.
An enumeration value must be a quoted string literal; it may not
be an expression, even one that evaluates to a string value. For
example, you can create a table with an
ENUM column like this:
CREATE TABLE sizes (
name ENUM('small', 'medium', 'large')
);
However, this version of the previous
CREATE TABLE statement does
not work:
CREATE TABLE sizes (
c1 ENUM('small', CONCAT('med','ium'), 'large')
);
You also may not employ a user variable as an enumeration value. This pair of statements do not work:
SET @mysize = 'medium';
CREATE TABLE sizes (
name ENUM('small', @mysize, 'large')
);
If you wish to use a number as an enumeration value, you must enclose it in quotes.
Duplicate values in the definition cause a warning, or an error if strict SQL mode is enabled.
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.
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. Note that
ENUM columns can be assigned a
character set and collation. For binary or case-sensitive
collations, lettercase is taken into account when assigning
values to the column.
If you retrieve an ENUM value in
a numeric context, the column value's index is returned. For
example, you can retrieve numeric values from an
ENUM column like this:
mysql> SELECT enum_col+0 FROM tbl_name;
If you store a number into an
ENUM column, the number is
treated as the index into the possible values, and the value
stored is the enumeration member with that index. (However, this
does not work with
LOAD DATA, which treats all input
as strings.) If the numeric value is quoted, it is still
interpreted as an index if there is no matching string in the
list of enumeration values. For these reasons, it is not
advisable to define an ENUM
column with enumeration values that look like numbers, because
this can easily become confusing. For example, the following
column has enumeration members with string values of
'0', '1', and
'2', but numeric index values of
1, 2, and
3:
numbers ENUM('0','1','2')
If you store 2, it is interpreted as an index
value, and becomes '1' (the value with index
2). If you store '2', it matches an
enumeration value, so it is stored as '2'. If
you store '3', it does not match any
enumeration value, so it is treated as an index and becomes
'2' (the value with index 3).
mysql>INSERT INTO t (numbers) VALUES(2),('2'),('3');mysql>SELECT * FROM t;+---------+ | numbers | +---------+ | 1 | | 2 | | 2 | +---------+
ENUM values are sorted according
to the order in which the enumeration members were listed in the
column specification. (In other words,
ENUM values are sorted according
to their index numbers.) For example, 'a'
sorts before 'b' for ENUM('a',
'b'), but 'b' sorts before
'a' for ENUM('b', 'a').
The empty string sorts before nonempty strings, and
NULL values sort before all other enumeration
values. To prevent unexpected results, specify the
ENUM list in alphabetical order.
You can also use GROUP BY CAST(col AS CHAR)
or GROUP BY CONCAT(col) to make sure that the
column is sorted lexically rather than by index number.
Functions such as SUM() or
AVG() that expect a numeric
argument cast the argument to a number if necessary. For
ENUM values, the cast operation
causes the index number to be used.
If you want to determine all possible values for an
ENUM column, use SHOW
COLUMNS FROM and parse the
tbl_name LIKE
enum_colENUM definition in the
Type column of the output.
A SET is a string object that can
have zero or more values, each of which must be chosen from a
list of allowed values specified when the table is created.
SET column values that consist of
multiple set members are specified with members separated by
commas (“,”). A consequence of
this is that SET member values
should not themselves contain commas.
For example, a column specified as SET('one', 'two')
NOT NULL can have any of these values:
'' 'one' 'two' 'one,two'
A SET can have a maximum of 64
different members.
Duplicate values in the definition cause a warning, or an error if strict SQL mode is enabled.
Trailing spaces are automatically deleted from
SET member values in the table
definition when a table is created.
When retrieved, values stored in a
SET column are displayed using
the lettercase that was used in the column definition. Note that
SET columns can be assigned a
character set and collation. For binary or case-sensitive
collations, lettercase is taken into account when assigning
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, so the first and
fourth SET value members
'a' and 'd' are selected
and the resulting value is 'a,d'.
For a value containing more than one
SET element, it does not matter
what order the elements are listed in when you insert the value.
It also does not matter how many times a given element is listed
in the value. When the value is retrieved later, each element in
the value appears once, with elements listed according to the
order in which they were specified at table creation time. For
example, suppose that a column is specified as
SET('a','b','c','d'):
mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
If you insert the values 'a,d',
'd,a', 'a,d,d',
'a,d,a', and 'd,a,d':
mysql> INSERT INTO myset (col) VALUES
-> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
Then all of these values appear as 'a,d' when
retrieved:
mysql> SELECT col FROM myset;
+------+
| col |
+------+
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
+------+
5 rows in set (0.04 sec)
If you set a SET column to an
unsupported value, the value is ignored and a warning is issued:
mysql>INSERT INTO myset (col) VALUES ('a,d,d,s');Query OK, 1 row affected, 1 warning (0.03 sec) mysql>SHOW WARNINGS;+---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1265 | Data truncated for column 'col' at row 1 | +---------+------+------------------------------------------+ 1 row in set (0.04 sec) mysql>SELECT col FROM myset;+------+ | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d | | a,d | +------+ 6 rows in set (0.01 sec)
If strict SQL mode is enabled, attempts to insert invalid
SET values result in an error.
SET values are sorted
numerically. NULL values sort before
non-NULL SET
values.
Functions such as SUM() or
AVG() that expect a numeric
argument cast the argument to a number if necessary. For
SET values, the cast operation
causes the numeric value to be used.
Normally, you search for SET
values using the FIND_IN_SET()
function or the LIKE operator:
mysql>SELECT * FROMmysql>tbl_nameWHERE FIND_IN_SET('value',set_col)>0;SELECT * FROMtbl_nameWHEREset_colLIKE '%value%';
The first statement finds rows where
set_col contains the
value set member. The second is
similar, but not the same: It finds rows where
set_col contains
value anywhere, even as a substring
of another set member.
The following statements also are legal:
mysql>SELECT * FROMmysql>tbl_nameWHEREset_col& 1;SELECT * FROMtbl_nameWHEREset_col= 'val1,val2';
The first of these statements looks for values containing the
first set member. The second looks for an exact match. Be
careful with comparisons of the second type. Comparing set
values to
'
returns different results than comparing values to
val1,val2''.
You should specify the values in the same order they are listed
in the column definition.
val2,val1'
If you want to determine all possible values for a
SET column, use SHOW
COLUMNS FROM and parse the
tbl_name LIKE
set_colSET definition in the
Type column of the output.
The storage requirements for each of the data types supported by MySQL are listed here by category.
The maximum size of a row in a MyISAM table is
65,535 bytes. (However, each BLOB
or TEXT column contributes only
9–12 bytes toward this size.) This limitation may be shared
by other storage engines as well. See
Chapter 13, Storage Engines, for more information.
For tables using the NDBCLUSTER
storage engine, there is the factor of 4-byte
alignment to be taken into account when calculating
storage requirements. This means that all
NDB data storage is done in
multiples of 4 bytes. Thus, a column value that would take 15
bytes in a table using a storage engine other than
NDB requires 16 bytes in an
NDB table. This requirement applies
in addition to any other considerations that are discussed in
this section. For example, in
NDBCLUSTER tables, the
TINYINT,
SMALLINT,
MEDIUMINT, and
INTEGER
(INT) column types each require 4
bytes storage per record due to the alignment factor.
An exception to this rule is the
BIT type, which is
not 4-byte aligned. In MySQL Cluster
tables, a BIT(
column takes M)M bits of storage space.
However, if a table definition contains 1 or more
BIT columns (up to 32
BIT columns), then
NDBCLUSTER reserves 4 bytes (32
bits) per row for these. If a table definition contains more
than 32 BIT columns (up to 64
such columns), then NDBCLUSTER
reserves 8 bytes (that is, 64 bits) per row.
In addition, while a NULL itself does not
require any storage space,
NDBCLUSTER reserves 4 bytes per row
if the table definition contains any columns defined as
NULL, up to 32 NULL
columns. (If a MySQL Cluster table is defined with more than 32
NULL columns up to 64 NULL
columns, then 8 bytes per row is reserved.)
When calculating storage requirements for MySQL Cluster tables,
you must also remember that every table using the
NDBCLUSTER storage engine requires a
primary key; if no primary key is defined by the user, then a
“hidden” primary key will be created by
NDB. This hidden primary key consumes
31-35 bytes per table record.
You may find the ndb_size.pl utility to be
useful for estimating NDB storage
requirements. This Perl script connects to a current MySQL
(non-Cluster) database and creates a report on how much space that
database would require if it used the
NDBCLUSTER storage engine. See
Section 17.6.19, “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 |
BIT( | approximately (M+7)/8 bytes |
The storage requirements for
DECIMAL (and
NUMERIC) are version-specific:
As of MySQL 5.0.3, values for
DECIMAL columns are represented
using a binary format that packs nine decimal (base 10) digits
into four bytes. Storage for the integer and fractional parts of
each value are determined separately. Each multiple of nine digits
requires four bytes, and the “leftover” digits
require some fraction of four bytes. The storage required for
excess digits is given by the following table.
| Leftover Digits | Number of Bytes |
| 0 | 0 |
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | 3 |
| 6 | 3 |
| 7 | 4 |
| 8 | 4 |
Before MySQL 5.0.3, DECIMAL columns
are represented as strings and storage requirements are:
M+2 bytes if
D > 0,
M+1D = 0, D+2
if M <
D
Storage Requirements for Date and Time Types
The storage requirements shown in the table arise from the way that MySQL represents temporal values:
DATE: A three-byte integer
packed as DD +
MM×32 +
YYYY×16×32
TIME: A three-byte integer
packed as DD×24×3600 +
HH×3600 +
MM×60 + SS
DATETIME: Eight bytes:
A four-byte integer packed as
YYYY×10000 +
MM×100 +
DD
A four-byte integer packed as
HH×10000 +
MM×100 +
SS
TIMESTAMP: A four-byte integer
representing seconds UTC since the epoch ('1970-01-01
00:00:00' UTC)
YEAR: A one-byte integer
Storage Requirements for String Types
In the following table, M represents
the declared column length in characters for nonbinary string
types and bytes for binary string types.
L represents the actual length in bytes
of a given string value.
| Data Type | Storage Required |
CHAR( | M × w bytes,
0 <= 255, where w is
the number of bytes required for the maximum-length
character in the character set |
BINARY( | M bytes, 0 <=
255 |
VARCHAR(,
VARBINARY( | L + 1 bytes if column values require 0
– 255 bytes, L + 2 bytes
if values may require more than 255 bytes |
TINYBLOB,
TINYTEXT | L + 1 bytes, where
L <
28 |
BLOB, TEXT | L + 2 bytes, where
L <
216 |
MEDIUMBLOB,
MEDIUMTEXT | L + 3 bytes, where
L <
224 |
LONGBLOB,
LONGTEXT | L + 4 bytes, where
L <
232 |
ENUM(' | 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum) |
SET(' | 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum) |
Variable-length string types are stored using a length prefix plus
data. The length prefix requires from one to four bytes depending
on the data type, and the value of the prefix is
L (the byte length of the string). For
example, storage for a MEDIUMTEXT
value requires L bytes to store the
value plus three bytes to store the length of the value.
To calculate the number of bytes used to store a particular
CHAR,
VARCHAR, or
TEXT column value, you must take
into account the character set used for that column and whether
the value contains multi-byte characters. In particular, when
using the utf8 Unicode character set, you must
keep in mind that not all utf8 characters use
the same number of bytes and can require up to three bytes per
character. For a breakdown of the storage used for different
categories of utf8 characters, see
Section 9.1.9, “Unicode Support”.
VARCHAR,
VARBINARY, and the
BLOB and
TEXT types are variable-length
types. For each, the storage requirements depend on these factors:
The actual length of the column value
The column's maximum possible length
The character set used for the column, because some character sets contain multi-byte characters
For example, a VARCHAR(255) column can hold a
string with a maximum length of 255 characters. Assuming that the
column uses the latin1 character set (one byte
per character), 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. If the same column is instead declared to use the
ucs2 double-byte character set, the storage
requirement is 10 bytes: The length of 'abcd'
is eight bytes and the column requires two bytes to store lengths
because the maximum length is greater than 255 (up to 510 bytes).
The effective maximum number of bytes that
can be stored in a VARCHAR or
VARBINARY column is subject to
the maximum row size of 65,535 bytes, which is shared among all
columns. For a VARCHAR column
that stores multi-byte characters, the effective maximum number
of characters is less. For example,
utf8 characters can require up to three bytes
per character, so a VARCHAR
column that uses the utf8 character set can
be declared to be a maximum of 21,844 characters.
As of MySQL 5.0.3, the NDBCLUSTER
engine supports only fixed-width columns. This means that a
VARCHAR column from a table in a
MySQL Cluster will behave as follows:
If the size of the column is fewer than 256 characters, the column requires one byte extra storage per row.
If the size of the column is 256 characters or more, the column requires two bytes extra storage per row.
The number of bytes required per character varies according to the
character set used. For example, if a
VARCHAR(100) column in a Cluster table uses the
utf8 character set, each character requires 3
bytes storage. This means that each record in such a column takes
up 100 × 3 + 1 = 301 bytes for storage,
regardless of the length of the string actually stored in any
given record. For a VARCHAR(1000) column in a
table using the NDBCLUSTER storage
engine with the utf8 character set, each record
will use 1000 × 3 + 2 = 3002 bytes storage; that
is, the column is 1,000 characters wide, each character requires 3
bytes storage, and each record has a 2-byte overhead because 1,000
>= 256.
TEXT and
BLOB columns are implemented
differently in the NDB Cluster storage engine, wherein each row 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 rows 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 row);
otherwise, the size is 256 + size +
(2000 – (size – 256) %
2000).
The size of an ENUM object is
determined by the number of different enumeration values. One byte
is used for enumerations with up to 255 possible values. Two bytes
are used for enumerations having between 256 and 65,535 possible
values. See Section 10.4.4, “The ENUM Type”.
The size of a SET object is
determined by the number of different set members. If the set size
is N, the object occupies
( bytes,
rounded up to 1, 2, 3, 4, or 8 bytes. A
N+7)/8SET can have a maximum of 64
members. See Section 10.4.5, “The SET Type”.
For optimum storage, you should 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, this type uses the least amount of storage.
Tables created in MySQL 5.0.3 and above use a new storage format
for DECIMAL columns. All basic
calculations (+, -,
*, and /) with
DECIMAL columns are done with
precision of 65 decimal (base 10) digits. See
Section 10.1.1, “Overview of Numeric Types”.
Prior to MySQL 5.0.3, calculations on
DECIMAL values are performed using
double-precision operations. If accuracy is not too important or
if speed is the highest priority, the
DOUBLE type may 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 as necessary.
PROCEDURE ANALYSE can be used to obtain
suggestions for optimal column data types. For more information,
see Section 21.3.1, “PROCEDURE ANALYSE”.
To facilitate the use of 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 |
BOOL | TINYINT |
BOOLEAN | TINYINT |
CHARACTER VARYING( | VARCHAR( |
FIXED | DECIMAL |
FLOAT4 | FLOAT |
FLOAT8 | DOUBLE |
INT1 | TINYINT |
INT2 | SMALLINT |
INT3 | MEDIUMINT |
INT4 | INT |
INT8 | BIGINT |
LONG VARBINARY | MEDIUMBLOB |
LONG VARCHAR | MEDIUMTEXT |
LONG | MEDIUMTEXT |
MIDDLEINT | MEDIUMINT |
NUMERIC | DECIMAL |
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)