11.2.5 2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR — MySQL 5.7

11.2.5 Ограничения YEAR(2) и миграция на YEAR(4)

В этом разделе описываются проблемы, которые могут возникнуть при использовании двухзначного типа данных YEAR(2) и предоставляется информация о преобразовании существующих столбцов YEAR(2) в столбцы со значениями года с четырьмя цифрами, которые можно объявить как 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) удалена, и существующие двухзначные столбцы 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 для выгрузки таблицы, созданной в предыдущем примере, файл выгрузки представляет все значения y2 с использованием одного и того же двухзначного представления (12). При повторной загрузке таблицы из файла выгрузки все полученные строки имеют внутреннее значение 2012 и отображаемое значение 12, тем самым теряя различия между ними.
Преобразование значения данных двухзначного или четырёхзначного типа YEAR в строку использует ширину отображения типа данных. Предположим, что столбец YEAR(2) и столбец YEAR/YEAR(4) оба содержат значение 1970. Присвоение каждого столбца строке приводит к значению '70' или '1970' соответственно. То есть, происходит потеря информации при преобразовании из YEAR(2) в строку.
Значения, выходящие за пределы диапазона от 1970 до 2069, хранятся неправильно при вставке в столбец YEAR(2) в таблице CSV. Например, вставка 2211 приводит к отображаемому значению 11, но внутреннему значению 2011.

Чтобы избежать этих проблем, используйте четырёхзначный тип данных YEAR или YEAR(4) вместо двухзначного типа данных YEAR(2). Рекомендации по стратегиям миграции будут представлены позже в этом разделе.

Сокращенная/удаленная поддержка YEAR(2) в MySQL 5.7

До MySQL 5.7.5 поддержка YEAR(2) была ограничена. Начиная с MySQL 5.7.5, поддержка YEAR(2) удалена.

Определения столбцов YEAR(2) для новых таблиц приводят к предупреждениям или ошибкам:

До MySQL 5.7.5 определения столбцов YEAR(2) для новых таблиц преобразуются (с предупреждением) в четырёхзначные столбцы 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, определения столбцов YEAR(2) для новых таблиц приводят к ошибке:
```
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 (что CHECK TABLE рекомендует использовать, если обнаруживает таблицу, содержащую столбцы YEAR(2)).
- mysql_upgrade (который использует REPAIR TABLE).
- Выгрузка с помощью mysqldump и повторная загрузка файла выгрузки. В отличие от преобразований, выполненных тремя предыдущими пунктами, выгрузка и загрузка могут изменить значения данных.
Обновление MySQL обычно включает в себя по крайней мере два последних пункта. Однако, относительно YEAR(2), mysql_upgrade предпочтительнее mysqldump, который, как отмечалось, может изменить значения данных.

END_OF_DOCUMENT_MARKER

Миграция с 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. Предположим, что у таблицы 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 для преобразования столбцов с 2-значным YEAR(2) в 4-значный YEAR, не изменяя при этом значения данных. Если сервер является источником репликации, команды REPAIR TABLE реплицируются на реплики и производят соответствующие изменения в каждой из них, если вы не используете опцию mysql_upgrade с опцией --skip-write-binlog.

Обновления серверов репликации обычно включают обновление реплик до более новой версии MySQL, а затем обновление источника. Например, если источник и реплика оба работают на MySQL 5.5, типичная последовательность обновления включает обновление реплики до 5.6, а затем обновление источника до 5.6. Что касается разного обращения с YEAR(2) начиная с MySQL 5.6.6, эта последовательность обновления приводит к проблеме: предположим, что реплика была обновлена, но не источник. Тогда создание таблицы, содержащей столбец с 2-значным YEAR(2) на источнике, приводит к таблице, содержащей столбец с 4-значным YEAR на реплике. Следовательно, следующие операции имеют различный результат на источнике и реплике, если вы используете репликацию на основе инструкций:

Вставка числового 0. Результирующее значение имеет внутреннее значение 2000 на источнике, но 0000 на реплике.
Преобразование YEAR(2) в строку. Эта операция использует значение для отображения YEAR(2) на источнике, но YEAR(4) на реплике.

Чтобы избежать таких проблем, измените все столбцы с 2-значным YEAR(2) на столбцы с 4-значным YEAR на источнике до обновления. (Используйте ALTER TABLE, как описано ранее.) Это позволит выполнить обновление в обычном порядке (сначала реплика, затем источник) без введения каких-либо различий между источником и репликой в YEAR(2) и YEAR(4).

Один метод миграции следует избегать: не делайте дамп ваших данных с помощью mysqldump и не загружайте дамп после обновления. Это может изменить значения YEAR(2), как описано ранее.

Миграция из столбцов с 2-значным YEAR(2) в столбцы с 4-значным YEAR также должна включать проверку кода приложения на предмет возможных изменений поведения в таких условиях:

Код, который ожидает, что выбор столбца YEAR даст ровно два цифры.
Код, который не учитывает разное обращение с вставкой числового 0: Вставка 0 в YEAR(2) или YEAR(4) приводит к внутреннему значению 2000 или 0000 соответственно.