MySQL 8.0 でのチェック制約の実装

みなさんこんにちは。私は技術の話ばかりして髪を切らない先生のトニーです。今回は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> バージョンを選択します();
+-----------+
| バージョン() |
+-----------+
| 8.0.15 |
+-----------+
セット内の 1 行 (0.00 秒)

mysql> テーブル t1 を作成します
  -> (
  -> c1 INT チェック (c1 > 10)、
  -> c2 INT 、
  -> c3 INT チェック (c3 < 100)、
  -> 制約 c2_positive チェック (c2 > 0)、
  -> チェック (c1 > c3)
  -> );
クエリは正常、影響を受けた行は 0 行 (0.33 秒)

mysql> テーブル t1\G の作成を表示します
************************** 1. 行 ****************************
    表: t1
テーブルの作成: CREATE TABLE `t1` (
 `c1` int(11) デフォルト NULL,
 `c2` int(11) デフォルト NULL,
 `c3` int(11) デフォルト NULL
) エンジン=InnoDB デフォルト文字セット=utf8mb4 照合=utf8mb4_0900_ai_ci
セット内の 1 行 (0.00 秒)

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

mysql> t1(c1, c2, c3) に値(1, -1, 100) を挿入します。
クエリは正常、1 行が影響を受けました (0.06 秒)

MySQL 8.0.15 より前に同様のチェック制約を実装する場合は、トリガーを使用するか、WITH CHECK OPTION オプションを使用してビューを作成し、そのビューを通じてデータを挿入または変更します。

MySQL 8.0.16以降

MySQL 8.0.16 は 2019 年 4 月 25 日にリリースされ、ついにすべてのストレージ エンジンに有効な待望の CHECK 制約機能が導入されました。 CREATE TABLE ステートメントでは、列レベルの制約とテーブル レベルの制約の両方を指定できる次の形式の CHECK 制約構文を使用できます。

[制約 [シンボル]] CHECK (式) [[NOT] ENFORCED]

オプションのシンボル パラメータは、制約に名前を割り当てるために使用されます。このオプションを省略すると、MySQL はテーブル名に _chk_ と数値 (1、2、3、...) を加えた名前 (table_name_chk_n) を生成します。制約名の最大長は 64 文字で、大文字と小文字が区別されます。

expr は制約の条件を指定するブール式です。テーブル内の各データ行は、expr を満たして TRUE または UNKNOWN (NULL) と評価される必要があります。式が FALSE と評価された場合、制約に違反します。

オプションの ENFORCED 句は、制約を適用するかどうかを指定します。

• ENFORCED を省略または指定すると、制約が作成され、適用されます。
• NOT ENFORCED が指定されている場合、制約は作成されますが、強制されません。これは、制約が有効にならないことも意味します。

CHECK 制約は、列レベルまたはテーブル レベルで指定できます。

列レベルのチェック制約

列レベルの制約はフィールド定義の後にのみ表示でき、そのフィールドに対してのみ制約できます。例えば：

mysql> バージョンを選択します();
+-----------+
| バージョン() |
+-----------+
| 8.0.16 |
+-----------+
セット内の 1 行 (0.00 秒)

mysql> テーブル t1 を作成します
  -> (
  -> c1 INT チェック (c1 > 10)、
  -> c2 INT 制約 c2_positive チェック (c2 > 0)、
  -> c3 INT チェック (c3 < 100)
  -> );
クエリは正常、影響を受けた行は 0 行 (0.04 秒)

mysql> テーブル t1\G の作成を表示します
************************** 1. 行 ****************************
    表: t1
テーブルの作成: CREATE TABLE `t1` (
 `c1` int デフォルト NULL,
 `c2` int デフォルト NULL,
 `c3` int デフォルト NULL,
 制約 `c2_positive` チェック ((`c2` > 0))、
 制約 `t1_chk_1` チェック ((`c1` > 10))、
 制約 `t1_chk_2` チェック ((`c3` < 100))
) エンジン=InnoDB デフォルト文字セット=utf8
セット内の 1 行 (0.00 秒)

フィールド c1 および c3 のチェック制約ではシステム生成名が使用され、c2 のチェック制約ではカスタム名が使用されます。

SQL 標準のすべての制約 (主キー、一意制約、外部キー、チェック制約など) は同じ名前空間に属しているため、同じ名前を付けることはできません。しかし、MySQL では、データベース内の各制約タイプは独自の名前空間に属しているため、主キーとチェック制約に同じ名前を付けることはできますが、2 つのチェック制約に同じ名前を付けることはできません。

テストデータを挿入します:

mysql> t1(c1, c2, c3) に値(1, -1, 100) を挿入します。
エラー 3819 (HY000): チェック制約 'c2_positive' に違反しています。

挿入されたデータの 3 つのフィールドはすべて制約に違反しています。結果は c2_positive が違反していることを示しています。名前で最初にランク付けされているため、MySQL は名前の順に制約をチェックしていることがわかります。

別のテストデータを挿入してみましょう:

mysql> t1(c1, c2, c3) に値(null, null, null) を挿入します。
クエリは正常、1 行が影響を受けました (0.00 秒)

データは正常に挿入されたため、NULL 値はチェック制約に違反しません。

テーブルレベルのチェック制約

テーブル レベルの制約はフィールド定義とは独立しており、フィールド定義の前でも複数のフィールドに適用できます。例えば：

mysql> テーブル t1 を削除します。
クエリは正常、影響を受けた行は 0 行 (0.04 秒)

mysql> テーブル t1 を作成します
  -> (
  -> チェック (c1 <> c2)、
  -> c1 INT、
  -> c2 INT、
  -> c3 INT、
  -> 制約 c1_nonzero チェック (c1 <> 0)、
  -> チェック (c1 > c3)
  -> );
クエリは正常、影響を受けた行は 0 行 (0.04 秒)

mysql> テーブル t1\G の作成を表示します
************************** 1. 行 ****************************
    表: t1
テーブルの作成: CREATE TABLE `t1` (
 `c1` int デフォルト NULL,
 `c2` int デフォルト NULL,
 `c3` int デフォルト NULL,
 制約 `c1_nonzero` チェック ((`c1` <> 0))、
 制約 `t1_chk_1` チェック ((`c1` <> `c2`)),
 制約 `t1_chk_2` チェック ((`c1` > `c3`))
) エンジン=InnoDB デフォルト文字セット=utf8
セット内の 1 行 (0.00 秒)

最初の制約 t1_chk_1 はフィールド定義の前に表示されますが、c1 と c2 を参照できます。2 番目の制約 c1_nonzero はカスタム名を使用し、3 番目の制約 t1_chk_2 はすべてのフィールド定義の後に表示されます。

いくつかのテストデータも挿入します。

mysql> t1(c1, c2, c3) に値(1, 2, 3) を挿入します。
エラー 3819 (HY000): チェック制約 't1_chk_2' に違反しています。

mysql> t1(c1, c2, c3) に値(null, 2, 3) を挿入します。
クエリは正常、1 行が影響を受けました (0.01 秒)

最初のレコードでは、c1 は c3 より小さいため、チェック制約 t1_chk_2 に違反します。2 番目のレコードでは、c1 は NULL であり、チェック制約 t1_chk_2 の結果は UNKNOWN であるため、制約に違反しません。

必須オプション

デフォルト モードまたは ENFORCED オプションを使用して作成された制約は、必須チェック状態になります。チェックを無視するために、NOT ENFORCED に変更することもできます。

ALTER TABLE tbl_name
ALTER {CHECK | CONSTRAINT} シンボル [NOT] ENFORCED

変更されたチェック制約は引き続き存在しますが、チェックは実行されません。例えば：

mysql> テーブル t1 を変更する 
  -> alter check t1_chk_1 は強制されません。
クエリは正常、影響を受けた行は 0 行 (0.02 秒)
レコード: 0 重複: 0 警告: 0

mysql> テーブル t1\G の作成を表示します
************************** 1. 行 ****************************
    表: t1
テーブルの作成: CREATE TABLE `t1` (
 `c1` int デフォルト NULL,
 `c2` int デフォルト NULL,
 `c3` int デフォルト NULL,
 制約 `c1_nonzero` チェック ((`c1` <> 0))、
 制約 `t1_chk_1` チェック ((`c1` <> `c2`)) /*!80016 強制されません */,
 制約 `t1_chk_2` チェック ((`c1` > `c3`))
) エンジン=InnoDB デフォルト文字セット=utf8
セット内の 1 行 (0.00 秒)

最新の定義から、t1_chk_1 が NOT ENFORCED 状態にあることがわかります。この制約に違反するデータを挿入します。

mysql> t1(c1, c2, c3) に値(1, 1, 0) を挿入します。
クエリは正常、1 行が影響を受けました (0.01 秒)

このレコードの c1 と c2 は等しいですが、挿入は成功します。

下位バージョンの履歴データを移行する必要がある場合、新しいチェック制約に違反する可能性があります。この場合、最初に制約を無効にし、データが移行されて処理された後に強制オプションを再度有効にすることができます。

制約の制限をチェックする

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 標準にさらに準拠するようになります。

MySQL 8.0 の新機能であるチェック制約の実装に関する記事はこれで終わりです。MySQL 8.0 のチェック制約の詳細については、123WORDPRESS.COM の過去の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。