TL;DR

MySQL 5.7とそれ以前では ALTER TABLE t1 CHANGE a b int .. とかやらないといけなかったのが、 ALTER TABLE t1 RENAME COLUMN a TO b でデータ型とか気にせずにいけるようになっていた
地味にうれしい
参考資料

今まではこう

mysql57 40> CREATE TABLE t1 (num int unsigned not null, val varchar(32) not null, PRIMARY KEY(num), FULLTEXT KEY (val) WITH PARSER ngram);
Query OK, 0 rows affected (0.06 sec)

mysql57 40> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(10) unsigned NOT NULL,
  `val` varchar(32) NOT NULL,
  PRIMARY KEY (`num`),
  FULLTEXT KEY `val` (`val`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql57 40> ALTER TABLE t1 CHANGE val abc varchar(32) NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57 40> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(10) unsigned NOT NULL,
  `abc` varchar(32) NOT NULL,
  PRIMARY KEY (`num`),
  FULLTEXT KEY `val` (`abc`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)

CHANGE の後にデータ型もつけなければいけなかったのが面倒だった。 NOT NULL をつけるのを忘れて死にかけたりした人も世の中には存在するんじゃなかろうか。

これがMySQL 8.0ではこうじゃ。

mysql80 24> CREATE TABLE t1 (num int unsigned not null, val varchar(32) not null, PRIMARY KEY(num), FULLTEXT KEY (val) WITH PARSER ngram);
Query OK, 0 rows affected (0.15 sec)

mysql80 24> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(10) unsigned NOT NULL,
  `val` varchar(32) COLLATE utf8mb4_ja_0900_as_cs NOT NULL,
  PRIMARY KEY (`num`),
  FULLTEXT KEY `val` (`val`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.00 sec)

mysql80 24> ALTER TABLE t1 RENAME COLUMN val TO abc;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 24> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(10) unsigned NOT NULL,
  `abc` varchar(32) COLLATE utf8mb4_ja_0900_as_cs NOT NULL,
  PRIMARY KEY (`num`),
  FULLTEXT KEY `val` (`abc`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.00 sec)

MySQL 5.7で追加されたALTER TABLE .. RENAME INDEX が好評だったんですかね！

ただし、generated columnがあると古い方のカラムを参照し続けるため ALTER TABLE が転ける。これは CHANGE でも転けるんだけれども。

mysql80 24> ALTER TABLE t1 ADD v_abc varchar(32) AS (MD5(abc)) NOT NULL;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 24> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(10) unsigned NOT NULL,
  `abc` varchar(32) COLLATE utf8mb4_ja_0900_as_cs NOT NULL,
  `v_abc` varchar(32) COLLATE utf8mb4_ja_0900_as_cs GENERATED ALWAYS AS (md5(`abc`)) VIRTUAL NOT NULL,
  PRIMARY KEY (`num`),
  FULLTEXT KEY `val` (`abc`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.00 sec)

mysql80 24> ALTER TABLE t1 RENAME COLUMN abc TO def;
ERROR 1054 (42S22): Unknown column 'abc' in 'generated column function'

mysql80 24> ALTER TABLE t1 CHANGE abc def varchar(32) NOT NULL;
ERROR 1054 (42S22): Unknown column 'abc' in 'generated column function'

綺麗に転けるならまだしも、 RENAME TABLE でたまにやるみたいにカラムをスワップさせようと思うと大変なことになる。

mysql80 24> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(10) unsigned NOT NULL,
  `abc` varchar(32) COLLATE utf8mb4_ja_0900_as_cs NOT NULL,
  `v_abc` varchar(32) COLLATE utf8mb4_ja_0900_as_cs GENERATED ALWAYS AS (md5(`abc`)) VIRTUAL NOT NULL,
  `def` varchar(32) COLLATE utf8mb4_ja_0900_as_cs DEFAULT NULL,
  PRIMARY KEY (`num`),
  FULLTEXT KEY `val` (`abc`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.00 sec)

mysql80 24> SELECT * FROM t1;
+-----+-----+----------------------------------+------+
| num | abc | v_abc                            | def  |
+-----+-----+----------------------------------+------+
|   1 | one | f97c5d29941bfb1b2fdab0874906ab82 | NULL |
+-----+-----+----------------------------------+------+
1 row in set (0.00 sec)

mysql80 24> ALTER TABLE t1 RENAME COLUMN abc TO def, RENAME COLUMN def TO abc;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 24> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(10) unsigned NOT NULL,
  `def` varchar(32) COLLATE utf8mb4_ja_0900_as_cs NOT NULL,
  `v_abc` varchar(32) COLLATE utf8mb4_ja_0900_as_cs GENERATED ALWAYS AS (md5(`abc`)) VIRTUAL NOT NULL,
  `abc` varchar(32) COLLATE utf8mb4_ja_0900_as_cs DEFAULT NULL,
  PRIMARY KEY (`num`),
  FULLTEXT KEY `val` (`def`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.00 sec)

mysql80 24> SELECT * FROM t1;
+-----+-----+-------+------+
| num | def | v_abc | abc  |
+-----+-----+-------+------+
|   1 | one |       | NULL |
+-----+-----+-------+------+
1 row in set (0.01 sec)

VIRTUAL generated columnはビューとかストアドと一緒で名前でしか参照されないので、「カラムが存在しない」以外ではエラーにならなさげ。

…あれ？これSTOREDだとどうなるんだ？

mysql80 24> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(10) unsigned NOT NULL,
  `abc` varchar(32) COLLATE utf8mb4_ja_0900_as_cs NOT NULL,
  `def` varchar(32) COLLATE utf8mb4_ja_0900_as_cs DEFAULT NULL,
  `v_abc` varchar(32) COLLATE utf8mb4_ja_0900_as_cs GENERATED ALWAYS AS (md5(`abc`)) STORED,
  PRIMARY KEY (`num`),
  FULLTEXT KEY `val` (`abc`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.00 sec)

mysql80 24> SELECT * FROM t1;
+-----+-----+------+----------------------------------+
| num | abc | def  | v_abc                            |
+-----+-----+------+----------------------------------+
|   1 | one | NULL | f97c5d29941bfb1b2fdab0874906ab82 |
+-----+-----+------+----------------------------------+
1 row in set (0.00 sec)

mysql80 24> ALTER TABLE t1 RENAME COLUMN abc TO def, RENAME COLUMN def TO abc;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 24> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(10) unsigned NOT NULL,
  `def` varchar(32) COLLATE utf8mb4_ja_0900_as_cs NOT NULL,
  `abc` varchar(32) COLLATE utf8mb4_ja_0900_as_cs DEFAULT NULL,
  `v_abc` varchar(32) COLLATE utf8mb4_ja_0900_as_cs GENERATED ALWAYS AS (md5(`abc`)) STORED,
  PRIMARY KEY (`num`),
  FULLTEXT KEY `val` (`def`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.00 sec)

mysql80 24> SELECT * FROM t1;
+-----+-----+------+----------------------------------+
| num | def | abc  | v_abc                            |
+-----+-----+------+----------------------------------+
|   1 | one | NULL | f97c5d29941bfb1b2fdab0874906ab82 |
+-----+-----+------+----------------------------------+
1 row in set (0.00 sec)

アッー！

ばぐれぽばぐれぽ…φ(･ω･`)

した。もうちょっとわかりやすいテストケースをつけてある。

MySQL Bugs: #92727: Swapping column by ALTER TABLE RENAME COLUMN breaks STORED generated column

ワークアラウンド: OPTIMIZE TABLE したら再計算されたようで直った

mysql80 24> OPTIMIZE TABLE t1;
+-------+----------+----------+-------------------------------------------------------------------+
| Table | Op       | Msg_type | Msg_text                                                          |
+-------+----------+----------+-------------------------------------------------------------------+
| d1.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| d1.t1 | optimize | status   | OK                                                                |
+-------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.18 sec)

mysql80 24> SELECT * FROM t1;
+-----+-----+------+-------+
| num | def | abc  | v_abc |
+-----+-----+------+-------+
|   1 | one | NULL | NULL  |
+-----+-----+------+-------+
1 row in set (0.00 sec)

日々の覚書

GA

2018/10/10

MySQL 8.0ではカラムのリネームに ALTER TABLE RENAME COLUMN 構文が使える

TL;DR

0 件のコメント :

コメントを投稿