Although the internal range of values for
YEAR(2) 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, consider avoiding
YEAR(2) throughout your
applications and using
wherever you need a
type. Note that conversion will become necessary at some point
because support for
types with display values other than 4, most notably
YEAR(2), is reduced as of MySQL
5.6.6 and will be removed entirely in a future release.
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(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
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)
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 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 5.6.6 or later without dumping and reloading
your data. Then run mysql_upgrade, which
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, use one of these strategies:
Use row-based replication instead of statement-based replication.
YEAR(2)columns on the master to
YEAR(4)before upgrading. (Use
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.