TL;DR
- MySQL 8.0.16 についに CHECK制約 が実装された
NOT ENFORCEDなんてオプションが “MySQLっぽい”- ちなみにCHECK制約の追加はオンラインDDL不可、引っぺがすのはできる
最初のFeature Requestから15年、ついにMySQL 8.0.16にCHECK制約が実装された。
アルファベットしか入ってほしくない
val カラムに対して、ひらがなの “さん” とかが入力されるのを制限できる、みたいなヤーツ。
フツーの遊び方は @taka_yuki_04 さんの記事に書いてある（というかドキュメントにも書いてある）ので、俺は「既存のテーブルにCHECK制約」ででも遊んでみる。
まず、こんなテーブルがあるじゃろ？
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制約については特に記載なし。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とかできるようになるといいな（いくら全行チェックが必要とはいえ、テーブルコピーする必要はないはず…）
