Although the internal range of values for
YEAR(4) and the deprecated
YEAR(2) type 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, before MySQL 5.7.5, avoid using
YEAR(2) in your applications and
YEAR(4) wherever you need a
YEAR data type. As of MySQL
5.7.5, support for
removed and existing
columns must be converted to
YEAR(4) to become usable again.
Issues with the
type include ambiguity of displayed values, and possible loss
of information when values are dumped and reloaded or
converted to strings.
CREATE TABLE t (y2 YEAR(2), y4 YEAR(4));Query OK, 0 rows affected (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 item, 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 among them.
Conversion of a
YEAR(4)data value to string form uses the display width of the
YEARtype. Suppose that
YEAR(4)columns 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
2111results in a display value of
11but an internal value of
YEAR(2)column definitions for new tables produce warnings or errors:
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)
CREATE TABLE t1 (y YEAR(2));ERROR 1818 (HY000): Supports only YEAR or YEAR(4) column.
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 values.
A MySQL upgrade usually involves at least one of the last two items. However, with respect to
YEAR(2), mysql_upgrade is preferable. You should avoid using mysqldump because, as noted, that can change values.
YEAR(2) columns to
YEAR(4), you can do so manually
at any time without upgrading. Alternatively, you can upgrade
to a version of MySQL with reduced or removed support for
YEAR(2) (MySQL 5.6.6 or later),
then have MySQL convert
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 master, the
TABLE statement replicates to slaves and makes the
corresponding table change on each one.
Another migration method is to perform a binary upgrade:
Install MySQL without dumping and reloading your data. Then
run mysql_upgrade, which uses
REPAIR TABLE to convert
YEAR(2) columns to
YEAR(4) without changing data
values. If the server is a replication master, the
REPAIR TABLE statements
replicate to slaves and make the corresponding table changes
on each one, unless you invoke
mysql_upgrade with the
Upgrades to replication servers usually involve upgrading
slaves to a newer version of MySQL, then upgrading the master.
For example, if a master and slave both run MySQL 5.5, a
typical upgrade sequence involves upgrading the slave to 5.6,
then upgrading the master to 5.6. With regard to the different
YEAR(2) as of
MySQL 5.6.6, that upgrade sequence results in a problem:
Suppose that the slave has been upgraded but not yet the
master. Then creating a table containing a
YEAR(2) column on the master
results in a table containing a
YEAR(4) column on the slave.
Consequently, these operations will have a different result on
the master and slave, if you use statement-based replication:
To avoid such problems, modify all
YEAR(2) columns on the master
YEAR(4) before upgrading.
ALTER TABLE, as described
previously.) Then you can upgrade normally (slave first, then
master) without introducing any
YEAR(4) differences between the
master and slave.