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. Chapter 16, Spatial Extensions, provides information about these data types.
Several of the data type descriptions use these conventions:
M indicates the maximum display width
for integer types. For floating-point and fixed-point types,
M is the total number of digits that
can be stored. For string types, M is
the maximum length. The maximum allowable value of
M depends on the data type.
D applies to floating-point and
fixed-point types and indicates the number of digits following
the decimal point. The maximum possible value is 30, but should
be no greater than M–2.
Square brackets (‘[’ and
‘]’) indicate optional parts of
type definitions.
A summary of the numeric data types follows. For additional information, see Section 11.2, “Numeric Types”. Storage requirements are given in Section 11.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 11.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.
Warning: When you use
subtraction between integer values where one is of type
UNSIGNED, the result is unsigned unless the
NO_UNSIGNED_SUBTRACTION SQL mode is enabled.
See Section 12.9, “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, and
BDB. 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. Non-zero values are
considered true:
mysql>SELECT IF(0, 'true', 'false');+------------------------+ | IF(0, 'true', 'false') | +------------------------+ | false | +------------------------+ mysql>SELECT IF(1, 'true', 'false');+------------------------+ | IF(1, 'true', 'false') | +------------------------+ | true | +------------------------+ mysql>SELECT IF(2, 'true', 'false');+------------------------+ | IF(2, 'true', 'false') | +------------------------+ | true | +------------------------+
However, the values TRUE and
FALSE are merely aliases for
1 and 0, respectively,
as shown here:
mysql>SELECT IF(0 = FALSE, 'true', 'false');+--------------------------------+ | IF(0 = FALSE, 'true', 'false') | +--------------------------------+ | true | +--------------------------------+ mysql>SELECT IF(1 = TRUE, 'true', 'false');+-------------------------------+ | IF(1 = TRUE, 'true', 'false') | +-------------------------------+ | true | +-------------------------------+ mysql>SELECT IF(2 = TRUE, 'true', 'false');+-------------------------------+ | IF(2 = TRUE, 'true', 'false') | +-------------------------------+ | false | +-------------------------------+ mysql>SELECT IF(2 = FALSE, 'true', 'false');+--------------------------------+ | IF(2 = FALSE, 'true', 'false') | +--------------------------------+ | false | +--------------------------------+
The last two statements display the results shown because
2 is equal to neither
1 nor 0.
We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release.
SMALLINT[(
M)] [UNSIGNED]
[ZEROFILL]
A small integer. The signed range is
-32768 to 32767. The
unsigned range is 0 to
65535.
MEDIUMINT[(
M)]
[UNSIGNED] [ZEROFILL]
A medium-sized integer. The signed range is
-8388608 to 8388607.
The unsigned range is 0 to
16777215.
INT[(
M)] [UNSIGNED]
[ZEROFILL]
A normal-size integer. The signed range is
-2147483648 to
2147483647. The unsigned range is
0 to 4294967295.
INTEGER[(
M)] [UNSIGNED]
[ZEROFILL]
This type is a synonym for INT.
BIGINT[(
M)] [UNSIGNED]
[ZEROFILL]
A large integer. The signed range is
-9223372036854775808 to
9223372036854775807. The unsigned range
is 0 to
18446744073709551615.
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, see Section 11.3, “Date and Time Types”. Storage requirements are given in Section 11.5, “Data Type Storage Requirements”. Functions that operate on temporal values are described at Section 12.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 partway through the year
2038. TIMESTAMP values
are stored as the number of seconds since the epoch
('1970-01-01 00:00:00' UTC). A
TIMESTAMP cannot represent the value
'1970-01-01 00:00:00' because that is
equivalent to 0 seconds from the epoch and the value 0 is
reserved for representing '0000-00-00
00:00:00', the “zero”
TIMESTAMP value.
A TIMESTAMP column is useful for
recording the date and time of an INSERT
or UPDATE operation. By default, the
first TIMESTAMP column in a table is
automatically set to the date and time of the most recent
operation if you do not assign it a value yourself. You can
also set any TIMESTAMP column to the
current date and time by assigning it a
NULL value. Variations on automatic
initialization and update properties are described in
Section 11.3.1.1, “TIMESTAMP Properties as of MySQL 4.1”.
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.
Note: 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 non-numeric character.) To work around this problem, you
can convert to numeric units, perform the aggregate operation,
and convert back to a temporal value. Examples:
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROMtbl_name; SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROMtbl_name;
A summary of the string data types follows. For additional information, see Section 11.4, “String Types”. Storage requirements are given in Section 11.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 13.1.5.1, “Silent Column Specification Changes”.
In MySQL 4.1 and up, string data types include some features that you may not have encountered in working with versions of MySQL prior to 4.1:
MySQL interprets length specifications in character column
definitions in character units. (Before MySQL 4.1, column
lengths were interpreted in bytes.) This applies to
CHAR, VARCHAR, and the
TEXT types.
Column definitions for many string data types can include
attributes that specify the character set or collation of
the column. These attributes apply to the
CHAR, VARCHAR, the
TEXT types, ENUM, and
SET data types:
The CHARACTER SET attribute specifies
the character set, and the COLLATE
attribute specifies a collation for the character set.
For example:
CREATE TABLE t
(
c1 VARCHAR(20) CHARACTER SET utf8,
c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);
This table definition creates a column named
c1 that has a character set of
utf8 with the default collation for
that character set, and a column named
c2 that has a character set of
latin1 and a case-sensitive
collation.
CHARSET is a synonym for
CHARACTER SET.
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.
Chapter 10, 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.
The range of M is 0 to 255
characters.
Note: 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. This is a
compatibility feature. 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, if you attempt to set the length
of a CHAR greater than 255, the
CREATE TABLE or ALTER
TABLE statement in which this is done 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 mysql>SHOW CREATE TABLE c1;ERROR 1146 (42S02): Table 'test.c1' doesn't exist
CHAR is shorthand for
CHARACTER. NATIONAL
CHAR (or its equivalent short form,
NCHAR) is the standard SQL way to define
that a CHAR column should use some
predefined character set. MySQL 4.1 and up uses
utf8 as this predefined character set.
Section 10.3.6, “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).
CHAR [CHARACTER SET
charset_name] [COLLATE
collation_name]
This type is a synonym for CHAR(1).
[NATIONAL] VARCHAR(
M)
[CHARACTER SET charset_name]
[COLLATE
collation_name]
A variable-length string. M
represents the maximum column length. 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 actual maximum length of a
VARCHAR in MySQL 5.0 is determined by the
maximum row size and the character set you use. The maximum
effective column length starting with
MySQL 5.0.3 is subject to a row size of 65,532 bytes.)
Note: 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 was converted
to the smallest TEXT type that could hold
values of the given length. For example,
VARCHAR(500) was converted to
TEXT, and
VARCHAR(200000) was converted to
MEDIUMTEXT. This was a compatibility
feature. However, this conversion affected trailing-space
removal.
VARCHAR is shorthand for
CHARACTER VARYING.
VARCHAR values are stored using as many
characters as are needed, plus one byte to record the length
(two bytes for columns that are declared with a length
longer than 255).
The BINARY type is similar to the
CHAR type, but stores binary byte strings
rather than non-binary character strings.
The VARBINARY type is similar to the
VARCHAR type, but stores binary byte
strings rather than non-binary character strings.
A BLOB column with a maximum length of
255 (28 – 1) bytes.
TINYTEXT [CHARACTER SET
charset_name] [COLLATE
collation_name]
A TEXT column with a maximum length of
255 (28 – 1) characters.
A BLOB column with a maximum length of
65,535 (216 – 1) bytes.
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.
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.
MEDIUMTEXT [CHARACTER SET
charset_name] [COLLATE
collation_name]
A TEXT column with a maximum length of
16,777,215 (224 – 1)
characters.
A BLOB column with a maximum length of
4,294,967,295 or 4GB (232 –
1) bytes. The maximum effective
(permitted) length of LONGBLOB columns
depends on the configured maximum packet size in the
client/server protocol and available memory.
LONGTEXT [CHARACTER SET
charset_name] [COLLATE
collation_name]
A TEXT column with a maximum length of
4,294,967,295 or 4GB (232 –
1) characters. The maximum effective
(permitted) length of LONGTEXT columns
depends on the configured maximum packet size in the
client/server protocol and available memory.
ENUM('
value1','value2',...)
[CHARACTER SET charset_name]
[COLLATE
collation_name]
An enumeration. A string object that can have only one
value, chosen from the list of values
',
value1'',
value2'..., NULL or the
special '' error value. An
ENUM column can have a maximum of 65,535
distinct values. ENUM values are
represented internally as integers.
SET('
value1','value2',...)
[CHARACTER SET charset_name]
[COLLATE
collation_name]
A set. A string object that can have zero or more values,
each of which must be chosen from the list of values
',
value1'',
value2'... A SET column can
have a maximum of 64 members. SET values
are represented internally as integers.
The DEFAULT
clause in a data type specification indicates a default value
for a column. With one exception, the default value must be a
constant; it cannot be a function or an expression. This means,
for example, that you cannot set the default for a date column
to be the value of a function such as valueNOW()
or CURRENT_DATE. The exception is that you
can specify CURRENT_TIMESTAMP as the default
for a TIMESTAMP column. See
Section 11.3.1.1, “TIMESTAMP Properties as of MySQL 4.1”.
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 other than integer types declared with the
AUTO_INCREMENT attribute, the default is
0. For an
AUTO_INCREMENT column, the default value
is the next value in the sequence.
For date and time types other than
TIMESTAMP, the default is the appropriate
“zero” value for the type. For the first
TIMESTAMP column in a table, the default
value is the current date and time. See
Section 11.3, “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, 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 non-transactional 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.2.6, “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 11.5, “Data Type Storage Requirements”.
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, and BDB.
As an extension to the SQL standard, MySQL also supports the
integer types TINYINT,
MEDIUMINT, and BIGINT. The
following table shows the required storage and range for each of
the integer types.
| Type | Bytes | Minimum Value | Maximum Value |
| (Signed/Unsigned) | (Signed/Unsigned) | ||
TINYINT | 1 | -128 | 127 |
0 | 255 | ||
SMALLINT | 2 | -32768 | 32767 |
0 | 65535 | ||
MEDIUMINT | 3 | -8388608 | 8388607 |
0 | 16777215 | ||
INT | 4 | -2147483648 | 2147483647 |
0 | 4294967295 | ||
BIGINT | 8 | -9223372036854775808 | 9223372036854775807 |
0 | 18446744073709551615 |
Another extension is supported by MySQL for optionally specifying
the display width of integer data types in parentheses following
the base keyword for the type (for example,
INT(4)). This optional display width is used to
display integer values having a width less than the width
specified for the column by left-padding them with spaces.
The display width does not constrain the
range of values that can be stored in the column, nor the number
of digits that are displayed for values having a width exceeding
that specified for the column. For example, a column specified as
SMALLINT(3) has the usual
SMALLINT range of -32768 to
32767, and values outside the range allowed by
three characters are displayed using more than three characters.
When used in conjunction with the optional extension attribute
ZEROFILL, the default padding of spaces is
replaced with zeros. For example, for a column declared as
INT(5) ZEROFILL, a value of
4 is retrieved as 00004.
Note that if you store larger values than the display width in an
integer column, you may experience problems when MySQL generates
temporary tables for some complicated joins, because in these
cases MySQL assumes that the data fits into the original column
width.
Note: The
ZEROFILL attribute is stripped when a column is
involved in expressions or UNION queries.
All integer types can have an optional (non-standard) attribute
UNSIGNED. Unsigned values can be used when you
want to allow only non-negative numbers in a column and you need a
larger upper numeric range for the column. For example, if an
INT column is UNSIGNED, the
size of the column's range is the same but its endpoints shift
from -2147483648 and
2147483647 up to 0 and
4294967295.
Floating-point and fixed-point types also can be
UNSIGNED. As with integer types, this attribute
prevents negative values from being stored in the column. However,
unlike the integer types, the upper range of column values remains
the same.
If you specify ZEROFILL for a numeric column,
MySQL automatically adds the UNSIGNED attribute
to the column.
For floating-point data types, MySQL uses four bytes for single-precision values and eight bytes for double-precision values.
The FLOAT and DOUBLE data
types are used to represent approximate numeric data values. For
FLOAT the SQL standard allows an optional
specification of the precision (but not the range of the exponent)
in bits following the keyword FLOAT in
parentheses. MySQL also supports this optional precision
specification, but the precision value is used only to determine
storage size. A precision from 0 to 23 results in a four-byte
single-precision FLOAT column. A precision from
24 to 53 results in an eight-byte double-precision
DOUBLE column.
MySQL allows a non-standard syntax:
FLOAT(
or
M,D)REAL(
or M,D)DOUBLE
PRECISION(.
Here,
“M,D)(”
means than values can be stored with up to
M,D)M digits in total, of which
D digits may be after the decimal
point. For example, a column defined as
FLOAT(7,4) will look like
-999.9999 when displayed. MySQL performs
rounding when storing values, so if you insert
999.00009 into a FLOAT(7,4)
column, the approximate result is 999.0001.
MySQL treats DOUBLE as a synonym for
DOUBLE PRECISION (a non-standard extension).
MySQL also treats REAL as a synonym for
DOUBLE PRECISION (a non-standard variation),
unless the REAL_AS_FLOAT SQL mode is enabled.
For maximum portability, code requiring storage of approximate
numeric data values should use FLOAT or
DOUBLE PRECISION with no specification of
precision or number of digits.
The DECIMAL and NUMERIC data
types are used to store exact numeric data values. In MySQL,
NUMERIC is implemented as
DECIMAL. These types are used to store values
for which it is important to preserve exact precision, for example
with monetary data.
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 Chapter 21, 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 9.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 the mode is set to TRADITIONAL,
MySQL rejects a value that is out of range with an error, and the
insert fails, in accordance with the SQL standard.
In non-strict 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.
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.2.6, “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 11.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.2.6, “SQL Modes”.) You can get MySQL to accept
certain dates, such as '1999-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
'1999-00-00' or
'1999-01-00'. If you store dates such as these,
you should not expect to get correct results for functions such as
DATE_SUB() or DATE_ADD that
require complete dates. (If you do not want
to allow zero in dates, you can use the
NO_ZERO_IN_DATE SQL mode).
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
is more convenient (and uses less space in data and index) 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.
| Data Type | “Zero” Value |
DATETIME | '0000-00-00 00:00:00' |
DATE | '0000-00-00' |
TIMESTAMP | '0000-00-00 00:00:00' |
TIME | '00:00:00' |
YEAR | 0000 |
The “zero” values are special, but you can store
or refer to them explicitly using the values shown in the
table. You can also do this using the values
'0' or 0, which are
easier to write.
“Zero” date or time values used through MyODBC
are converted automatically to NULL in
MyODBC 2.50.12 and above, because ODBC cannot handle such
values.
The DATETIME, DATE, and
TIMESTAMP types are related. This section
describes their characteristics, how they are similar, and how
they differ.
The DATETIME type is used when you need
values that contain both date and time information. MySQL
retrieves and displays DATETIME values in
'YYYY-MM-DD HH:MM:SS' format. The supported
range is '1000-01-01 00:00:00' to
'9999-12-31 23:59:59'.
The DATE type is used when you need only a
date value, without a time part. MySQL retrieves and displays
DATE values in
'YYYY-MM-DD' format. The supported range is
'1000-01-01' to
'9999-12-31'.
For the DATETIME and DATE
range descriptions, “supported” means that although
earlier values might work, there is no guarantee.
The TIMESTAMP data type has varying
properties, depending on the MySQL version 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,
'19970523091528' and
'970523091528' are interpreted as
'1997-05-23 09:15:28', but
'971122129015' is illegal (it has a
nonsensical minute part) and becomes '0000-00-00
00:00:00'.
As a string with no delimiters in either
'YYYYMMDD' or 'YYMMDD'
format, provided that the string makes sense as a date. For
example, '19970523' and
'970523' are interpreted as
'1997-05-23', but
'971332' is illegal (it has nonsensical
month and day parts) and becomes
'0000-00-00'.
As a number in either YYYYMMDDHHMMSS or
YYMMDDHHMMSS format, provided that the
number makes sense as a date. For example,
19830905132800 and
830905132800 are interpreted as
'1983-09-05 13:28:00'.
As a number in either YYYYMMDD or
YYMMDD format, provided that the number
makes sense as a date. For example,
19830905 and 830905
are interpreted as '1983-09-05'.
As the result of a function that returns a value that is
acceptable in a DATETIME,
DATE, or TIMESTAMP
context, such as NOW() or
CURRENT_DATE.
A microseconds part is allowable in temporal values in some
contexts, such as in literal values, and in the arguments to or
return values from some temporal functions. Microseconds are
specified as a trailing .uuuuuu part in the
value. Example:
mysql> SELECT MICROSECOND('2010-12-10 14:12:09.019473');
+-------------------------------------------+
| MICROSECOND('2010-12-10 14:12:09.019473') |
+-------------------------------------------+
| 19473 |
+-------------------------------------------+
However, microseconds cannot be stored into a column of any temporal data type. Any microseconds part is discarded.
As of MySQL 5.0.8, conversion of DATETIME
values to numeric form (for example, by adding
+0) results in a double value with a
microseconds part of .000000:
mysql> SELECT NOW(), NOW()+0;
+---------------------+-----------------------+
| NOW() | NOW()+0 |
+---------------------+-----------------------+
| 2007-04-23 14:21:52 | 20070423142152.000000 |
+---------------------+-----------------------+
Before MySQL 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 non-delimited strings are interpreted using
their length as given. If the string is 8 or 14 characters long,
the year is assumed to be given by the first 4 characters.
Otherwise, the year is assumed to be given by the first 2
characters. The string is interpreted from left to right to find
year, month, day, hour, minute, and second values, for as many
parts as are present in the string. This means you should not
use strings that have fewer than 6 characters. For example, if
you specify '9903', thinking that represents
March, 1999, MySQL inserts a “zero” date 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 or later than
2038. This means that a date such as
'1968-01-01', while legal as a
DATETIME or DATE
value, is not valid as a TIMESTAMP value
and is converted to 0.
Be aware of certain pitfalls when specifying date values:
The relaxed format allowed for values specified as strings
can be deceiving. For example, a value such as
'10:11:12' might look like a time value
because of the ‘:’ delimiter,
but if used in a date context is interpreted as the year
'2010-11-12'. The value
'10:45:15' is converted to
'0000-00-00' because
'45' is not a legal month.
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.2.6, “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.
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.
Note: In older versions of
MySQL (prior to 4.1), the properties of the
TIMESTAMP data type differed significantly
in many ways from what is described in this section. If you
need to convert older TIMESTAMP data to
work with MySQL 5.0, be sure to 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.
The MySQL server can be also be run with the
MAXDB SQL mode enabled. When the server
runs with this mode enabled, TIMESTAMP is
identical with DATETIME. That is, if this
mode is enabled at the time that a table is created,
TIMESTAMP columns are created as
DATETIME columns. As a result, such columns
use DATETIME display format, have the same
range of values, and there is no automatic initialization or
updating to the current date and time.
To enable MAXDB mode, set the server SQL
mode to MAXDB at startup using the
--sql-mode=MAXDB server option or by setting
the global sql_mode variable at runtime:
mysql> SET GLOBAL sql_mode=MAXDB;
A client can cause the server to run in
MAXDB mode for its own connection as
follows:
mysql> SET SESSION sql_mode=MAXDB;
Note that the information in the following discussion applies
to TIMESTAMP columns only for tables not
created with MAXDB mode enabled, because
such columns are created as DATETIME
columns.
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'.
You have considerable flexibility in determining when
automatic TIMESTAMP initialization and
updating occur and which column should have those behaviors:
For one TIMESTAMP column in a table,
you can assign the current timestamp as the default value
and the auto-update value. It is possible to have the
current timestamp be the default value for initializing
the column, for the auto-update value, or both. It is not
possible to have the current timestamp be the default
value for one column and the auto-update value for another
column.
You can specify which TIMESTAMP column
to automatically initialize or update to the current date
and time. This need not be the first
TIMESTAMP column.
The following rules govern initialization and updating of
TIMESTAMP columns:
If a DEFAULT value is specified for the
first TIMESTAMP column in a table, it
is not ignored. The default can be
CURRENT_TIMESTAMP or a constant date
and time value.
DEFAULT NULL is the same as
DEFAULT CURRENT_TIMESTAMP for the
first TIMESTAMP
column. For any other TIMESTAMP column,
DEFAULT NULL is treated as
DEFAULT 0.
Any single TIMESTAMP column in a table
can be used as the one that is initialized to the current
timestamp or updated automatically.
In a CREATE TABLE statement, the first
TIMESTAMP column can be declared in any
of the following ways:
With both DEFAULT CURRENT_TIMESTAMP
and ON UPDATE CURRENT_TIMESTAMP
clauses, the column has the current timestamp for its
default value, and is automatically updated.
With neither DEFAULT nor
ON UPDATE clauses, it is the same
as DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP.
With a DEFAULT CURRENT_TIMESTAMP
clause and no ON UPDATE clause, the
column has the current timestamp for its default value
but is not automatically updated.
With no DEFAULT clause and with an
ON UPDATE CURRENT_TIMESTAMP clause,
the column has a default of 0 and is automatically
updated.
With a constant DEFAULT value, the
column has the given default. If the column has an
ON UPDATE CURRENT_TIMESTAMP clause,
it is automatically updated, otherwise not.
In other words, you can use the current timestamp for both
the initial value and the auto-update value, or either
one, or neither. (For example, you can specify ON
UPDATE to enable auto-update without also having
the column auto-initialized.)
CURRENT_TIMESTAMP or any of its
synonyms (CURRENT_TIMESTAMP(),
NOW(), LOCALTIME,
LOCALTIME(),
LOCALTIMESTAMP, or
LOCALTIMESTAMP()) can be used in the
DEFAULT and ON
UPDATE clauses. They all mean “the current
timestamp.” (UTC_TIMESTAMP is
not allowed. Its range of values does not align with those
of the TIMESTAMP column anyway unless
the current time zone is UTC.)
The order of the DEFAULT and
ON UPDATE attributes does not matter.
If both DEFAULT and ON
UPDATE are specified for a
TIMESTAMP column, either can precede
the other. For example, these statements are equivalent:
CREATE TABLE t (ts TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DEFAULT CURRENT_TIMESTAMP);
To specify automatic default or updating for a
TIMESTAMP column other than the first
one, you must suppress the automatic initialization and
update behaviors for the first
TIMESTAMP column by explicitly
assigning it a constant DEFAULT value
(for example, DEFAULT 0 or
DEFAULT '2003-01-01 00:00:00'). Then,
for the other TIMESTAMP column, the
rules are the same as for the first
TIMESTAMP column, except that if you
omit both of the DEFAULT and
ON UPDATE clauses, no automatic
initialization or updating occurs.
Example. These statements are equivalent:
CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DEFAULT CURRENT_TIMESTAMP);
You can set the current time zone on a per-connection basis,
as described in Section 5.10.8, “MySQL Server Time Zone Support”.
TIMESTAMP values are stored in UTC, being
converted from the current time zone for storage, and
converted back to the current time zone upon retrieval. As
long as the time zone setting remains constant, you get back
the same value you store. If you store a
TIMESTAMP value, and then change the time
zone and retrieve the value, the retrieved value is different
than the value you stored. This occurs because the same time
zone was not used for conversion in both directions. The
current time zone is available as the value of the
time_zone system variable.
You can include the NULL attribute in the
definition of a TIMESTAMP column to allow
the column to contain NULL values. For
example:
CREATE TABLE t ( ts1 TIMESTAMP NULL DEFAULT NULL, ts2 TIMESTAMP NULL DEFAULT 0, ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP );
If the NULL attribute is not specified,
setting the column to NULL sets it to the
current timestamp. Note that a TIMESTAMP
column which allows NULL values 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 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);
Note that TIMESTAMP columns are
NOT NULL by default.
MySQL retrieves and displays TIME values in
'HH:MM:SS' format (or
'HHH:MM:SS' format for large hours values).
TIME values may range from
'-838:59:59' to
'838:59:59'. The hours part may be so large
because the TIME type can be used not only to
represent a time of day (which must be less than 24 hours), but
also elapsed time or a time interval between two events (which
may be much greater than 24 hours, or even negative).
You can specify TIME values in a variety of
formats:
As a string in 'D HH:MM:SS.fraction'
format. You can also use one of the following
“relaxed” syntaxes:
'HH:MM:SS.fraction',
'HH:MM:SS', 'HH:MM',
'D HH:MM:SS', 'D
HH:MM', 'D HH', or
'SS'. Here D
represents days and can have a value from 0 to 34. Note that
MySQL does not store the fraction part.
As a string with no delimiters in
'HHMMSS' format, provided that it makes
sense as a time. For example, '101112' is
understood as '10:11:12', but
'109712' is illegal (it has a nonsensical
minute part) and becomes '00:00:00'.
As a number in HHMMSS format, provided
that it makes sense as a time. For example,
101112 is understood as
'10:11:12'. The following alternative
formats are also understood: SS,
MMSS, HHMMSS,
HHMMSS.fraction. Note that MySQL does not
store the 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 11.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.2.6, “SQL Modes”.
The YEAR type is a one-byte type used for
representing years.
MySQL retrieves and displays YEAR values in
YYYY format. The range is
1901 to 2155.
You can specify YEAR values in a variety of
formats:
As a four-digit string in the range
'1901' to '2155'.
As a four-digit number in the range 1901
to 2155.
As a two-digit string in the range '00'
to '99'. Values in the ranges
'00' to '69' and
'70' to '99' are
converted to YEAR values in the ranges
2000 to 2069 and
1970 to 1999.
As a two-digit number in the range 1 to
99. Values in the ranges
1 to 69 and
70 to 99 are converted
to YEAR values in the ranges
2001 to 2069 and
1970 to 1999. Note
that the range for two-digit numbers is slightly different
from the range for two-digit strings, because you cannot
specify zero directly as a number and have it be interpreted
as 2000. You must specify it as a string
'0' or '00' or it is
interpreted as 0000.
As the result of a function that returns a value that is
acceptable in a YEAR context, such as
NOW().
Illegal YEAR values are converted to
0000.