MySQL 8.0.16 の CHECK 制約について検証してみた

こんにちは。サイオステクノロジー OSS サポート担当 Y です。

今回は MySQL 8.0.16 で実装された CHECK 制約について検証してみました。(※以下の内容は MySQL 8.0.15/MySQL 8.0.16 にて検証しています。)

■はじめに

MySQL 8.0 では、様々な新機能が追加されています。

普段の業務では PostgreSQL を扱うことが多いのですが、最近 MySQL に CHECK 制約が実装されたという話を聞いたので、今回はこの CHECK 制約について検証してみました。

■MySQL 8.0.15 (CHECK 制約未実装) にて検証

最初に、MySQL 8.0.15 (CHECK 制約未実装のバージョン) で検証してみます。

以下の様に CHECK 制約を指定してテーブルを作成してみます。(※t1.a に対して “0 < a AND a <= 3" という CHEKC 制約を指定しています。)

mysql> SELECT version();
+———–+
| version() |
+———–+
| 8.0.15    |
+———–+
1 row in set (0.00 sec)

mysql> 
mysql> CREATE TABLE t1 (a INT CHECK (0 < a AND a <= 3));
Query OK, 0 rows affected (0.10 sec)

mysql> 

すると、エラーにはならずにテーブルを作成することができました。

しかし、SHOW CREATE TABLE コマンドにてテーブルの定義を確認してみると、CHECK 制約の設定は反映されていない様に見えます。

mysql> SHOW CREATE TABLE t1;
+-------+-------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> 

このテーブル t1 に対して、実際に値を INSERT してみます。

まずは、CHECK 制約に指定した条件 “0 < a AND a <= 3" の範囲内の値を INSERT してみます。

mysql> INSERT INTO t1 VALUES (1), (2), (3);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT * FROM t1;
+——+
| a    |
+——+
|    1 |
|    2 |
|    3 |
+——+
3 rows in set (0.01 sec)

mysql> 

特に問題なく実行できました。これは想定通りの動作です。

次に、CHECK 制約に指定した条件 “0 < a AND a <= 3" の範囲外の値を INSERT してみます。

mysql> INSERT INTO t1 VALUES (0);
Query OK, 1 row affected (0.02 sec)

mysql> 
mysql> INSERT INTO t1 VALUES (4);
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> SELECT * FROM t1;
+——+
| a    |
+——+
|    1 |
|    2 |
|    3 |
|    0 |
|    4 |
+——+
5 rows in set (0.00 sec)

mysql> 

すると、テーブル定義時の DDL 文に指定している CHECK 制約の範囲外の値でもテーブルに INSERT することができました。

これが、MySQL 8.0.15 までの仕様動作のようです。

■MySQL 8.0.16 (CHECK 制約実装) にて検証

次に、MySQL 8.0.16 で検証してみます。

先程と同じ様に、CHECK 制約を指定してテーブルを作成します。(※t1.a に対して “0 < a AND a <= 3" という CHEKC 制約を指定しています。)

mysql> SELECT version();
+———–+
| version() |
+———–+
| 8.0.16    |
+———–+
1 row in set (0.00 sec)

mysql> 
mysql> CREATE TABLE t1 (a INT CHECK (0 < a AND a <= 3));
Query OK, 0 rows affected (0.10 sec)

mysql> 

テーブル作成後、SHOW CREATE TABLE コマンドにてテーブルの定義を確認してみると、CHECK 制約の設定 “CONSTRAINT `t1_chk_1` CHECK (((0 < `a`) and (`a` <= 3)))" が反映されています。

mysql> SHOW CREATE TABLE t1;
+——-+——————————————————————————————————————————————————————————-+
| Table | Create Table                                                                                                                                                                  |
+——-+——————————————————————————————————————————————————————————-+
| t1    | CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  CONSTRAINT `t1_chk_1` CHECK (((0 < `a`) and (`a` <= 3)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> 

先程と同じ様に、このテーブル t1 に対して実際に値を INSERT してみます。

まずは、CHECK 制約に指定した条件 “0 < a AND a <= 3" の範囲内の値を INSERT してみます。

mysql> INSERT INTO t1 VALUES (1), (2), (3);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT * FROM t1;
+——+
| a    |
+——+
|    1 |
|    2 |
|    3 |
+——+
3 rows in set (0.01 sec)

mysql> 

特に問題なく INSERT できました。

次に、CHECK 制約に指定した条件 “0 < a AND a <= 3" の範囲外の値を INSERT してみます。

mysql> INSERT INTO t1 VALUES (0);
ERROR 3819 (HY000): Check constraint ‘t1_chk_1’ is violated.
mysql> 
mysql> INSERT INTO t1 VALUES (4);
ERROR 3819 (HY000): Check constraint ‘t1_chk_1’ is violated.
mysql> 
mysql> SELECT * FROM t1;
+——+
| a    |
+——+
|    1 |
|    2 |
|    3 |
+——+
3 rows in set (0.00 sec)

mysql> 

すると、先程とは異なり “Check constraint ‘t1_chk_1’ is violated.” というエラーが出力されました。

実際に t1 のレコードを確認すると、エラーになった “0” や “4” の値は INSERT されていないことが確認できます。

上記の通り、MySQL 8.0.16 では CHECK 制約が実装されていることが確認できました。

■最後に

今回は MySQL 8.0.16 で実装された CHECK 制約について検証してみました。

MySQL 8.0 では様々な新機能が実装されているので、次回も MySQL 8.0 の新機能の検証を実施してみようかと思います。

About サイオステクノロジーの中の人 41 Articles

サイオステクノロジーで働く中の人です。

Twitter YouTube