MySQL 8.0新しいプロパティのチェック制約(CHECK)

文書ディレクトリ

MySQL 8.0.15より前の

MySQL 8.0.16以降

列レベル検査制約

表レベル検査制約

強制オプション

検査制約

まとめ

皆さん、こんにちは、私は技術だけを話して髪を切らないトニー先生です.今回はMySQL 8.0に追加された新しい機能:チェック制約(CHECK)を紹介します.
SQLのチェック制約は完全性制約の一種であり、制約テーブルのフィールドまたは一部のフィールドが条件を満たす必要がある場合に使用できます.たとえば、ユーザー名は大文字でなければなりません.残高はゼロ以下ではありません.
Oracle、SQL Server、PostgreSQL、SQLiteなど、一般的なデータベースではチェック制約が実装されています.しかし、MySQLは、最新のMySQL 8.0.16までこの機能を実装していません.
MySQL 8.0.15より前
MySQL 8.0.15および以前のバージョンでは、CREATE TABLE文ではCHECK (expr)形式の制約構文のチェックが許可されていましたが、実際に解析すると、この句は無視されます.たとえば

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.15    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1
    -> (
    ->   c1 INT CHECK (c1 > 10),
    ->   c2 INT ,
    ->   c3 INT CHECK (c3 < 100),
    ->   CONSTRAINT c2_positive CHECK (c2 > 0),
    ->   CHECK (c1 > c3)
    -> );
Query OK, 0 rows affected (0.33 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

さまざまなCHECKオプションを定義時に指定しましたが、最終的なテーブル構造にはチェック制約は含まれません.これは、不正なデータを挿入できることを意味します.

mysql> insert into t1(c1, c2, c3) values(1, -1, 100);
Query OK, 1 row affected (0.06 sec)

MySQL 8.0.15の前に同様のチェック制約を実装したい場合は、トリガを使用します.または、WITH CHECK OPTIONオプションを含むビューを作成し、ビューを介してデータを挿入または変更します.
MySQL 8.0.16以降
MySQL 8.0.16は2019年4月25日にリリースされ、ついに私たちが期待していたCHECK制約機能をもたらし、すべてのストレージエンジンに有効になりました.CREATE TABLE文では、カラム制約とテーブル制約を指定できるCHECK制約構文の形式を使用できます.

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

オプションのsymbolパラメータを使用して、コンストレイントに名前を指定します.このオプションを省略すると、MySQLはテーブル名の先頭に_を付けます.chk_ 数字番号(1、2、3...)からなる名前(table_name_chk_n).コンストレイント名は最大64文字で、大文字と小文字を区別します.
exprは、制約の条件を指定するためのブール式です.テーブル内の各ローのデータは、exprの結果がTRUEまたはUNKNOWN(NULL)であることを満たさなければならない.式の結果がFALSEの場合、制約に違反します.
オプションのENFORCED句は、制約を強制するかどうかを指定します.

ENFORCEDが無視または指定されている場合、制約が作成され、強制されます.

NOT ENFORCEDが指定されている場合、コンストレイントは作成されますが強制されません.これは、コンストレイントが有効にならないことを意味します.

CHECK制約はカラムレベルでもテーブルレベルでも指定できます.
列レベルチェック制約
カラムレベルコンストレイントは、フィールド定義の後にのみ表示され、このフィールドに対してのみコンストレイントされます.例:

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.16    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1
    -> (
    ->   c1 INT CHECK (c1 > 10),
    ->   c2 INT CONSTRAINT c2_positive CHECK (c2 > 0),
    ->   c3 INT CHECK (c3 < 100)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int DEFAULT NULL,
  `c2` int DEFAULT NULL,
  `c3` int DEFAULT NULL,
  CONSTRAINT `c2_positive` CHECK ((`c2` > 0)),
  CONSTRAINT `t1_chk_1` CHECK ((`c1` > 10)),
  CONSTRAINT `t1_chk_2` CHECK ((`c3` < 100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ここで、フィールドc 1およびc 3のチェック制約は、システム生成の名前を使用する.c 2のチェックコンストレイントにはカスタム名が使用されます.
SQL規格のすべての制約(プライマリ・キー、一意制約、外部キー、チェック制約など)は、同じネーミング・スペースに属し、互いに名前を変更できないことを意味します.ただし、MySQLでは、各データベースの制約タイプは独自のネーミングスペースに属します.したがって、プライマリ・キーとチェック・コンストレイントは名前を変更できますが、2つのチェック・コンストレイントは名前を変更できません.
テストデータを挿入します.

mysql> insert into t1(c1, c2, c3) values(1, -1, 100);
ERROR 3819 (HY000): Check constraint 'c2_positive' is violated.

データを挿入する3つのフィールドはすべて制約に違反し、結果はc 2_に違反していることを示します.positive;名前順で1位なので、MySQLが制約された名前順にチェックされていることもわかります.
テストデータをもう1つ挿入します.

mysql> insert into t1(c1, c2, c3) values(null, null, null);
Query OK, 1 row affected (0.00 sec)

データ挿入に成功したため、NULL値はチェック制約に違反しません.
表レベルの検査制約
表レベル制約は、フィールドの定義とは独立しており、複数のフィールドに対して制約を行うことができ、フィールド定義の前にも表示されます.例:

mysql> drop table t1;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE t1
    -> (
    ->   CHECK (c1 <> c2),
    ->   c1 INT,
    ->   c2 INT,
    ->   c3 INT,
    ->   CONSTRAINT c1_nonzero CHECK (c1 <> 0),
    ->   CHECK (c1 > c3)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int DEFAULT NULL,
  `c2` int DEFAULT NULL,
  `c3` int DEFAULT NULL,
  CONSTRAINT `c1_nonzero` CHECK ((`c1` <> 0)),
  CONSTRAINT `t1_chk_1` CHECK ((`c1` <> `c2`)),
  CONSTRAINT `t1_chk_2` CHECK ((`c1` > `c3`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

最初の制約t 1_chk_1フィールド定義の前に表示されますが、c 1とc 2を参照できます.2番目のコンストレイントc 1_nonzeroはカスタム名を使用しています.3番目の制約t 1_chk_2すべてのフィールド定義の後.
テストデータもいくつか挿入します.

mysql> insert into t1(c1, c2, c3) values(1, 2, 3);
ERROR 3819 (HY000): Check constraint 't1_chk_2' is violated.

mysql> insert into t1(c1, c2, c3) values(null, 2, 3);
Query OK, 1 row affected (0.01 sec)

第1条記録中のc 1はc 3より小さく、検査制約t 1_に違反しているchk_2;第2条記録中のc 1はNULLであり、検査制約t 1_chk_2の結果はUNKNOWNであり、違法に拘束されない.
強制オプション
デフォルトまたはENFORCEDオプションを使用して作成された制約は、チェックを無視するためにNOT ENFORCEDに変更することもできます.

ALTER TABLE tbl_name
ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED

変更後もチェックコンストレイントは存在しますが、チェックは実行されません.例:

mysql> alter table t1 
    -> alter check t1_chk_1 not enforced;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int DEFAULT NULL,
  `c2` int DEFAULT NULL,
  `c3` int DEFAULT NULL,
  CONSTRAINT `c1_nonzero` CHECK ((`c1` <> 0)),
  CONSTRAINT `t1_chk_1` CHECK ((`c1` <> `c2`)) /*!80016 NOT ENFORCED */,
  CONSTRAINT `t1_chk_2` CHECK ((`c1` > `c3`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

最新の定義から、t 1_chk_1 NOT ENFORCED状態です.この制約に違反するデータを挿入します.

mysql> insert into t1(c1, c2, c3) values(1, 1, 0);
Query OK, 1 row affected (0.01 sec)

このレコードのc 1とc 2は等しいが、挿入に成功した.
低バージョンの履歴データを移行する必要がある場合は、新しいチェック制約に違反する可能性があります.この場合、コンストレイントを無効にして、データの移行と処理が完了したら、強制オプションを再度有効にできます.
制約のチェック
MySQLのCHECK条件式は、次のルールを満たす必要があります.そうしないと、チェック制約を作成できません.

非計算列および計算列の使用は許可されていますが、AUTO_の使用は許可されていません.INCREMENTフィールドまたは他のテーブルのフィールド.

では、異なるユーザであっても、同じ結果を入力するだけで同じ関数を複数回呼び出すワード値、決定的な組み込み関数、および演算子を使用できます.不確実性関数には、CONNECTION_が含まれます.ID()、CURRENT_USER()やNOW()など、コンストレイントのチェックには使用できません.

では、ストレージ関数またはカスタム関数の使用は許可されていません.

では、ストレージ・プロシージャおよび関数パラメータの使用は許可されません.

は、システム変数、ユーザ定義変数、およびストレージプログラムのローカル変数を含む変数の使用を許可しない.

では、サブクエリの使用は許可されていません.

また、CHECK制約フィールドで外部キー制約を定義する参照操作(ON UPDATE、ON DELETE)を無効にする.同様に、外部キーコンストレイント参照操作があるフィールドでもCHECKコンストレイントの作成は許可されません.
INSERT、UPDATE、REPLACE、LOAD DATA、およびLOAD XML文では、チェック制約に違反するとエラーが返されます.この場合、変更されたデータ処理は、ストレージエンジンがトランザクションをサポートしているかどうか、および厳格なSQLモードが使用されているかどうかによって異なります.
INSERT IGNORE、UPDATE IGNORE、REPLACE、LOAD DATA...IGNORE、およびLOAD XML...IGNORE文の場合、チェック制約に違反すると警告が返され、問題のあるデータ行がスキップされます.
制約式の結果タイプとフィールドのデータ型が異なる場合、MySQLは暗黙的なタイプ変換を実行します.タイプ変換に失敗したり、精度が失われたりすると、エラーが返されます.
まとめ
MySQL 8.0.16に追加された検査制約は、MySQLがビジネス整合性制約を実現する能力を向上させ、MySQLがSQL基準に従うようになりました.
文章があなたに役に立つと思ったら、無駄に買わないでください.注目を集める❤️、コメント、いいね!