This section describes problems that can occur when using the
YEAR(2) data type and
provides information about converting existing
YEAR(2) columns to 4-digit
year-valued columns, which can be declared as
YEAR with an implicit display width of 4
characters, or equivalently as
an explicit display width.
Although the internal range of values for
and the deprecated
is the same (
0000), the display width for
YEAR(2) makes that type
inherently ambiguous because displayed values indicate only the
last two digits of the internal values and omit the century
digits. The result can be a loss of information under certain
circumstances. For this reason, avoid using
YEAR(2) in your applications and
wherever you need a year-valued data type. Note that conversion
is required at some point because support for
YEAR data types with display
values other than 4, most notably
YEAR(2), is reduced as of MySQL
5.6.6 and is removed entirely in MySQL 5.7.
Issues with the
type include ambiguity of displayed values, and possible loss
of information when values are dumped and reloaded or
converted to strings.
mysql> CREATE TABLE t (y2 YEAR(2), y4 YEAR); Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> INSERT INTO t (y2) VALUES(1912),(2012),(2112); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> UPDATE t SET y4 = y2; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> SELECT * FROM t; +------+------+ | y2 | y4 | +------+------+ | 12 | 1912 | | 12 | 2012 | | 12 | 2112 | +------+------+ 3 rows in set (0.00 sec)
If you use mysqldump to dump the table created in the preceding example, the dump file represents all
y2values using the same 2-digit representation (
12). If you reload the table from the dump file, all resulting rows have internal value
2012and display value
12, thus losing the distinctions between them.
Conversion of a 2-digit or 4-digit
YEARdata value to string form uses the data type display width. Suppose that a
YEAR(2)column and a
YEAR(4)column both contain the value
1970. Assigning each column to a string results in a value of
'1970', respectively. That is, loss of information occurs for conversion from
Values outside the range from
2069are stored incorrectly when inserted into a
YEAR(2)column in a
CSVtable. For example, inserting
2211results in a display value of
11but an internal value of
As of MySQL 5.6.6, support for
YEAR(2) is diminished:
mysql> CREATE TABLE t1 (y YEAR(2)); Query OK, 0 rows affected, 1 warning (0.04 sec) mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Warning Code: 1818 Message: YEAR(2) column type is deprecated. Creating YEAR(4) column instead. 1 row in set (0.00 sec) mysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `y` year(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
YEAR(2)columns in existing tables remain as
YEAR(2)and are processed in queries as in older versions of MySQL. However, several programs or statements convert
YEAR(2)columns to 4-digit
ALTER TABLEstatements that result in a table rebuild.
Dumping with mysqldump and reloading the dump file. Unlike the conversions performed by the preceding three items, a dump and reload has the potential to change data values.
To convert 2-digit
columns to 4-digit
columns, you can do so manually at any time without upgrading.
Alternatively, you can upgrade to a version of MySQL with
reduced support for
(MySQL 5.6.6 or later), then have MySQL convert
YEAR(2) columns automatically.
In the latter case, avoid upgrading by dumping and reloading
your data because that can change data values. In addition, if
you use replication, there are upgrade considerations you must
take into account.
CREATE TABLE t1 (ycol YEAR(2) NOT NULL DEFAULT '70');
Modify the column using
ALTER TABLE as
ALTER TABLE t1 FORCE;
ALTER TABLE statement
converts the table without changing
YEAR(2) values. If the server
is a replication source, the
TABLE statement replicates to replicas and makes the
corresponding table change on each one.
Another migration method is to perform a binary upgrade:
Upgrade MySQL 5.6.6 or later in place without dumping and
reloading your data. Then run
mysql_upgrade, which uses
REPAIR TABLE to convert 2-digit
YEAR(2) columns to 4-digit
YEAR columns without changing
data values. If the server is a replication source, the
REPAIR TABLE statements
replicate to replicas and make the corresponding table changes
on each one, unless you invoke
mysql_upgrade with the
Upgrades to replication servers usually involve upgrading
replicas to a newer version of MySQL, then upgrading the
source. For example, if a source and replica both run MySQL
5.5, a typical upgrade sequence involves upgrading the replica
to 5.6, then upgrading the source to 5.6. With regard to the
different treatment of
as of MySQL 5.6.6, that upgrade sequence results in a problem:
Suppose that the replica has been upgraded but not yet the
source. Then creating a table containing a 2-digit
YEAR(2) column on the source
results in a table containing a 4-digit
YEAR column on the replica.
Consequently, the following operations have a different result
on the source and replica, if you use statement-based
To avoid such problems, use one of these strategies:
Use row-based replication instead of statement-based replication.
Modify all 2-digit
YEAR(2)columns on the source to 4-digit
YEARcolumns before upgrading. (Use
ALTER TABLE, as described previously.) That makes it possible to upgrade normally (replica first, then source) without introducing any
YEAR(4)differences between the source and replica.