Movatterモバイル変換

[0]ホーム
URL:

GA

2019/04/26

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制約が実装された。
アルファベットしか入ってほしくない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: 0mysql80 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: 0mysql80 8> SHOW CREATE TABLE t1\G*************************** 1. row ***************************       Table: t1Create 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_ci1 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: 0mysql80 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: 0mysql80 8> SHOW CREATE TABLE t1\G*************************** 1. row ***************************       Table: t1Create 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_ci1 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: 0mysql80 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とかできるようになるといいな（いくら全行チェックが必要とはいえ、テーブルコピーする必要はないはず…）
​
投稿者時刻:
ラベル: ,

0 件のコメント :

コメントを投稿

登録:コメントの投稿 ( Atom )
[8]ページ先頭
©2009-2025 Movatter.jp