日々の覚書: MySQL 5.7.6のgenerated columnは関数インデックスの夢を見るか
というわけでやってみましょう。
mysql57> CREATE TABLE t1 (num int primary key, val varchar(32)) Engine= InnoDB; Query OK, 0 rows affected (0.03 sec) mysql57> INSERT INTO t1 (num, val) VALUES (1, '2015-08-06'); Query OK, 1 row affected (0.01 sec) mysql57> SELECT * FROM t1; +-----+------------+ | num | val | +-----+------------+ | 1 | 2015-08-06 | +-----+------------+ 1 row in set (0.00 sec)
こんなテーブルがあったとするじゃろ？
mysql57> ALTER TABLE t1 ADD check_val datetime AS (CAST(val AS datetime)); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
valの中身をdatetimeにキャストする関数を食わせたgenerated columnを作るじゃろ？
mysql57> INSERT INTO t1 (num, val) VALUES (2, '2015-09-31'); ERROR 1292 (22007): Incorrect datetime value: '2015-09-31' mysql57> SELECT * FROM t1; +-----+------------+---------------------+ | num | val | check_val | +-----+------------+---------------------+ | 1 | 2015-08-06 | 2015-08-06 00:00:00 | +-----+------------+---------------------+ 1 row in set (0.00 sec) mysql57> INSERT INTO t1 (num, val) VALUES (2, '2015-09-30'); Query OK, 1 row affected (0.00 sec) mysql57> SELECT * FROM t1; +-----+------------+---------------------+ | num | val | check_val | +-----+------------+---------------------+ | 1 | 2015-08-06 | 2015-08-06 00:00:00 | | 2 | 2015-09-30 | 2015-09-30 00:00:00 | +-----+------------+---------------------+ 2 rows in set (0.00 sec)
なんと、日付として不正な値を入れると、キャストに失敗してエラーが返るんじゃよ！
mysql57> SET sql_mode= ''; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql57> SELECT @@sql_mode; +------------+ | @@sql_mode | +------------+ | | +------------+ 1 row in set (0.00 sec) mysql57> INSERT INTO t1 (num, val) VALUES (3, '2015-11-31'); Query OK, 1 row affected, 1 warning (0.02 sec) mysql57> SHOW WARNINGS; +---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1292 | Incorrect datetime value: '2015-11-31' | +---------+------+----------------------------------------+ 1 row in set (0.00 sec) mysql57> SELECT * FROM t1; +-----+------------+---------------------+ | num | val | check_val | +-----+------------+---------------------+ | 1 | 2015-08-06 | 2015-08-06 00:00:00 | | 2 | 2015-09-30 | 2015-09-30 00:00:00 | | 3 | 2015-11-31 | NULL | +-----+------------+---------------------+ 3 rows in set, 1 warning (0.00 sec)
もちろん、sql_modeがSTRICT_TRANS_TABLESかSTRICT_ALL_TABLESでないと、キャストの失敗を丸めちゃうので台無しになる。まさかもう非strict_modeとか選ばない…よね？
datetime型の場合はNOT NULL制約をつけても0000-00-00 00:00:00で丸めちゃうからアレだけど、IFをかませてtinyintとかにすると
mysql57> CREATE TABLE t2 (num int primary key, val varchar(32), check_val tinyint AS (IF(val IN ('東京', '大阪'), 1, NULL)) NOT NULL) Engine= InnoDB;
Query OK, 0 rows affected (0.02 sec)
mysql57> INSERT INTO t2 (num, val) VALUES (1, '東京');
Query OK, 1 row affected (0.00 sec)
mysql57> INSERT INTO t2 (num, val) VALUES (2, '大阪');
Query OK, 1 row affected (0.01 sec)
mysql57> INSERT INTO t2 (num, val) VALUES (3, '奈良');
ERROR 1048 (23000): Column 'check_val' cannot be null
mysql57> SELECT * FROM t2;
+-----+--------+-----------+
| num | val | check_val |
+-----+--------+-----------+
| 1 | 東京 | 1 |
| 2 | 大阪 | 1 |
+-----+--------+-----------+
2 rows in set (0.00 sec)
おおおこれ結構良くない？ このIF関数で無理矢理NULLを返す方式だと、NOT NULL制約の問題なので非strict_modeでもちゃんとエラーになってくれる。わーい（死）
ちなみに、既に格納されている値に対して後からこの似非CHECK制約を追加する場合、
mysql57> SELECT * FROM t3;
+-----+--------+
| num | val |
+-----+--------+
| 1 | 東京 |
| 2 | 大阪 |
| 3 | 奈良 |
+-----+--------+
3 rows in set (0.00 sec)
mysql57> ALTER TABLE t3 ADD check_val tinyint AS (IF (val IN ('東京', '大阪'), 1, NULL)) NOT NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql57> SELECT * FROM t3;
+-----+--------+-----------+
| num | val | check_val |
+-----+--------+-----------+
| 1 | 東京 | 1 |
| 2 | 大阪 | 1 |
| 3 | 奈良 | 0 |
+-----+--------+-----------+
3 rows in set (0.00 sec)
mysql57> INSERT INTO t3 (num, val) VALUES (4, '京都');
ERROR 1048 (23000): Column 'check_val' cannot be null
generated columnの種類を指定しない場合、暗黙のデフォルトとしてVIRTUALになるので、
「VIRTUALなgenerated columnの場合は、今までのものは無視されて、新たに更新するものに対して有効」（VIRTUALなgenerated columnはALTER TABLEの時には何もせず、SELECTされた時に都度関数が走るから）になり、
mysql57> ALTER TABLE t3 DROP check_val;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql57> SELECT * FROM t3;
+-----+--------+
| num | val |
+-----+--------+
| 1 | 東京 |
| 2 | 大阪 |
| 3 | 奈良 |
+-----+--------+
3 rows in set (0.00 sec)
mysql57> ALTER TABLE t3 ADD check_val tinyint AS (IF (val IN ('東京', '大阪'), 1, NULL)) STORED NOT NULL;
ERROR 1048 (23000): Column 'check_val' cannot be null
「STOREDなgenerated columnの場合は、今までのもので似非CHECK制約を通らない行がある場合、そもそもALTER TABLEが通らない」（STOREDなgenerated columnはALTER TABLEの時に関数を通って計算され、それがデータに格納されるから）になる。
おおお面白いんじゃないこれ？
【2015/08/12 10:52】
I translated this post into English, See An idea for using MySQL 5.7's generated column like CHECK constraint
0 件のコメント :
コメントを投稿