官方文档地址: 11.2.5 2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR
注意:该文档属于 MySQL 5.7 版本,因为在 MySQL 8.0 参考手册中有几篇文档引用了该文档,所以也翻译放在了这里。
本节描述在使用两位的YEAR(2)
数据类型时可能出现的问题,并提供有关将现有的YEAR(2)
列转换为具有四位年份值的列的方法,这些列可以声明为具有隐式显示宽度(四个字符)的YEAR
,也可以声明为具有显式显示宽度的YEAR(4)
。
尽管YEAR
/YEAR(4)
和已弃用的YEAR(2)
类型的内部值范围是相同的(1901
到2155
,0000
),YEAR(2)
的显示宽度使该类型具有固有的歧义,因为显示的值只指示内部值的最后两位数字,而省略了世纪数字。在某些情况下,结果可能导致信息的丢失。因此,在应用程序中避免使用YEAR(2)
,而在需要年值数据类型的地方使用YEAR
/YEAR(4)
。从 MySQL 5.7.5 开始,对YEAR(2)
的支持被删除,现有的两位数的YEAR(2)
列必须转换为四位数的YEAR
列才能再次使用。
YEAR(2) 的限制
YEAR(2)
数据类型的问题包括显示值的模糊性,以及转储、重新加载或转换为字符串时可能丢失的信息。
YEAR(2)
值的显示可能有二义性。可能最多有三个YEAR(2)
类型内部值不同的值可能会显示相同的值,如下面的例子所示:
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)
如果您使用mysqldump
转储前面示例中创建的表,转储文件使用相同的两位数(12)表示所有的 y2 值。如果从转储文件中重新加载表,所有结果行都会变为内部值 2012 和显示值 12,这样就丢失了它们之间的区别。
将两位或四位YEAR
数据转换为字符串形式将使用数据类型的显示宽度。假设YEAR(2)
列和YEAR
/YEAR(4)
列都包含值1970
。将每个列赋值给一个字符串,结果分别为'70'
或'1970'
。也就是说,从YEAR(2)
到字符串的转换会丢失信息。
当在 CSV 表的YEAR(2)
列插入数据时,超出1970
到2069
范围的值存储错误。例如,插入2211
的结果显示值为11
,但内部值为2011
。
为了避免这些问题,请使用四位的YEAR
或YEAR(4)
数据类型,而不是两位的YEAR(2)
数据类型。关于迁移策略的建议将在本节后面介绍。
MySQL 5.7 中减少/删除对 YEAR(2) 的支持
在 MySQL 5.7.5 之前,对YEAR(2)
的支持减少了。从 MySQL 5.7.5 开始,对YEAR(2)
的支持被删除了。
在新表中定义一个YEAR(2)
列会产生警告或错误:
在 MySQL 5.7.5 之前,新表的YEAR(2)
列定义会被转换为四位的YEAR
列(产生一个警告ER_INVALID_YEAR_COLUMN_LENGTH
):
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)
从 MySQL 5.7.5 开始,新表的YEAR(2)
列定义会产生一个ER_INVALID_YEAR_COLUMN_LENGTH
错误:
mysql> CREATE TABLE t1 (y YEAR(2));
ERROR 1818 (HY000): Supports only YEAR or YEAR(4) column.
现有表格中的YEAR(2)
列仍为YEAR(2)
列:
- 在 MySQL 5.7.5 之前,
YEAR(2)
在查询中处理,就像在旧版本的 MySQL 中一样。 - 从 MySQL 5.7.5 开始,查询中的
YEAR(2)
列会产生警告或错误。
一些程序或语句会自动将YEAR(2)
列转换为四位的YEAR
列:
- 导致表重建的
ALTER TABLE
语句。 REPAIR TABLE
(如果找到一个包含YEAR(2)
列的表,建议使用CHECK TABLE
)。mysql_upgrade
(使用REPAIR TABLE
)。- 使用
mysqldump
进行转储,并重新加载转储文件。与前面三个项目执行的转换不同,转储和重新加载有可能更改数据值。
MySQL 的升级通常至少涉及最后两项中的一项。然而,就YEAR(2)
而言,mysql_upgrade
比mysqldump
好一点,如前所述,因为mysqldump
会更改数据值。
从 YEAR(2) 迁移到四位数 YEAR
要将两位YEAR(2)
列转换为四位YEAR
列,您可以在不升级的情况下随时手动进行转换。或者,你可以升级到一个减少或删除对YEAR(2)
类型支持的 MySQL 版本(MySQL 5.6.6 或更高的版本),然后让 MySQL 自动转换YEAR(2)
列。在后一种情况下,通过转储和重新加载数据来避免升级,因为这可能会更改数据值。此外,如果您使用复制,则必须考虑一些升级注意事项。
若要手动将两位数字的YEAR(2)
列转换为四位数字的YEAR
列,使用ALTER TABLE
或REPAIR TABLE
。假设表 t1 有这样的定义:
CREATE TABLE t1 (ycol YEAR(2) NOT NULL DEFAULT '70');
修改列使用ALTER TABLE
如下:
ALTER TABLE t1 FORCE;
ALTER TABLE
语句在不改变YEAR(2)
值的情况下转换表。如果服务器是复制源,那么ALTER TABLE
语句将复制到多个副本,并对每个副本进行相应的表更改。
另一种迁移方法是执行二进制升级:就地升级 MySQL,无需转储和重新加载数据。然后运行mysql_upgrade
,它使用REPAIR TABLE
将两位的YEAR(2)
列转换为四位的YEAR
列,而不改变数据值。如果服务器是复制源,REPAIR TABLE
语句复制到多个副本,并对每个副本进行相应的表更改,除非你调用mysql_upgrade
的时候使用了--skip-write-binlog
选项。
对复制服务器的升级通常涉及将副本升级到 MySQL 的新版本,然后升级源。例如,如果一个源和副本都运行 MySQL 5.5,典型的升级顺序包括将副本升级到 5.6,然后将源升级到 5.6。由于 MySQL 5.6.6 对YEAR(2)
的不同处理,升级顺序导致了一个问题:假设副本已经升级,但源还没有升级。然后,在源上创建一个包含两位数字YEAR(2)
列的表,会在副本上创建一个包含四位数字YEAR
列的表。因此,如果使用基于语句的复制,以下操作在源和副本上有不同的结果:
- 插入数字 0。结果值在源上的内部值为 2000,而在副本上的内部值为 0000。
- 将
YEAR(2)
值转换为字符串。该操作在源上使用YEAR(2)
的显示值,在副本上使用YEAR(4)
的显示值。
为避免此类问题,请在升级前将源上的所有两位YEAR(2)
列修改为四位YEAR
列。(如前所述,使用ALTER TABLE
。)这使得正常升级(先升级副本,然后升级源)成为可能,而不会在源和副本之间引入任何YEAR(2)
到YEAR(4)
的差异。
应该避免使用的一种迁移方法:不要使用mysqldump
转储数据,升级后请重新加载转储文件。如前所述,有可能改变YEAR(2)
的值。
从两位数字的YEAR(2)
列迁移到四位数字的YEAR
列时,还应该检查应用程序代码,以确定在以下情况下行为发生变化的可能性:
- 期望选择
YEAR
列时恰好产生两位数字的代码。 - 不考虑插入数字 0 时不同处理方式的代码:向
YEAR(2)
或YEAR(4)
插入 0 会分别得到内部值 2000 或 0000 。