Table of Contents
MySQL supports a number of data types in several categories: numeric types, date and time types, and string (character) types. This chapter first gives an overview of these data types, and then provides a more detailed description of the properties of the types in each category, and a summary of the data type storage requirements. The initial overview is intentionally brief. The more detailed descriptions later in the chapter should be consulted for additional information about particular data types, such as the allowable formats in which you can specify values.
MySQL 4.1 and up also supports extensions for handing spatial data. Chapter 16, Spatial Extensions, provides information about these data types.
Several of the data type descriptions use these conventions:
MM is the total
number of digits. For string types, M
is the maximum length. The maximum allowable value of
M depends on the data type.
DM–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. The maximum legal display width is 255. Display width is
unrelated to the storage size or range of values a type can
contain, as described in Section 11.2, “Numeric Types”.
If you specify ZEROFILL for a numeric column,
MySQL automatically adds the UNSIGNED
attribute to the column.
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.8, “Cast Functions and Operators”.
In versions of MySQL up to and lincluding 4.1,
BIT is a synonym for
TINYINT(1).
TINYINT[(
M)] [UNSIGNED]
[ZEROFILL]
A very small integer. The signed range is
-128 to 127. The
unsigned range is 0 to
255.
These types are synonyms for TINYINT(1).
The BOOLEAN synonym was added in MySQL
4.1.0. A value of zero is considered false. Non-zero values
are considered true.
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.
Some things you should be aware of with respect to
BIGINT columns:
All arithmetic is done using signed
BIGINT or DOUBLE
values, so you should not use unsigned big integers
larger than 9223372036854775807 (63
bits) except with bit functions! If you do that, some of
the last digits in the result may be wrong because of
rounding errors when converting a
BIGINT value to a
DOUBLE.
MySQL 4.0 can handle BIGINT in the
following cases:
When using integers to store large unsigned values
in a BIGINT column.
In
MIN(
or
col_name)MAX(,
where col_name)col_name refers to
a BIGINT column.
When using operators (+,
-, *, and so
on) where both operands are integers.
You can always store an exact integer value in a
BIGINT column by storing it using a
string. In this case, MySQL performs a string-to-number
conversion that involves no intermediate
double-precision representation.
The -, +, and
* operators use
BIGINT arithmetic when both operands
are integer values. This means that if you multiply two
big integers (or results from functions that return
integers), you may get unexpected results when the
result is larger than
9223372036854775807.
FLOAT[(
M,D)]
[UNSIGNED] [ZEROFILL]
A small (single-precision) floating-point number. Allowable
values are -3.402823466E+38 to
-1.175494351E-38, 0,
and 1.175494351E-38 to
3.402823466E+38. These are the
theoretical limits, based on the IEEE standard. The actual
range might be slightly smaller depending on your hardware
or operating system.
M is the total number of decimal
digits and D is the number of
digits following the decimal point. If
M and
D are omitted, values are stored
to the limits allowed by the hardware. A single-precision
floating-point number is accurate to approximately 7 decimal
places.
UNSIGNED, if specified, disallows
negative values.
Using FLOAT might give you some
unexpected problems because all calculations in MySQL are
done with double precision. See
Section A.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 decimal
digits and D is the number of
digits following the decimal point. If
M and
D are omitted, values are stored
to the limits allowed by the hardware. A double-precision
floating-point number is accurate to approximately 15
decimal places.
UNSIGNED, if specified, disallows
negative values.
DOUBLE
PRECISION[(,
M,D)]
[UNSIGNED] [ZEROFILL]REAL[(
M,D)]
[UNSIGNED] [ZEROFILL]
These types are synonyms for DOUBLE.
Exception: If the REAL_AS_FLOAT SQL mode
is enabled, REAL is a synonym for
FLOAT rather than
DOUBLE.
FLOAT(
p) [UNSIGNED]
[ZEROFILL]
A floating-point number. p
represents the precision in bits, but MySQL uses this value
only to determine whether to use FLOAT or
DOUBLE for the resulting data type. If
p is from 0 to 24, the data type
becomes FLOAT with no
M or D
values. If p is from 25 to 53,
the data type becomes DOUBLE with no
M or D
values. The range of the resulting column is the same as for
the single-precision FLOAT or
double-precision DOUBLE data types
described earlier in this section.
As of MySQL 3.23, this data type holds true floating-point
values. In earlier MySQL versions,
FLOAT(
always has two decimals.
p)
DECIMAL[(
M[,D])]
[UNSIGNED] [ZEROFILL]
An unpacked fixed-point number. Behaves like a
CHAR column; “unpacked”
means the number is stored as a string, using one character
for each digit of the value. M is
the total number of digits and D
is the number of digits after the decimal point. The decimal
point and (for negative numbers) the
‘-’ sign are not counted in
M, although space for them is
reserved. If D is 0, values have
no decimal point or fractional part. The maximum range of
DECIMAL values is the same as for
DOUBLE, but the actual range for a given
DECIMAL column may be constrained by the
choice of M and
D. If
D is omitted, the default is 0.
If M is omitted, the default is
10.
UNSIGNED, if specified, disallows
negative values.
Note: Before MySQL 3.23,
the value of M must be large
enough to include the space needed for the sign and the
decimal point characters.
DEC[(,
M[,D])]
[UNSIGNED] [ZEROFILL]NUMERIC[(,
M[,D])]
[UNSIGNED] [ZEROFILL]FIXED[(
M[,D])]
[UNSIGNED] [ZEROFILL]
These types are synonyms for DECIMAL. The
FIXED synonym was added in MySQL 4.1.0
for compatibility with other database systems.
A summary of the temporal data types follows. For additional information, see Section 11.3, “Date and Time Types”. Storage requirements are given in Section 11.5, “Data Type Storage Requirements”.
For the DATETIME and DATE
range descriptions, “supported” means that although
earlier values might work, there is no guarantee.
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 date. The supported range is
'1000-01-01' to
'9999-12-31'. MySQL displays
DATE values in
'YYYY-MM-DD' format, but allows you to
assign 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 you to assign values
to DATETIME columns using either strings
or numbers.
A timestamp. The range is '1970-01-01
00:00:00' to partway through the year
2037.
A TIMESTAMP column is useful for
recording the date and time of an INSERT
or UPDATE operation. By default, the
first TIMESTAMP column in a table is
automatically set to the date and time of the most recent
operation if you do not assign it a value yourself. You can
also set any TIMESTAMP column to the
current date and time by assigning it a
NULL value. Variations on automatic
initialization and update properties are described in
Section 11.3.1.2, “TIMESTAMP Properties as of MySQL 4.1”.
In MySQL 4.1, TIMESTAMP is returned as a
string with the format 'YYYY-MM-DD
HH:MM:SS'. Display widths (used as described in
the following paragraphs) are no longer supported; the
display width is fixed at 19 characters. To obtain the value
as a number, you should add +0 to the
timestamp column.
In MySQL 4.0 and earlier, TIMESTAMP
values are displayed in YYYYMMDDHHMMSS,
YYMMDDHHMMSS,
YYYYMMDD, or YYMMDD
format, depending on whether M is
14 (or missing), 12, 8, or 6, but allows you to assign
values to TIMESTAMP columns using either
strings or numbers. The M
argument affects only how a TIMESTAMP
column is displayed, not storage. Its values always are
stored using four bytes each. From MySQL 4.0.12, the
--new option can be used to make the server
behave as in MySQL 4.1.
Note that
TIMESTAMP(
columns where M)M is 8 or 14 are
reported to be numbers, whereas other
TIMESTAMP(
columns are reported to be strings. This is just to ensure
that you can reliably dump and restore the table with these
types.
M)
Note: The behavior of
TIMESTAMP columns changed considerably in
MySQL 4.1. For complete information on the differences with
regard to this data type in MySQL 4.1 and later versions (as
opposed to MySQL 4.0 and earlier versions), be sure to see
Section 11.3.1.1, “TIMESTAMP Properties Prior to MySQL 4.1”, and
Section 11.3.1.2, “TIMESTAMP Properties as of MySQL 4.1”.
A time. The range is '-838:59:59' to
'838:59:59'. MySQL displays
TIME values in
'HH:MM:SS' format, but allows you to
assign values to TIME columns using
either strings or numbers.
A year in two-digit or four-digit format. The default is
four-digit format. In four-digit format, the allowable
values are 1901 to
2155, and 0000. In
two-digit format, the allowable values are
70 to 69, representing
years from 1970 to 2069. MySQL displays
YEAR values in YYYY
format, but allows you to assign values to
YEAR columns using either strings or
numbers. The YEAR type is unavailable
prior to MySQL 3.22.
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 previous versions of MySQL (prior to 4.1):
Column definitions for many string data types can include a
CHARACTER SET attribute to specify the
character set. (CHARSET is a synonym for
CHARACTER SET.) The
COLLATE attribute specifies a collation
for the the character set. These attributes apply to
CHAR, VARCHAR, the
TEXT types, ENUM, and
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.
Note: As of MySQL 4.1, MySQL interprets length specifications in character column definitions in character units. (Previously, MySQL interpreted lengths in bytes.)
For CHAR, VARCHAR, and
the TEXT types, the
BINARY attribute causes the column to be
assigned the binary collation of the column character set.
(Previously, BINARY caused
CHAR and VARCHAR to be
treated as binary strings and was disallowed for the
TEXT types.)
Character column sorting and comparison are based on the
character set assigned to the column. (Previously sorting
and comparison were based on the collation of the server
character set.) For CHAR and
VARCHAR columns, you can declare the
column with a binary collation or the
BINARY attribute to cause sorting and
comparison to use the underlying character code values
rather then a lexical ordering.
Chapter 10, Character Set Support, provides additional information about use of character sets in MySQL 4.1 and up.
[NATIONAL] CHAR(
M)
[BINARY | ASCII | UNICODE]
A fixed-length string that is always right-padded with
spaces to the specified length when stored.
M represents the column length.
The range of M is 0 to 255
characters (1 to 255 prior to MySQL 3.23).
Note: Trailing spaces are
removed when CHAR values are retrieved.
In MySQL 4.1, a CHAR column with a length
specification greater than 255 is converted to the smallest
TEXT type that can hold values of the
given length. For example, CHAR(500) is
converted to TEXT, and
CHAR(200000) is converted to
MEDIUMTEXT. This is a compatibility
feature. However, this conversion causes the column to
become a variable-length column, and also affects
trailing-space removal.
CHAR is shorthand for
CHARACTER. NATIONAL
CHAR (or its equivalent short form,
NCHAR) is the standard SQL way to define
that a CHAR column should use some
predefined character set. MySQL 4.1 and up
utf8 as this predefined character set.
Section 10.3.6, “National Character Set”.
As of MySQL 4.1.2, the BINARY attribute
is shorthand for specifying the binary collation of the
column character set. In this case, sorting and comparison
are based on numeric character values. Before 4.1.2,
BINARY attribute causes the column to be
treated as a binary string. Sorting and comparison are based
on numeric byte values.
From MySQL 4.1.2 on, the CHAR BYTE data
type is an alias for the BINARY type.
This is a compatibility feature.
From MySQL 4.1.0 on, the ASCII attribute
is shorthand for CHARACTER SET latin1.
From MySQL 4.1.1 on, the UNICODE
attribute is shorthand for CHARACTER SET
ucs2.
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
CHAR(0) column that is not defined as
NOT NULL occupies only one bit and can
take only the values NULL and
'' (the empty string).
CHAR
This type is a synonym for CHAR(1).
[NATIONAL] VARCHAR(
M)
[BINARY]
A variable-length string. M
represents the maximum column length. The range of
M is 1 to 255 before MySQL 4.0.2,
and 0 to 255 as of MySQL 4.0.2.
Note: Trailing spaces are
removed when VARCHAR values are stored.
This differs from the standard SQL specification.
In MySQL 4.1, a VARCHAR column with a
length specification greater than 255 is converted to the
smallest TEXT type that can hold values
of the given length. For example,
VARCHAR(500) is converted to
TEXT, and
VARCHAR(200000) is converted to
MEDIUMTEXT. This is a compatibility
feature. However, this conversion affects trailing-space
removal.
VARCHAR is shorthand for
CHARACTER VARYING.
As of MySQL 4.1.2, the BINARY attribute
is shorthand for specifying the binary collation of the
column character set. In this case, sorting and comparison
are based on numeric character values. Before 4.1.2,
BINARY attribute causes the column to be
treated as a binary string. Sorting and comparison are based
on numeric byte values.
The BINARY type is similar to the
CHAR type, but stores binary byte strings
rather than non-binary character strings.
This type was added in MySQL 4.1.2.
The VARBINARY type is similar to the
VARCHAR type, but stores binary byte
strings rather than non-binary character strings.
This type was added in MySQL 4.1.2.
A BLOB column with a maximum length of
255 (28 – 1) bytes.
A TEXT column with a maximum length of
255 (28 – 1) characters.
A BLOB column with a maximum length of
65,535 (216 – 1) bytes.
Beginning with MySQL 4.1, an optional length
M can be given for this type.
MySQL creates the column as the smallest
BLOB type large enough to hold values
M bytes long.
A TEXT column with a maximum length of
65,535 (216 – 1)
characters.
Beginning with MySQL 4.1, an optional length
M can be given for this type.
MySQL creates the column as the smallest
TEXT type large enough to hold values
M characters long.
A BLOB column with a maximum length of
16,777,215 (224 – 1) bytes.
A TEXT column with a maximum length of
16,777,215 (224 – 1)
characters.
A BLOB column with a maximum length of
4,294,967,295 or 4GB (232 –
1) bytes. Up to MySQL 3.23, the client/server protocol and
MyISAM tables had a limit of 16MB per
communication packet or table row. From MySQL 4.0, the
maximum allowed length of LONGBLOB
columns depends on the configured maximum packet size in the
client/server protocol and available memory.
A TEXT column with a maximum length of
4,294,967,295 or 4GB (232 –
1) characters. Up to MySQL 3.23, the client/server protocol
and MyISAM tables had a limit of 16MB per
communication packet or table row. From MySQL 4.0, the
maximum allowed length of LONGTEXT
columns depends on the configured maximum packet size in the
client/server protocol and available memory.
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.
A set. A string object that can have zero or more values,
each of which must be chosen from the list of values
',
value1'',
value2'... A SET column can
have a maximum of 64 members. SET values
are represented internally as integers.
The DEFAULT
clause in a data type specification indicates a default value
for a column. With one exception, the default value must be a
constant; it cannot be a function or an expression. This means,
for example, that you cannot set the default for a date column
to be the value of a function such as valueNOW()
or CURRENT_DATE. The exception is that you
can specify CURRENT_TIMESTAMP as the default
for a TIMESTAMP column as of MySQL 4.1.2. See
Section 11.3.1.2, “TIMESTAMP Properties as of MySQL 4.1”.
If a column definition includes no explicit
DEFAULT value, MySQL determines the default
value as follows:
If the column can take NULL as a value, the
column is defined with an explicit DEFAULT
NULL clause.
If the column cannot take NULL as the value,
MySQL defines the column with an explicit
DEFAULT clause, using the implicit default
value for the column data type. Implicit defaults are defined as
follows:
For numeric types other than integer types declared with the
AUTO_INCREMENT attribute, the default is
0. For an
AUTO_INCREMENT column, the default value
is the next value in the sequence.
For date and time types other than
TIMESTAMP, the default is the appropriate
“zero” value for the type. For the first
TIMESTAMP column in a table, the default
value is the current date and time. See
Section 11.3, “Date and Time Types”.
For string types other than ENUM, the
default value is the empty string. For
ENUM, the default is the first
enumeration value.
BLOB and TEXT columns
cannot be assigned a default value.
For a given table, you can use the SHOW CREATE
TABLE statement to see which columns have an explicit
DEFAULT clause.
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 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 an integer value in parentheses following the
base keyword for the type (for example,
INT(4)). This optional display width
specification is used to left-pad the display of values having a
width less than the width specified for the column.
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.
When used in conjunction with the optional extension attribute
ZEROFILL, the default padding of spaces is
replaced with zeroes. 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.
All integer types can have an optional (non-standard) attribute
UNSIGNED. Unsigned values can be used when you
want to allow only non-negative numbers in a column and you need a
larger upper numeric range for the column. For example, if an
INT column is UNSIGNED, the
size of the column's range is the same but its endpoints shift
from -2147483648 and
2147483647 up to 0 and
4294967295.
As of MySQL 4.0.2, floating-point and fixed-point types also can
be UNSIGNED. As with integer types, this
attribute prevents negative values from being stored in the
column. However, unlike the integer types, the upper range of
column values remains the same.
If you specify ZEROFILL for a numeric column,
MySQL automatically adds the UNSIGNED attribute
to the column.
For floating-point data types, MySQL uses four bytes for single-precision values and eight bytes for double-precision values.
The FLOAT and DOUBLE data
types are used to represent approximate numeric data values. For
FLOAT the SQL standard allows an optional
specification of the precision (but not the range of the exponent)
in bits following the keyword FLOAT in
parentheses. MySQL also supports this optional precision
specification, but the precision value is used only to determine
storage size. A precision from 0 to 23 results in a four-byte
single-precision FLOAT column. A precision from
24 to 53 results in an eight-byte double-precision
DOUBLE column.
MySQL allows a non-standard syntax:
FLOAT(
or
M,D)REAL(
or M,D)DOUBLE
PRECISION(.
Here,
“M,D)(”
means than values are displayed with up to
M,D)M digits in total, of which
D digits may be after the decimal
point. For example, a column defined as
FLOAT(7,4) will look like
-999.9999 when displayed. MySQL performs
rounding when storing values, so if you insert
999.00009 into a FLOAT(7,4)
column, the approximate result is 999.0001.
MySQL treats DOUBLE as a synonym for
DOUBLE PRECISION (a non-standard extension).
MySQL also treats REAL as a synonym for
DOUBLE PRECISION (a non-standard variation),
unless the REAL_AS_FLOAT SQL mode is enabled.
For maximum portability, code requiring storage of approximate
numeric data values should use FLOAT or
DOUBLE PRECISION with no specification of
precision or number of digits.
The DECIMAL and NUMERIC data
types are used to store exact numeric data values. In MySQL,
NUMERIC is implemented as
DECIMAL. These types are used to store values
for which it is important to preserve exact precision, for example
with monetary data.
Through version 4.1, MySQL stores DECIMAL and
NUMERIC values as strings, rather than in
binary format. One character is used for each digit of the value,
the decimal point (if the scale is greater than 0), and the
‘-’ sign (for negative numbers).
When declaring a DECIMAL or
NUMERIC column, the precision and scale can be
(and usually is) specified; for example:
salary DECIMAL(5,2)
In this example, 5 is the precision and
2 is the scale. The precision represents the
number of significant digits that are stored for values, and the
scale represents the number of digits that can be stored following
the decimal point. If the scale is 0, DECIMAL
and NUMERIC values contain no decimal point or
fractional part.
Standard SQL requires that the salary column be
able to store any value with five digits and two decimals. In this
case, therefore, the range of values that can be stored in the
salary column is from
-999.99 to 999.99. In
versions up to and including 4.1, MySQL varies from this limit in
two ways due to the use of string format for value storage:
On the positive end of the range, the column actually can
store numbers up to 9999.99. For positive
numbers, MySQL uses the byte reserved for the sign to extend
the upper end of the range.
DECIMAL columns in MySQL before 3.23 are
stored differently and cannot represent all the values
required by standard SQL. This is because for a type of
DECIMAL(,
the value of M,D)M includes the bytes
for the sign and the decimal point. The range of the
salary column before MySQL 3.23 would be
-9.99 to 99.99.
In standard SQL, the syntax
DECIMAL( is
equivalent to
M)DECIMAL(.
Similarly, the syntax M,0)DECIMAL is equivalent to
DECIMAL(, where
the implementation is allowed to decide the value of
M,0)M. As of MySQL 3.23.6, both of these
variant forms of the DECIMAL and
NUMERIC data types are supported. The default
value of M is 10. Before 3.23.6,
M and D both
must be specified explicitly.
The maximum range of DECIMAL and
NUMERIC values is the same as for
DOUBLE, but the actual range for a given
DECIMAL or NUMERIC column
can be constrained by the precision or scale for a given column.
When such a column is assigned a value with more digits following
the decimal point than are allowed by the specified scale, the
value is converted to that scale. (The precise behavior is
operating system-specific, but generally the effect is truncation
to the allowable number of digits.)
When asked to store a value in a numeric column that is outside the data type's allowable range, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead.
For example, when an out-of-range value is assigned to an integer
column, MySQL stores the value representing the corresponding
endpoint of the column data type range. If you store 256 into a
TINYINT or TINYINT UNSIGNED
column, MySQL stores 127 or 255, respectively. When a
floating-point or fixed-point column is assigned a value that
exceeds the range implied by the specified (or default) precision
and scale, MySQL stores the value representing the corresponding
endpoint of that range.
Conversions that occur due to clipping are reported as
“warnings” for ALTER TABLE,
LOAD DATA INFILE, UPDATE,
and multiple-row INSERT statements.
The date and time types for representing temporal values are
DATETIME, DATE,
TIMESTAMP, TIME, and
YEAR. Each temporal type has a range of legal
values, as well as a “zero” value that is used when
you specify an illegal value that MySQL cannot represent. The
TIMESTAMP type has special automatic updating
behavior, described later on. For temporary type storage
requirements, see Section 11.5, “Data Type Storage Requirements”.
MySQL version through 4.1 accept certain “illegal”
values for dates, such as '1999-11-31'. This is
useful when you want to store a possibly incorrect value specified
by a user (for example, in a web form) in the database for future
processing. MySQL verifies only that the month is in the range
from 0 to 12 and that the day is in the range from 0 to 31. These
ranges are defined to include zero because MySQL allows you to
store dates where the day or month and day are zero in a
DATE or DATETIME column.
This is extremely useful for applications that need to store a
birthdate for which you do not know the exact date. In this case,
you simply store the date as '1999-00-00' or
'1999-01-00'. If you store dates such as these,
you should not expect to get correct results for functions such as
DATE_SUB() or DATE_ADD that
require complete dates.
MySQL also allows you to store '0000-00-00' as
a “dummy date.” This is in some cases more
convenient, and uses less data and index space, than storing
NULL values.
Here are some general considerations to keep in mind when working with date and time types:
MySQL retrieves values for a given date or time type in a standard output format, but it attempts to interpret a variety of formats for input values that you supply (for example, when you specify a value to be assigned to or compared to a date or time type). Only the formats described in the following sections are supported. It is expected that you supply legal values. Unpredictable results may occur if you use values in other formats.
Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using the following rules:
Year values in the range 00-69 are
converted to 2000-2069.
Year values in the range 70-99 are
converted to 1970-1999.
Although MySQL tries to interpret values in several formats,
dates always must be given in year-month-day order (for
example, '98-09-04'), rather than in the
month-day-year or day-month-year orders commonly used
elsewhere (for example, '09-04-98',
'04-09-98').
MySQL automatically converts a date or time type value to a number if the value is used in a numeric context and vice versa.
By default, when MySQL encounters a value for a date or time
type that is out of range or otherwise illegal for the type
(as described at the beginning of this section), it converts
the value to the “zero” value for that type. The
exception is that out-of-range TIME values
are clipped to the appropriate endpoint of the
TIME range.
| Data Type | “Zero” Value |
DATETIME | '0000-00-00 00:00:00' |
DATE | '0000-00-00' |
TIMESTAMP (4.1 and up) | '0000-00-00 00:00:00' |
TIMESTAMP (before 4.1) | 00000000000000 |
TIME | '00:00:00' |
YEAR | 0000 |
The “zero” values are special, but you can store
or refer to them explicitly using the values shown in the
table. You can also do this using the values
'0' or 0, which are
easier to write.
“Zero” date or time values used through MyODBC
are converted automatically to NULL in
MyODBC 2.50.12 and above, because ODBC cannot handle such
values.
The DATETIME, DATE, and
TIMESTAMP types are related. This section
describes their characteristics, how they are similar, and how
they differ.
The DATETIME type is used when you need
values that contain both date and time information. MySQL
retrieves and displays DATETIME values in
'YYYY-MM-DD HH:MM:SS' format. The supported
range is '1000-01-01 00:00:00' to
'9999-12-31 23:59:59'.
The DATE type is used when you need only a
date value, without a time part. MySQL retrieves and displays
DATE values in
'YYYY-MM-DD' format. The supported range is
'1000-01-01' to
'9999-12-31'.
For the DATETIME and DATE
range descriptions, “supported” means that although
earlier values might work, there is no guarantee.
The TIMESTAMP data type has varying
properties, depending on the MySQL version. These properties are
described later in this section.
You can specify DATETIME,
DATE, and TIMESTAMP values
using any of a common set of formats:
As a string in either 'YYYY-MM-DD
HH:MM:SS' or 'YY-MM-DD
HH:MM:SS' format. A “relaxed” syntax
is allowed: Any punctuation character may be used as the
delimiter between date parts or time parts. For example,
'98-12-31 11:30:45', '98.12.31
11+30+45', '98/12/31 11*30*45',
and '98@12@31 11^30^45' are equivalent.
As a string in either 'YYYY-MM-DD' or
'YY-MM-DD' format. A
“relaxed” syntax is allowed here, too. For
example, '98-12-31',
'98.12.31',
'98/12/31', and
'98@12@31' are equivalent.
As a string with no delimiters in either
'YYYYMMDDHHMMSS' or
'YYMMDDHHMMSS' format, provided that the
string makes sense as a date. For example,
'19970523091528' and
'970523091528' are interpreted as
'1997-05-23 09:15:28', but
'971122129015' is illegal (it has a
nonsensical minute part) and becomes '0000-00-00
00:00:00'.
As a string with no delimiters in either
'YYYYMMDD' or 'YYMMDD'
format, provided that the string makes sense as a date. For
example, '19970523' and
'970523' are interpreted as
'1997-05-23', but
'971332' is illegal (it has nonsensical
month and day parts) and becomes
'0000-00-00'.
As a number in either YYYYMMDDHHMMSS or
YYMMDDHHMMSS format, provided that the
number makes sense as a date. For example,
19830905132800 and
830905132800 are interpreted as
'1983-09-05 13:28:00'.
As a number in either YYYYMMDD or
YYMMDD format, provided that the number
makes sense as a date. For example,
19830905 and 830905
are interpreted as '1983-09-05'.
As the result of a function that returns a value that is
acceptable in a DATETIME,
DATE, or TIMESTAMP
context, such as NOW() or
CURRENT_DATE.
Illegal DATETIME, DATE, or
TIMESTAMP values are converted to the
“zero” value of the appropriate type
('0000-00-00 00:00:00',
'0000-00-00', or
00000000000000).
For values specified as strings that include date part
delimiters, it is not necessary to specify two digits for month
or day values that are less than 10.
'1979-6-9' is the same as
'1979-06-09'. Similarly, for values specified
as strings that include time part delimiters, it is not
necessary to specify two digits for hour, minute, or second
values that are less than 10.
'1979-10-30 1:2:3' is the same as
'1979-10-30 01:02:03'.
Values specified as numbers should be 6, 8, 12, or 14 digits
long. If a number is 8 or 14 digits long, it is assumed to be in
YYYYMMDD or YYYYMMDDHHMMSS
format and that the year is given by the first 4 digits. If the
number is 6 or 12 digits long, it is assumed to be in
YYMMDD or YYMMDDHHMMSS
format and that the year is given by the first 2 digits. Numbers
that are not one of these lengths are interpreted as though
padded with leading zeroes to the closest length.
Values specified as non-delimited strings are interpreted using
their length as given. If the string is 8 or 14 characters long,
the year is assumed to be given by the first 4 characters.
Otherwise, the year is assumed to be given by the first 2
characters. The string is interpreted from left to right to find
year, month, day, hour, minute, and second values, for as many
parts as are present in the string. This means you should not
use strings that have fewer than 6 characters. For example, if
you specify '9903', thinking that represents
March, 1999, MySQL inserts a “zero” date into your
table. This occurs because the year and month values are
99 and 03, but the day
part is completely missing, so the value is not a legal date.
However, as of MySQL 3.23, you can explicitly specify a value of
zero to represent missing month or day parts. For example, you
can use '990300' to insert the value
'1999-03-00'.
You can to some extent assign values of one date type to an object of a different date type. However, there may be some alteration of the value or loss of information:
If you assign a DATE value to a
DATETIME or TIMESTAMP
object, the time part of the resulting value is set to
'00:00:00' because the
DATE value contains no time information.
If you assign a DATETIME or
TIMESTAMP value to a
DATE object, the time part of the
resulting value is deleted because the
DATE type stores no time information.
Remember that although DATETIME,
DATE, and TIMESTAMP
values all can be specified using the same set of formats,
the types do not all have the same range of values. For
example, TIMESTAMP values cannot be
earlier than 1970 or later than
2037. This means that a date such as
'1968-01-01', while legal as a
DATETIME or DATE
value, is not valid as a TIMESTAMP value
and is converted to 0.
Be aware of certain pitfalls when specifying date values:
The relaxed format allowed for values specified as strings
can be deceiving. For example, a value such as
'10:11:12' might look like a time value
because of the ‘:’ delimiter,
but if used in a date context is interpreted as the year
'2010-11-12'. The value
'10:45:15' is converted to
'0000-00-00' because
'45' is not a legal month.
The MySQL server performs only basic checking on the
validity of a date: The ranges for year, month, and day are
1000 to 9999, 00 to 12, and 00 to 31, respectively. Any date
containing parts not within these ranges is subject to
conversion to '0000-00-00'. Please note
that this still allows you to store invalid dates such as
'2002-04-31'. To ensure that a date is
valid, perform a check in your application.
Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using the following rules:
Year values in the range 00-69 are
converted to 2000-2069.
Year values in the range 70-99 are
converted to 1970-1999.
The TIMESTAMP data type provides a type
that you can use to automatically mark
INSERT or UPDATE
operations with the current date and time. If you have
multiple TIMESTAMP columns in a table, only
the first one is updated automatically. (From MySQL 4.1.2 on,
you can specify which TIMESTAMP column
updates; see Section 11.3.1.2, “TIMESTAMP Properties as of MySQL 4.1”.)
Automatic updating of the first TIMESTAMP
column in a table occurs under any of the following
conditions:
You explicitly set the column to NULL.
The column is not specified explicitly in an
INSERT or LOAD DATA
INFILE statement.
The column is not specified explicitly in an
UPDATE statement and some other column
changes value. An UPDATE that sets a
column to the value it does not cause the
TIMESTAMP column to be updated; if you
set a column to its current value, MySQL ignores the
update for efficiency.
A TIMESTAMP column other than the first
also can be assigned the current date and time by setting it
to NULL or to any function that produces
the current date and time (NOW(),
CURRENT_TIMESTAMP).
Note that the information in the following discussion applies
to TIMESTAMP columns only for tables not
created with MAXDB mode enabled, because
such columns are created as DATETIME
columns.
You can set any TIMESTAMP column to a value
different from the current date and time by setting it
explicitly to the desired value. This is true even for the
first TIMESTAMP column. You can use this
property if, for example, you want a
TIMESTAMP to be set to the current date and
time when you create a row, but not to be changed whenever the
row is updated later:
Let MySQL set the column when the row is created. This initializes it to the current date and time.
When you perform subsequent updates to other columns in
the row, set the TIMESTAMP column
explicitly to its current value:
UPDATEtbl_nameSETtimestamp_col=timestamp_col,other_col1=new_value1,other_col2=new_value2, ...
Another way to maintain a column that records row-creation
time is to use a DATETIME column that you
initialize to NOW() when the row is created
and do not modify for subsequent updates.
TIMESTAMP values may range from the
beginning of 1970 to partway through the year 2037, with a
resolution of one second. Values are displayed as numbers.
When you store a value in a TIMESTAMP
column, it is assumed to be represented in the current time
zone, and is converted to UTC for storage. When you retrieve
the value, it is converted from UTC back to the local time
zone for display. Before MySQL 4.1.3, the server has a single
time zone. As of 4.1.3, clients can set their own time zones
on a per-connection basis, as described in
Section 5.10.8, “MySQL Server Time Zone Support”.
Prior to version 4.1, the format in which MySQL retrieves and
displays TIMESTAMP values depends on the
display size, as illustrated in the following table. The
“full” TIMESTAMP format is 14
digits, but TIMESTAMP columns may be
created with shorter display sizes:
| Data Type | Display Format |
TIMESTAMP(14) | YYYYMMDDHHMMSS |
TIMESTAMP(12) | YYMMDDHHMMSS |
TIMESTAMP(10) | YYMMDDHHMM |
TIMESTAMP(8) | YYYYMMDD |
TIMESTAMP(6) | YYMMDD |
TIMESTAMP(4) | YYMM |
TIMESTAMP(2) | YY |
All TIMESTAMP columns have the same storage
size, regardless of display size. The most common display
sizes are 6, 8, 12, and 14. You can specify an arbitrary
display size at table creation time, but values of 0 or
greater than 14 are coerced to 14. Odd-valued sizes in the
range from 1 to 13 are coerced to the next higher even number.
TIMESTAMP columns store legal values using
the full precision with which the value was specified,
regardless of the display size. This has several implications:
Always specify year, month, and day, even if your column
types are TIMESTAMP(4) or
TIMESTAMP(2). Otherwise, the value is
not a legal date and 0 is stored.
If you use ALTER TABLE to widen a
narrow TIMESTAMP column, information is
displayed that previously was “hidden.”
Similarly, narrowing a TIMESTAMP column
does not cause information to be lost, except in the sense
that less information is shown when the values are
displayed.
If you are planning to use mysqldump
for the database, do not use
TIMESTAMP(4) or
TIMESTAMP(2). The display format for
these data types are not legal dates and
0 will be stored instead. This
inconsistency is fixed starting with MySQL 4.1, where
display width is ignored. To prepare for transition to
versions after 4.0, you should change to use display
widths of 6 or more, which will produce a legal display
format. You can change the display width of
TIMESTAMP data types, without losing
any information, by using ALTER TABLE
as indicated above.
If you need to print the timestamps for external
applications, you can use MID() to
extract the relevant part of the timestamp: for example,
to imitate the TIMESTAMP(4) display
format.
Although TIMESTAMP values are stored to
full precision, the only function that operates directly
on the underlying stored value is
UNIX_TIMESTAMP(). Other functions
operate on the formatted retrieved value. This means you
cannot use a function such as HOUR() or
SECOND() unless the relevant part of
the TIMESTAMP value is included in the
formatted value. For example, the HH
part of a TIMESTAMP column is not
displayed unless the display size is at least 10, so
trying to use HOUR() on shorter
TIMESTAMP values produces a meaningless
result.
In MySQL 4.1, TIMESTAMP display format
changes to be the same as DATETIME, that
is, as a string in 'YYYY-MM-DD HH:MM:SS'
format rather than as a number in
YYYYMMDDHHMMSS format. To test applications
written for MySQL 4.0 for compatibility with this change, you
can set the new system variable to 1. This
variable is available beginning with MySQL 4.0.12. It can be
set at server startup by specifying the --new
option to mysqld. At runtime, a user who
has the SUPER privilege can set the global
value with a SET statement:
mysql> SET GLOBAL new = 1;
Any client can set its session value of new
as follows:
mysql> SET new = 1;
The general effect of setting new to 1 is
that values for a TIMESTAMP column display
as strings rather than as numbers. Also,
DESCRIBE displays the column definition as
timestamp(19), rather than as
timestamp(14).
However, the effect differs somewhat for
TIMESTAMP columns that are created while
new is set to 1. In this case, column
values display as strings and DESCRIBE
shows the definition as timestamp(19),
regardless of the current value of new.
In other words, with new=1, all
TIMESTAMP values display as strings and
DESCRIBE shows a display width of 19. For
columns created while new=1, they continue
to display as strings and to have a display width of 19 even
if new is set to 0.
For a TIMESTAMP column that displays as a
string, you can display it as a number by retrieving it as
col_name+0
In MySQL 4.1 and up, the properties of the
TIMESTAMP data type change in the ways
described in this section.
From MySQL 4.1.0 on, TIMESTAMP display
format differs from that of earlier MySQL releases:
TIMESTAMP columns are displayed in the
same format as DATETIME columns. In
other words, the display width is fixed at 19 characters,
and the format is YYYY-MM-DD HH:MM:SS.
Display widths (used as described in the preceding
section) are no longer supported. In other words, for
declarations such as TIMESTAMP(2),
TIMESTAMP(4), and so on, the display
width is ignored.
Beginning with MySQL 4.1.1, the MySQL server can be also be
run with the MAXDB SQL mode enabled. When
the server runs with this mode enabled,
TIMESTAMP is identical with
DATETIME. That is, if this mode is enabled
at the time that a table is created,
TIMESTAMP columns are created as
DATETIME columns. As a result, such columns
use DATETIME display format, have the same
range of values, and there is no automatic initialization or
updating to the current date and time.
To enable MAXDB mode, set the server SQL
mode to MAXDB at startup using the
--sql-mode=MAXDB server option or by setting
the global sql_mode variable at runtime:
mysql> SET GLOBAL sql_mode=MAXDB;
A client can cause the server to run in
MAXDB mode for its own connection as
follows:
mysql> SET SESSION sql_mode=MAXDB;
Beginning with MySQL 4.1.2, you have more flexible control
over when automatic TIMESTAMP
initialization and updating occur and which column should have
those behaviors:
For one TIMESTAMP column in a table,
you can assign the current timestamp as the default value
and the auto-update value. It is possible to have the
current timestamp be the default value for initializing
the column, for the auto-update value, or both. It is not
possible to have the current timestamp be the default
value for one column and the auto-update value for another
column.
You can specify which TIMESTAMP column
to automatically initialize or update to the current date
and time. This need not be the first
TIMESTAMP column.
The following discussion describes the revised syntax and
behavior. Note that this information applies only to
TIMESTAMP columns for tables not created
with MAXDB mode enabled. As noted earlier
in this section, MAXDB mode causes columns
to be created as DATETIME columns.
The following items summarize the pre-4.1.2 properties for
TIMESTAMP initialization and updating:
The first TIMESTAMP column in table row
automatically is set to the current timestamp when the record
is created if the column is set to NULL or
is not specified at all.
The first TIMESTAMP column in table row
automatically is updated to the current timestamp when the
value of any other column in the row is changed, unless the
TIMESTAMP column explicitly is assigned a
value other than NULL.
If a DEFAULT value is specified for the
first TIMESTAMP column when the table is
created, it is silently ignored.
Other TIMESTAMP columns in the table can be
set to the current TIMESTAMP by assigning
NULL to them, but they do not update
automatically.
As of 4.1.2, you have more flexibility in deciding which
TIMESTAMP column automatically is
initialized and updated to the current timestamp. The rules
are as follows:
If a DEFAULT value is specified for the
first TIMESTAMP column in a table, it is
not ignored. The default can be
CURRENT_TIMESTAMP or a constant date and
time value.
DEFAULT NULL is the same as
DEFAULT CURRENT_TIMESTAMP for the
first TIMESTAMP
column. For any other TIMESTAMP column,
DEFAULT NULL is treated as DEFAULT
0.
Any single TIMESTAMP column in a table can
be used as the one that is initialized to the current
timestamp or updated automatically.
In a CREATE TABLE statement, the first
TIMESTAMP column can be declared in any of
the following ways:
With both DEFAULT CURRENT_TIMESTAMP and
ON UPDATE CURRENT_TIMESTAMP clauses,
the column has the current timestamp for its default
value, and is automatically updated.
With neither DEFAULT nor ON
UPDATE clauses, it is the same as
DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP.
With a DEFAULT CURRENT_TIMESTAMP clause
and no ON UPDATE clause, the column has
the current timestamp for its default value but is not
automatically updated.
With no DEFAULT clause and with an
ON UPDATE CURRENT_TIMESTAMP clause, the
column has a default of 0 and is automatically updated.
With a constant DEFAULT value, the
column has the given default. If the column has an
ON UPDATE CURRENT_TIMESTAMP clause, it
is automatically updated, otherwise not.
In other words, you can use the current timestamp for both the
initial value and the auto-update value, or either one, or
neither. (For example, you can specify ON
UPDATE to get auto-update without also having the
column auto-initialized.)
CURRENT_TIMESTAMP or any of its synonyms
(CURRENT_TIMESTAMP(),
NOW(), LOCALTIME,
LOCALTIME(),
LOCALTIMESTAMP, or
LOCALTIMESTAMP()) can be used in the
DEFAULT and ON UPDATE
clauses. They all mean “the current timestamp.”
(UTC_TIMESTAMP is not allowed. Its range of
values does not align with those of the
TIMESTAMP column anyway unless the current
time zone is UTC.)
The order of the DEFAULT and ON
UPDATE attributes does not matter. If both
DEFAULT and ON UPDATE
are specified for a TIMESTAMP column,
either can precede the other. For example, these statements
are equivalent:
CREATE TABLE t (ts TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DEFAULT CURRENT_TIMESTAMP);
To specify automatic default or updating for a
TIMESTAMP column other than the first one,
you must suppress the automatic initialization and update
behaviors for the first TIMESTAMP column by
explicitly assigning it a constant DEFAULT
value (for example, DEFAULT 0 or
DEFAULT '2003-01-01 00:00:00'). Then for
the other TIMESTAMP column, the rules are
the same as for the first TIMESTAMP column,
except that if you omit both of the DEFAULT
and ON UPDATE clauses, no automatic
initialization or updating occurs.
Example. These statements are equivalent:
CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DEFAULT CURRENT_TIMESTAMP);
Beginning with MySQL 4.1.3, you can set the current time zone
on a per-connection basis, as described in
Section 5.10.8, “MySQL Server Time Zone Support”.
TIMESTAMP values still are stored in UTC,
but are converted from the current time zone for storage, and
converted back to the current time zone for retrieval. As long
as the time zone setting remains constant, you get back the
same value you store. If you store a
TIMESTAMP value, and then change the time
zone and retrieve the value, the retrieved value is different
than the value you stored. This occurs because the same time
zone was not used for conversion in both directions. The
current time zone is available as the value of the
time_zone system variable.
Beginning with MySQL 4.1.6, you can include the
NULL attribute in the definition of a
TIMESTAMP column to allow the column to
contain NULL values. For example:
CREATE TABLE t ( ts1 TIMESTAMP NULL DEFAULT NULL, ts2 TIMESTAMP NULL DEFAULT 0, ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP );
Before MySQL 4.1.6 (and even as of 4.1.6 if the
NULL attribute is not specified), setting
the column to NULL sets it to the current
timestamp. Note that a TIMESTAMP column
which allows NULL values
not take on the current timestamp except
under one of the following conditions:
Its default value is defined as
CURRENT_TIMESTAMP
NOW() or
CURRENT_TIMESTAMP is inserted into the
column
In other words, a TIMESTAMP column defined
as NULL will auto-initialize only if it is
created using a definition such as the following:
CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
Otherwise — that is, if the TIMESTAMP
column is defined to allow NULL values but
not using DEFAULT TIMESTAMP, as shown
here…
CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT NULL); CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
…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);
MySQL retrieves and displays TIME values in
'HH:MM:SS' format (or
'HHH:MM:SS' format for large hours values).
TIME values may range from
'-838:59:59' to
'838:59:59'. The hours part may be so large
because the TIME type can be used not only to
represent a time of day (which must be less than 24 hours), but
also elapsed time or a time interval between two events (which
may be much greater than 24 hours, or even negative).
You can specify TIME values in a variety of
formats:
As a string in 'D HH:MM:SS.fraction'
format. You can also use one of the following
“relaxed” syntaxes:
'HH:MM:SS.fraction',
'HH:MM:SS', 'HH:MM',
'D HH:MM:SS', 'D
HH:MM', 'D HH', or
'SS'. Here D
represents days and can have a value from 0 to 34. Note that
MySQL does not store the fraction part.
As a string with no delimiters in
'HHMMSS' format, provided that it makes
sense as a time. For example, '101112' is
understood as '10:11:12', but
'109712' is illegal (it has a nonsensical
minute part) and becomes '00:00:00'.
As a number in HHMMSS format, provided
that it makes sense as a time. For example,
101112 is understood as
'10:11:12'. The following alternative
formats are also understood: SS,
MMSS, HHMMSS,
HHMMSS.fraction. Note that MySQL does not
store the fractional part.
As the result of a function that returns a value that is
acceptable in a TIME context, such as
CURRENT_TIME.
For TIME values specified as strings that
include a time part delimiter, it is not necessary to specify
two digits for hours, minutes, or seconds values that are less
than 10. '8:3:2' is the
same as '08:03:02'.
Be careful about assigning “short”
TIME values to a TIME
column. Without colons, MySQL interprets values using the
assumption that the rightmost digits represent seconds. (MySQL
interprets TIME values as elapsed time rather
than as time of day.) For example, you might think of
'1112' and 1112 as meaning
'11:12:00' (12 minutes after 11 o'clock), but
MySQL interprets them as '00:11:12' (11
minutes, 12 seconds). Similarly, '12' and
12 are interpreted as
'00:00:12'. TIME values
with colons, by contrast, are always treated as time of the day.
That is '11:12' means
'11:12:00', not
'00:11:12'.
By default, values that lie outside the TIME
range but are otherwise legal are clipped to the closest
endpoint of the range. For example,
'-850:00:00' and
'850:00:00' are converted to
'-838:59:59' and
'838:59:59'. Illegal TIME
values are converted to '00:00:00'. Note that
because '00:00:00' is itself a legal
TIME value, there is no way to tell, from a
value of '00:00:00' stored in a table,
whether the original value was specified as
'00:00:00' or whether it was illegal.
For more restrictive treatment of invalid
TIME values, enable strict SQL mode to cause
errors to occur. See Section 5.2.5, “The Server SQL Mode”.
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.
As discussed in Section 1.4.5, “Year 2000 Compliance”, MySQL itself is year 2000 (Y2K) safe. However, particular input values presented to MySQL may not be Y2K safe. Any value containing a two-digit year is ambiguous, because the c