11.2.5 2-年数字(2)限制,并迁移到年 4 位数字
本节介绍使用 2 位数YEAR(2)数据类型时可能出现的问题,并提供有关将现有YEAR(2)列转换为 4 位数的年份值列的信息,这些列可以声明为YEAR
,隐式显示宽度为 4 个字符,或等效地为YEAR(4)
,并具有明确的显示宽度。
尽管YEAR
/YEAR(4)和不推荐使用的YEAR(2)类型的值的内部范围是相同的(1901
到2155
和0000
),但是YEAR(2)的显示宽度使该类型固有地模棱两可,因为显示的值仅指示内部值的最后两位数字并省略世纪数字。在某些情况下,结果可能是信息丢失。因此,请避免在应用程序中使用YEAR(2),而在需要使用年值数据类型的地方使用YEAR
/YEAR(4)。从 MySQL 5.7.5 开始,不再支持YEAR(2),必须将现有的 2 位YEAR(2)列转换为 4 位YEAR列才能再次使用。
YEAR(2) Limitations
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转储在上一个示例中创建的 table,则转储文件使用相同的 2 位数字 table 示形式(
12
)table 示所有y2
值。如果从转储文件中重新加载 table,则所有结果行的内部值均为2012
,显示值为12
,因此将失去它们之间的区别。 -
将 2 位数或 4 位数的YEAR数据值转换为字符串形式将使用数据类型显示宽度。假设YEAR(2)列和
YEAR
/YEAR(4)列都包含值1970
。将每一列分配给一个字符串将分别导致'70'
或'1970'
的值。即,从YEAR(2)转换为字符串会发生信息丢失。 -
当将
1970
到2069
范围之外的值插入到CSVtable 的YEAR(2)列中时,存储不正确。例如,插入2211
会导致显示值11
但内部值2011
。
为避免这些问题,请使用 4 位数字YEAR或YEAR(4)数据类型,而不是 2 位数字YEAR(2)数据类型。有关迁移策略的建议将在本节后面介绍。
MySQL 5.7 中减少/删除了 YEAR(2)支持
在 MySQL 5.7.5 之前,对YEAR(2)的支持已减少。从 MySQL 5.7.5 开始,不再支持YEAR(2)。
-
新 table 的YEAR(2)列定义会产生警告或错误:
-
在 MySQL 5.7.5 之前,新 table 的YEAR(2)列定义被转换(带有ER_INVALID_YEAR_COLUMN_LENGTH警告)为 4 位YEAR列:
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 开始,新 table 的YEAR(2)列定义产生ER_INVALID_YEAR_COLUMN_LENGTH错误:
mysql> CREATE TABLE t1 (y YEAR(2));
ERROR 1818 (HY000): Supports only YEAR or YEAR(4) column.
-
在 MySQL 5.7.5 之前,与旧版本的 MySQL 一样,查询中处理YEAR(2)。
- 从 MySQL 5.7.5 开始,查询中的YEAR(2)列会产生警告或错误。
-
ALTER TABLE导致 table 重建的语句。
-
REPAIR TABLE(如果它找到包含YEAR(2)列的 table,则CHECK TABLE建议您使用)。
-
使用mysqldump转储并重新加载转储文件。与前三个项目执行的转换不同,转储和重新加载有可能更改数据值。
-
MySQL 升级通常至少涉及最后两项。但是,关于YEAR(2),mysql_upgrade优于mysqldump,如上所述,它可以更改数据值。
从 YEAR(2)迁移到 4 位数字 YEAR
要将 2 位数的YEAR(2)列转换为 4 位数的YEAR列,您可以随时手动进行,而无需升级。另外,您可以升级到 MySQL 版本,减少或删除对YEAR(2)的支持(MySQL 5.6.6 或更高版本),然后让 MySQL 自动转换YEAR(2)列。在后一种情况下,请避免通过转储和重新加载数据进行升级,因为这可能会更改数据值。另外,如果使用复制,则必须考虑升级注意事项。
要将 2 位数的YEAR(2)列手动转换为 4 位数的YEAR,请使用ALTER TABLE或REPAIR TABLE。假设 tablet1
具有以下定义:
CREATE TABLE t1 (ycol YEAR(2) NOT NULL DEFAULT '70');
使用ALTER TABLE
修改列,如下所示:
ALTER TABLE t1 FORCE;
ALTER TABLE语句在不更改YEAR(2)值的情况下转换 table。如果服务器是复制主服务器,则ALTER TABLE语句复制到从属服务器,并在每个服务器上更改相应的 table。
另一种迁移方法是执行二进制升级:在不转储和重新加载数据的情况下就地升级 MySQL。然后运行mysql_upgrade,它使用REPAIR TABLE将 2 位YEAR(2)列转换为 4 位YEAR列而不更改数据值。如果服务器是复制主服务器,则REPAIR TABLE语句复制到从属服务器,并在每个服务器上进行相应的 table 更改,除非您使用--skip-write-binlog选项调用mysql_upgrade。
升级到复制服务器通常需要将从服务器升级到较新版本的 MySQL,然后再升级主服务器。例如,如果主服务器和从服务器都运行 MySQL 5.5,则典型的升级过程包括将从服务器升级到 5.6,然后再将主服务器升级到 5.6. 关于从 MySQL 5.6.6 开始对YEAR(2)的不同处理,该升级序列会导致一个问题:假设从属服务器已被升级,但主服务器尚未被升级。然后,在主服务器上创建一个包含 2 位数YEAR(2)列的 table,将在从服务器上创建一个包含 4 位数YEAR列的 table。因此,如果您使用基于语句的复制,则以下操作在主服务器和从服务器上的结果将有所不同:
为避免此类问题,请在升级之前将主文件上的所有 2 位YEAR(2)列修改为 4 位YEAR列。 (如前所述,使用ALTER TABLE。)这样就可以正常升级(先从动,然后再升级为主),而无需在主从之间引入YEAR(2)到YEAR(4)的差异。
应避免一种迁移方法:请勿使用mysqldump转储数据,并在升级后重新加载转储文件。如前所述,这可能会更改YEAR(2)值。