MySQL 8.0.16にCHECK制約が来て、NOT ENFORCEDなんてものまでついてきた

TL;DR

• MySQL 8.0.16 についに CHECK制約 が実装された
• NOT ENFORCED なんてオプションが “MySQLっぽい”
• ちなみにCHECK制約の追加はオンラインDDL不可、引っぺがすのはできる

---

最初のFeature Requestから15年、ついにMySQL 8.0.16にCHECK制約が実装された。

• MySQL Bugs: #3464: Constraints: support CHECK

アルファベットしか入ってほしくない val カラムに対して、ひらがなの “さん” とかが入力されるのを制限できる、みたいなヤーツ。

フツーの遊び方は @taka_yuki_04 さんの記事に書いてある（というかドキュメントにも書いてある）ので、俺は「既存のテーブルにCHECK制約」ででも遊んでみる。

• 【祝】MySQL8.0.16登場！MySQLはその先に向かう。 - 41から始めました

まず、こんなテーブルがあるじゃろ？

mysql80 8> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.16    |
+-----------+
1 row in set (0.00 sec)

mysql80 8> CREATE TABLE t1 (num serial, val varchar(32));
Query OK, 0 rows affected (0.02 sec)

mysql80 8> INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'さん');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql80 8> SELECT * FROM t1;
+-----+--------+
| num | val    |
+-----+--------+
|   1 | one    |
|   2 | two    |
|   3 | さん   |
+-----+--------+
3 rows in set (0.00 sec)

val にアルファベット以外（ val RLIKE '^[a-z]+$' を満たさない）を入れさせないようなCHECK制約をかけようとするじゃろ？

mysql80 8> ALTER TABLE t1 ADD CHECK(val RLIKE '^[a-z]+$');
ERROR 3819 (HY000): Check constraint 't1_chk_1' is violated.

既に制約違反しているから ALTER TABLE がエラーになるんじゃ。
という訳で制約に違反している行を消すじゃろ？

mysql80 8> DELETE FROM t1 WHERE num = 3;
Query OK, 1 row affected (0.00 sec)

mysql80 8> SELECT * FROM t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
|   2 | two  |
+-----+------+
2 rows in set (0.00 sec)

すると通るんじゃ。

mysql80 8> ALTER TABLE t1 ADD CHECK(val RLIKE '^[a-z]+$');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql80 8> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`),
  CONSTRAINT `t1_chk_1` CHECK (regexp_like(`val`,_utf8mb4'^[a-z]+$'))
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

CHECK制約があれば、 “さん” はそもそも入らないんじゃ。

mysql80 8> INSERT INTO t1 VALUES (3, 'さん');
ERROR 3819 (HY000): Check constraint 't1_chk_1' is violated.

mysql80 8> SELECT * FROM t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
|   2 | two  |
+-----+------+
2 rows in set (0.00 sec)

Σ(ﾟдﾟlll) フツーのDBMSっぽい！

しかしまあ、MySQLっぽいと言われる謎の NOT ENFORCED なんて句がついてきて

mysql80 8> ALTER TABLE t1 DROP CHECK t1_chk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 8> ALTER TABLE t1 ADD CHECK(val RLIKE '^[a-z]+$') NOT ENFORCED;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 8> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`),
  CONSTRAINT `t1_chk_1` CHECK (regexp_like(`val`,_utf8mb4'^[a-z]+$')) /*!80016 NOT ENFORCED */
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql80 8> INSERT INTO t1 VALUES (3, 'さん');
Query OK, 1 row affected (0.01 sec)

mysql80 8> SELECT * FROM t1;
+-----+--------+
| num | val    |
+-----+--------+
|   1 | one    |
|   2 | two    |
|   3 | さん   |
+-----+--------+
3 rows in set (0.00 sec)

:(；ﾞﾟ’ωﾟ’): なんと入る
誰得なんだろう…せめてワーニングとか出てくれるといいんだけどな。。

ところで、 ALTER TABLE でCHECK制約を追加するということは、それってオンラインでできるの的な疑問が湧いてくるんですが、2019年4月25日 20時10分02秒 JST 現在、InnoDBのオンラインDDL関連のページにはCHECK制約については特に記載なし。

• MySQL :: MySQL 8.0 Reference Manual :: 15.12.1 Online DDL Operations

mysql80 20> ALTER TABLE t1 ADD CHECK ('ワレワレ' <> '宇宙人'), ALGORITHM= INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY.

mysql80 20> ALTER TABLE t1 ADD CHECK ('ワレワレ' <> '宇宙人'), ALGORITHM= INPLACE;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.

mysql80 20> ALTER TABLE t1 ADD CHECK ('ワレワレ' <> '宇宙人'), ALGORITHM= COPY, LOCK= NONE;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

mysql80 20> ALTER TABLE t1 ADD CHECK ('ワレワレ' <> '宇宙人'), ALGORITHM= COPY, LOCK= SHARED;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql80 20> ALTER TABLE t1 ADD CHECK ('ワレワレ' <> '宇宙人'), ALGORITHM= COPY, LOCK= EXCLUSIVE;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

試した感じ、 ALGORITHM= COPY （テーブルコピー）で LOCK= SHARED （共有ロック、読めるけど書けない）か LOCK = EXCLUSIVE （排他ロック、読めないし書けない）しか選べない模様。。

このへん、せめてINPLACEとかできるようになるといいな（いくら全行チェックが必要とはいえ、テーブルコピーする必要はないはず…）

​