Calíopeブログ

MySQLとMariaDBでENUMの値を名前変更する方法：エラー1265「Data truncated」を出さずに

著者： ·

開発MySQLMariaDB

誰もが読めるようにAIで翻訳しています。原文はスペイン語です。 原文を読む

「どんな問題も、いったん説明されてしまえば、ひどく子どもじみたものになる」シャーロック・ホームズ、「踊る人形」（1903年）より。

この記事はごく短いものです。解決策は読めばわかる3つの文で、こう書くと、話すことなど何もないように見えます。でもホームズが言うように、誰かが説明してくれれば、何でも簡単です。私にそれが起きた日、説明してくれる人はいませんでした。エラーの意味も、そこから抜け出す方法もわからず、何時間もの作業が消えていきました。

直す必要があったENUM

いくつかのカタログのコードを入れたテーブルがあり、ENUMの列が、各行がどのカタログに属するかを表していました。値はすべて複数形でしたが、provinciaだけが単数形で、これをprovinciasにする必要がありました。

CREATE TABLE codigos (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    catalogo ENUM('paises', 'provincia', 'cantones') NOT NULL,
    codigo   VARCHAR(10)  NOT NULL,
    nombre   VARCHAR(100) NOT NULL
);

INSERT INTO codigos (catalogo, codigo, nombre) VALUES
    ('paises',    'CR',  'Costa Rica'),
    ('provincia', '1',   'San José'),
    ('provincia', '4',   'Heredia'),
    ('cantones',  '101', 'San José'),
    ('cantones',  '102', 'Escazú');
対応エンジンMySQLMariaDB

ENUMは各行にテキストを保存するのではなく、リストの中での値の位置（1、2、3…）を保存します。場所をとらず、リストにない値はエンジンが保存させません。この最後の点こそが、私を困らせることになりました。

エラー

自然なのは、名前を直した定義で列を変更することです。

mysql> ALTER TABLE codigos MODIFY catalogo ENUM('paises', 'provincias', 'cantones') NOT NULL;
ERROR 1265 (01000): Data truncated for column 'catalogo' at row 2

「Data truncated」は紛らわしい言葉です。ここでは長いテキストが切り詰められているわけではないからです。実際には、定義を変えると、MySQLは各行の値をテキストとして新しいリストの中で探し、変換します。2行目はprovinciaですが、新しいリストにprovinciaはありません。MySQLにとってprovinciasは別の値であって、1文字多いだけの同じ値ではないのです。リストにない値は保存できないので、そこで止まります。MariaDBもまったく同じ、同じコードで応答します。

よいことに、このALTERは何も中途半端に残しません。テーブルは元のまま、2行はprovinciaのままです。

厳格モードでないと、もっと悪い

このエラーが出るのは、サーバーが厳格モード（sql_modeのSTRICT_TRANS_TABLES）だからです。現在のMySQLとMariaDBは、デフォルトでこの設定になっています。厳格モードでないサーバーでは、同じALTERが「うまく」終わってしまいます。

mysql> SET SESSION sql_mode = '';
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE codigos MODIFY catalogo ENUM('paises', 'provincias', 'cantones') NOT NULL;
Query OK, 5 rows affected, 2 warnings (0.09 sec)
Records: 5  Duplicates: 0  Warnings: 2

mysql> SELECT id, catalogo, codigo, nombre FROM codigos;
+----+----------+--------+------------+
| id | catalogo | codigo | nombre     |
+----+----------+--------+------------+
|  1 | paises   | CR     | Costa Rica |
|  2 |          | 1      | San José   |
|  3 |          | 4      | Heredia    |
|  4 | cantones | 101    | San José   |
|  5 | cantones | 102    | Escazú     |
+----+----------+--------+------------+
5 rows in set (0.00 sec)

2つの県はカタログを失いました。空の値はENUMの位置0で、エンジンが無効だったものに使う位置です。テーブルには、それらの行が以前何だったのかがどこにも残っていません。残るのは2つの警告だけで、それを見るにはSHOW WARNINGSで要求しなければなりません。これとエラーなら、私は迷わずエラーのほうを選びます。

3ステップのレシピ

抜け出す方法は、MySQLに推測させないことです。まず新しい値を追加し、次に行を一方から他方へ移し、最後に古い値を取り除きます。

ALTER TABLE codigos MODIFY catalogo ENUM('paises', 'provincia', 'cantones', 'provincias') NOT NULL;

UPDATE codigos SET catalogo = 'provincias' WHERE catalogo = 'provincia';

ALTER TABLE codigos MODIFY catalogo ENUM('paises', 'provincias', 'cantones') NOT NULL;
対応エンジンMySQLMariaDB
  • 最初のALTERは、リストの末尾、provinciaと並べてprovinciasを追加します。末尾にするのは、そうすれば既存の値の位置が変わらず、リストが255個を超えないかぎり、エンジンはテーブルを書き直さずに列の定義を変えるだけで済むからです。
  • UPDATEは、行を一方の値から他方へ移します。両方が存在するので、今度はそれができます。
  • 2つ目のALTERはprovinciaを取り除きます。こちらはテーブルを書き直しますが、新しいリストにない値を持つ行はもうないので、切り詰めるものは何もありません。

最後のステップでは、provinciasを、provinciaがあった2番目に置きました。見た目のためではありません。ENUMはアルファベット順ではなくリスト内の位置で並ぶので、ORDER BY catalogoはリストの順にカタログを返すからです。

SELECT catalogo, COUNT(*) AS filas
FROM codigos
GROUP BY catalogo
ORDER BY catalogo;
対応エンジンMySQLMariaDB
+------------+-------+
| catalogo   | filas |
+------------+-------+
| paises     |     1 |
| provincias |     2 |
| cantones   |     2 |
+------------+-------+

provinciasを末尾に残していたら、県は市町村（カントン）の後に出てきたでしょう。

大きなテーブルでは、2つ目のALTERはテーブル全体をコピーするのと同じだけ時間がかかります。負荷の少ない時間帯に行うのがよいでしょう。

アクセント記号だけが変わるなら？

私の場合はsでした。もしアクセント記号を直す必要があったなら、話は違っていて、このレシピは使えなかったでしょう。créditoのアクセント記号が抜けている支払い方法のカタログの例です。

CREATE TABLE pagos (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    metodo ENUM('efectivo', 'tarjeta', 'credito') NOT NULL
);

INSERT INTO pagos (metodo) VALUES ('efectivo'), ('credito'), ('credito');

ALTER TABLE pagos MODIFY metodo ENUM('efectivo', 'tarjeta', 'crédito') NOT NULL;
対応エンジンMySQLMariaDB

直接のALTERは、エラーもなく、何も失わずに成功します。2行ともcréditoになります。理由は照合順序（collation）、つまりエンジンがテキストを比較するときの規則にあります。MySQLとMariaDBがデフォルトで使う照合順序は_ai_ciで終わり、アクセントの違い（accent-insensitive）も大文字と小文字の違い（case-insensitive）も区別しません。照合順序にとってcreditoとcréditoは同じ値なので、変換は新しいリストの中にそれを見つけます。Créditoでも同じです。

そして同じ理由で、3ステップのレシピは、2つの値を同時に持とうとする最初のステップで失敗します。

mysql> ALTER TABLE pagos MODIFY metodo ENUM('efectivo', 'tarjeta', 'credito', 'crédito') NOT NULL;
ERROR 1291 (HY000): Column 'metodo' has duplicated value 'credito' in ENUM

これはすべて列の照合順序しだいです。utf8mb4_binのようなバイナリの照合順序では、アクセント記号もほかの文字と同じように数えられます。直接のALTERはsのときと同じくエラー1265になり、3ステップのレシピが使えます。ENUMを直す前に、私はSHOW CREATE TABLEの結果で列の照合順序を確認します。列で宣言されていなければ、テーブルの照合順序を見ます。

PostgreSQLなら1行

PostgreSQLではENUMは別に作る型で、バージョン10からは、値の名前変更がUPDATEなしで、行にも触れずに、1つの文で済みます。

CREATE TYPE catalogo AS ENUM ('paises', 'provincia', 'cantones');

ALTER TYPE catalogo RENAME VALUE 'provincia' TO 'provincias';
対応エンジンPostgreSQL 10+

PostgreSQLにないのはその逆で、型を作り直さずにENUMから値を取り除く方法はありません。

3つの文

今では考えずに書ける3つの文です。あの日は何時間もかかりました。だからここに残しておきます。エラー1265に出会った人が、これを読むだけで済むように。

すべての記事