MySQL 8.0 リファレンスマニュアル


5.1.11 サーバー SQL モード

MySQL Server は異なる SQL モードで動作でき、sql_mode システム変数の値に応じて異なるクライアントにこれらの異なるモードを適用できます。 DBA はサイトサーバーの動作要件に一致するグローバル SQL モードを設定でき、各アプリケーションはアプリケーションのセッション SQL モードをアプリケーション独自の要件に設定できます。

モードは MySQL がサポートする SQL 構文と、MySQL が実行するデータ検証に影響します。 これにより、MySQL をさまざまな環境で使用したり、MySQL をほかのデータベースサーバーと一緒に使用したりすることが、さらに容易になります。

MySQL のサーバー SQL モードについてのよくある質問に対する回答は、セクションA.3「MySQL 8.0 FAQ: サーバー SQL モード」を参照してください。

InnoDB テーブルを操作するとき、innodb_strict_mode システム変数についても考慮してください。 これによって、InnoDB テーブルの追加のエラー検査が可能になります。

SQL モードの設定

MySQL 8.0 のデフォルトの SQL モードには、次のモードが含まれます: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO および NO_ENGINE_SUBSTITUTION

サーバー起動時に SQL モードを設定するには、コマンド行で --sql-mode="modes" オプションを使用するか、my.cnf (Unix オペレーティングシステム) または my.ini (Windows) などのオプションファイル内で sql-mode="modes" を使用します。modes は、カンマで区切られるさまざまなモードのリストです。 SQL モードを明示的にクリアするには、コマンド行で --sql-mode="" を使用するかオプションファイル内で sql-mode="" を使用して、SQL モードを空の文字列に設定します。

注記

MySQL インストールプログラムはインストールプロセス中に SQL モードを構成することがあります。

SQL モードがデフォルトまたは期待されているモードと異なる場合、サーバーが起動時に読み取るオプションファイル内の設定を確認してください。

SQL モードを実行時に変更するには、SET ステートメントを使用して、グローバルまたはセッションの sql_mode システム変数を設定します。

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

GLOBAL 変数を設定するには、SYSTEM_VARIABLES_ADMIN 権限 (または非推奨の SUPER 権限) が必要であり、その時点から接続するすべてのクライアントの操作に影響します。 SESSION 変数を設定すると、現在のクライアントにのみ影響します。 すべてのクライアントは、自分のセッションの sql_mode 値をいつでも変更できます。

現在のグローバルまたはセッションの sql_mode 設定を確認するには、その値を選択します:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
重要

SQL モードおよびユーザー定義のパーティショニング.  パーティション化されたテーブルを作成してデータを挿入したあとでサーバー SQL モードを変更すると、このようなテーブルの動作が大きく変更される可能性があり、データが失われたり破損したりすることがあります。 ユーザー定義のパーティショニングを使用したテーブルを作成したら、SQL モードを変更しないことを強くお勧めします。

パーティションテーブルをレプリケートする場合、ソースとレプリカで異なる SQL モードを使用すると、問題が発生する可能性もあります。 最良の結果を得るには、ソースとレプリカで常に同じサーバー SQL モードを使用する必要があります。

詳細は、セクション24.6「パーティショニングの制約と制限」を参照してください。

もっとも重要な SQL モード

次に、多くの場合でもっとも重要な sql_mode 値を示します。

  • ANSI

    このモードは、構文および動作が標準の SQL にさらに緊密に準拠するように変更します。 これは、このセクションの末尾にリストされている、特殊な組み合わせモードの 1 つです。

  • STRICT_TRANS_TABLES

    値を指定したとおりにトランザクションテーブルに挿入できない場合、ステートメントを中止します。 非トランザクションテーブルの場合、値が単一行ステートメントで発生するか、複数行ステートメントの先頭行で発生した場合、ステートメントを中止します。 詳細については、このセクションのあとの方で説明します。

  • TRADITIONAL

    MySQL を 従来型の SQL データベースシステムのように動作させます。 このモードを簡単に説明すると、カラムに不正な値を挿入したときに警告ではなくエラーを返します。 これは、このセクションの末尾にリストされている、特殊な組み合わせモードの 1 つです。

    注記

    TRADITIONAL モードを有効にすると、エラーが発生するとすぐに INSERT または UPDATE が中断されます。 非トランザクションストレージエンジンを使用している場合、エラーの前に行われたデータ変更はロールバックされず、「一部完了」が更新される可能性があるため、これは望ましくない可能性があります。

このマニュアルの厳密モードとは、STRICT_TRANS_TABLES または STRICT_ALL_TABLES のいずれかあるいは両方が有効なモードを意味します。

SQL モードの完全なリスト

次のリストは、サポートされるすべての SQL モードについて説明しています。

  • ALLOW_INVALID_DATES

    日付の完全な検査を実行しません。 月が 1 から 12 までの範囲にあることと、日が 1 から 31 までの範囲にあることのみ検査します。 これは、3 つの異なるフィールドで年、月および日を取得し、日付検証なしでユーザーが挿入した内容を正確に格納する Web アプリケーションに役立ちます。 このモードは DATE および DATETIME カラムに適用されます。 TIMESTAMP カラムは有効な日付が常に必要なため、このカラムには適用されません。

    ALLOW_INVALID_DATES が無効になっている場合、サーバーでは月と日の値が有効である必要があり、それぞれ 1 から 12 および 1 から 31 の範囲内にあるだけではありません。 厳密モードが無効になっていると、'2004-04-31' のような無効な日付は '0000-00-00' に変換され、警告メッセージが表示されます。 厳密モードが有効なときは、無効な日付によってエラーが発生します。 このような日付を許可するには、ALLOW_INVALID_DATES を有効にします。

  • ANSI_QUOTES

    "は、文字列引用符文字としてではなく、識別子引用符文字 (`引用符文字など) として扱います。 このモードを有効にして、識別子を引用するために ` を引き続き使用できます。 ANSI_QUOTES が有効な場合、リテラル文字列は識別子として解釈されるため、二重引用符を使用して引用符を使用することはできません。

  • ERROR_FOR_DIVISION_BY_ZERO

    ERROR_FOR_DIVISION_BY_ZERO モードは、MOD(N,0) を含むゼロ除算の処理に影響します。 データ変更操作 (INSERTUPDATE) の場合、この効果は厳密 SQL モードが有効であるかどうかにもよります。

    • このモードが有効でない場合、ゼロによる除算は NULL を挿入し、警告は生成されません。

    • このモードが有効な場合、ゼロによる除算は NULL を挿入し、警告が生成されます。

    • このモードおよび厳密モードが有効な場合、ゼロによる除算はエラーを生成しますが、IGNORE も指定されている場合は例外です。 INSERT IGNORE および UPDATE IGNORE の場合、ゼロによる除算は NULL を挿入し、警告が生成されます。

    SELECT の場合、ゼロによる除算は NULL を返します。 ERROR_FOR_DIVISION_BY_ZERO を有効にすると、厳密モードが有効かどうかに関係なく警告も生成されます。

    ERROR_FOR_DIVISION_BY_ZERO は非推奨です。 ERROR_FOR_DIVISION_BY_ZERO は厳密モードの一部ではありませんが、厳密モードとともに使用する必要があり、デフォルトで有効になっています。 厳密モードも有効にせずに ERROR_FOR_DIVISION_BY_ZERO が有効になっている場合、またはその逆の場合は、警告が発生します。

    ERROR_FOR_DIVISION_BY_ZERO は非推奨であるため、将来の MySQL リリースでは、個別のモード名として削除され、厳密な SQL モードの影響に含まれることが予想されます。

  • HIGH_NOT_PRECEDENCE

    NOT 演算子の存在によって、NOT a BETWEEN b AND c のような式は NOT (a BETWEEN b AND c) として構文解析されます。 一部の古い MySQL バージョンでは、この式は (NOT a) BETWEEN b AND c として構文解析されます。 優先順位を高める以前の動作は、HIGH_NOT_PRECEDENCE の SQL モードを有効にすることによって取得できます。

    mysql> SET sql_mode = '';
    mysql> SELECT NOT 1 BETWEEN -5 AND 5;
            -> 0
    mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
    mysql> SELECT NOT 1 BETWEEN -5 AND 5;
            -> 1
  • IGNORE_SPACE

    関数名と ( 文字の間にスペースを許可します。 これにより、組み込み関数名が予約語として扱われます。 その結果、関数名と同じ識別子は、セクション9.2「スキーマオブジェクト名」に記載されているように引用符で囲む必要があります。 たとえば、COUNT() 関数があるため、次のステートメントで count をテーブル名として使用すると、エラーが発生します。

    mysql> CREATE TABLE count (i INT);
    ERROR 1064 (42000): You have an error in your SQL syntax

    テーブル名を引用符で囲んでください。

    mysql> CREATE TABLE `count` (i INT);
    Query OK, 0 rows affected (0.00 sec)

    IGNORE_SPACE SQL モードは、ユーザー定義関数またはストアドファンクションではなく、組み込み関数に適用されます。 IGNORE_SPACE が有効かどうかにかかわらず、UDF またはストアドファンクション名のあとにスペースを入れることが常に許可されます。

    IGNORE_SPACE に関する詳細は、セクション9.2.5「関数名の構文解析と解決」を参照してください。

  • NO_AUTO_VALUE_ON_ZERO

    NO_AUTO_VALUE_ON_ZEROAUTO_INCREMENT カラムの処理に影響します。 通常は、NULL または 0 をカラムに挿入することによって、カラムの次のシーケンス番号を生成します。 NO_AUTO_VALUE_ON_ZERO0 のこの動作を抑制するため、NULL のみが次のシーケンス番号を生成します。

    このモードは、テーブルの AUTO_INCREMENT カラムに 0 が格納されている場合に便利なことがあります。 (ただし、0 を格納することは、推奨される方法ではありません。) たとえば、mysqldump でテーブルをダンプして、テーブルをリロードする場合、MySQL は通常、0 という値を検出すると、新たなシーケンス番号を生成するため、その結果、ダンプされたものとは異なる内容を持つテーブルになります。 ダンプファイルをリロードする前に NO_AUTO_VALUE_ON_ZERO を有効にすると、この問題は解決します。 このため、mysqldump の出力には、NO_AUTO_VALUE_ON_ZERO を有効にするステートメントが自動的に含まれます。

  • NO_BACKSLASH_ESCAPES

    文字列および識別子内のエスケープ文字としてのバックスラッシュ文字 (\) の使用を無効にします。 このモードを有効にすると、バックスラッシュはほかの文字のように通常の文字になります。

  • NO_DIR_IN_CREATE

    テーブルを作成するとき、INDEX DIRECTORY および DATA DIRECTORY ディレクティブをすべて無視します。 このオプションは、レプリカサーバーで役立ちます。

  • NO_ENGINE_SUBSTITUTION

    CREATE TABLE または ALTER TABLE などのステートメントが無効またはコンパイルされていないストレージエンジンを指定したとき、デフォルトのストレージエンジンの自動置換を制御します。

    デフォルトでは、NO_ENGINE_SUBSTITUTION は有効です。

    ストレージエンジンは実行時にプラガブルであるため、利用できないエンジンも同様に扱われます。

    NO_ENGINE_SUBSTITUTION を無効にすると、CREATE TABLE については、目的のエンジンが利用できない場合にデフォルトエンジンが使用されて警告が発生します。 ALTER TABLE では、警告が発生してテーブルは変更されません。

    NO_ENGINE_SUBSTITUTION を有効にすると、目的のエンジンが利用できない場合にエラーが発生し、テーブルは作成または変更されません。

  • NO_UNSIGNED_SUBTRACTION

    一方が UNSIGNED 型のときに 2 つの整数値の間で減算を行うと、デフォルトでは符号なしの結果が生成されます。 それ以外の場合は、エラーが発生します:

    mysql> SET sql_mode = '';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT CAST(0 AS UNSIGNED) - 1;
    ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

    NO_UNSIGNED_SUBTRACTION SQL モードが有効な場合は、結果は負になります。

    mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
    mysql> SELECT CAST(0 AS UNSIGNED) - 1;
    +-------------------------+
    | CAST(0 AS UNSIGNED) - 1 |
    +-------------------------+
    |                      -1 |
    +-------------------------+

    このような演算の結果を使用して UNSIGNED 整数カラムが更新されると、結果はそのカラム型の最大値に切り落とされます。NO_UNSIGNED_SUBTRACTION が有効になっている場合は、0 に切り落とされます。 厳密な SQL モードを有効にすると、エラーが発生し、カラムは変更されません。

    NO_UNSIGNED_SUBTRACTION が有効な場合、いずれかのオペランドが符号なしであっても、減算の結果は符号付きになります。 たとえば、テーブル t1 のカラム c2 のタイプと、テーブル t2 のカラム c2 のタイプを比較します。

    mysql> SET sql_mode='';
    mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);
    mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;
    mysql> DESCRIBE t1;
    +-------+---------------------+------+-----+---------+-------+
    | Field | Type                | Null | Key | Default | Extra |
    +-------+---------------------+------+-----+---------+-------+
    | c2    | bigint(21) unsigned | NO   |     | 0       |       |
    +-------+---------------------+------+-----+---------+-------+
    
    mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
    mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;
    mysql> DESCRIBE t2;
    +-------+------------+------+-----+---------+-------+
    | Field | Type       | Null | Key | Default | Extra |
    +-------+------------+------+-----+---------+-------+
    | c2    | bigint(21) | NO   |     | 0       |       |
    +-------+------------+------+-----+---------+-------+

    つまり、BIGINT UNSIGNED はすべてのコンテキストで 100% 使用できるわけではありません。 セクション12.11「キャスト関数と演算子」を参照してください。

  • NO_ZERO_DATE

    NO_ZERO_DATE モードは、サーバーが '0000-00-00' を有効な日付として許可するかどうかに影響します。 この影響は、厳密 SQL モードが有効かどうかにも依存します。

    • このモードが有効でない場合、'0000-00-00' は許可され、挿入によって警告が生成されません。

    • このモードが有効な場合、'0000-00-00' は許可され、挿入によって警告が生成されます。

    • このモードおよび厳密モードが有効な場合、IGNORE も指定されている場合を除き、'0000-00-00' は許可されず、挿入によってエラーが生成されます。 INSERT IGNORE および UPDATE IGNORE の場合、'0000-00-00' は許可され、挿入によって警告が生成されます。

    NO_ZERO_DATE は非推奨です。 NO_ZERO_DATE は厳密モードの一部ではありませんが、厳密モードとともに使用する必要があり、デフォルトで有効になっています。 厳密モードも有効にせずに NO_ZERO_DATE が有効になっている場合、またはその逆の場合は、警告が発生します。

    NO_ZERO_DATE は非推奨であるため、将来の MySQL リリースでは、個別のモード名として削除され、厳密な SQL モードの影響に含まれることが予想されます。

  • NO_ZERO_IN_DATE

    NO_ZERO_IN_DATE モードは、年の部分は非ゼロであるが月または日の部分が 0 である日付をサーバーが許可するかどうかに影響します。 (このモードは '2010-00-01''2010-01-00' などの日付に影響しますが、'0000-00-00' には影響しません。 サーバーが '0000-00-00' を許可するかどうかを制御するには、NO_ZERO_DATE モードを使用してください。) NO_ZERO_IN_DATE の影響は、厳密 SQL モードが有効かどうかにも依存します。

    • このモードが有効でない場合、ゼロ部分を含む日付は許可され、挿入によって警告が生成されません。

    • このモードが有効な場合、ゼロ部分を含む日付は '0000-00-00' として挿入され、警告が生成されます。

    • このモードおよび厳密モードが有効な場合は、IGNORE も指定されている場合を除き、ゼロ部分を含む日付は許可されず、挿入によってエラーが生成されます。 INSERT IGNORE および UPDATE IGNORE の場合、ゼロ部分を含む日付は '0000-00-00' として挿入され、警告が生成されます。

    NO_ZERO_IN_DATE は非推奨です。 NO_ZERO_IN_DATE は厳密モードの一部ではありませんが、厳密モードとともに使用する必要があり、デフォルトで有効になっています。 厳密モードも有効にせずに NO_ZERO_IN_DATE が有効になっている場合、またはその逆の場合は、警告が発生します。

    NO_ZERO_IN_DATE は非推奨であるため、将来の MySQL リリースでは、個別のモード名として削除され、厳密な SQL モードの影響に含まれることが予想されます。

  • ONLY_FULL_GROUP_BY

    選択リスト、HAVING 条件または ORDER BY リストが、GROUP BY 句で指定されておらず、機能的に GROUP BY カラムに依存しない (一意に決定される) 非集計カラムを参照するクエリーを拒否します。

    標準 SQL に対する MySQL 拡張機能を使用すると、HAVING 句内で選択リスト内のエイリアス式を参照できます。 HAVING 句は、ONLY_FULL_GROUP_BY が有効かどうかに関係なく、エイリアスを参照できます。

    その他の説明と例については、セクション12.20.3「MySQL での GROUP BY の処理」 を参照してください。

  • PAD_CHAR_TO_FULL_LENGTH

    デフォルトでは、末尾のスペースは、取得時に CHAR カラム値から削除されます。 PAD_CHAR_TO_FULL_LENGTH が有効な場合、削除は行われず、取得された CHAR 値は完全な長さになるまでパディングされます。 このモードは VARCHAR カラムには適用されず、この場合、末尾のスペースは取得時に保持されます。

    注記

    MySQL 8.0.13 では、PAD_CHAR_TO_FULL_LENGTH は非推奨です。 MySQL の将来のバージョンで削除されることが予想されます。

    mysql> CREATE TABLE t1 (c1 CHAR(10));
    Query OK, 0 rows affected (0.37 sec)
    
    mysql> INSERT INTO t1 (c1) VALUES('xy');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> SET sql_mode = '';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
    +------+-----------------+
    | c1   | CHAR_LENGTH(c1) |
    +------+-----------------+
    | xy   |               2 |
    +------+-----------------+
    1 row in set (0.00 sec)
    
    mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
    +------------+-----------------+
    | c1         | CHAR_LENGTH(c1) |
    +------------+-----------------+
    | xy         |              10 |
    +------------+-----------------+
    1 row in set (0.00 sec)
  • PIPES_AS_CONCAT

    || を、OR のシノニムとしてではなく (CONCAT() と同様に) 文字列連結演算子として扱います。

  • REAL_AS_FLOAT

    REALFLOAT のシノニムとして扱います。 デフォルトでは、MySQL は REALDOUBLE のシノニムとして扱います。

  • STRICT_ALL_TABLES

    すべてのストレージエンジンに対して厳密な SQL モードを有効にします。 無効なデータ値は拒否されます。 詳細は、厳密な SQL モードを参照してください。

  • STRICT_TRANS_TABLES

    トランザクションストレージエンジンに対して、および可能な場合は非トランザクションストレージエンジンに対して、厳密な SQL モードを有効にします。 詳細は、厳密な SQL モードを参照してください。

  • TIME_TRUNCATE_FRACTIONAL

    小数秒部分を持つ TIMEDATE または TIMESTAMP 値を、同じタイプで小数桁数が少ないカラムに挿入するときに、端数処理または切捨てが行われるかどうかを制御します。 デフォルトの動作では、丸めが使用されます。 このモードが有効な場合は、かわりに切捨てが行われます。 次の一連のステートメントは、違いを示しています:

    CREATE TABLE t (id INT, tval TIME(1));
    SET sql_mode='';
    INSERT INTO t (id, tval) VALUES(1, 1.55);
    SET sql_mode='TIME_TRUNCATE_FRACTIONAL';
    INSERT INTO t (id, tval) VALUES(2, 1.55);

    結果のテーブルの内容は次のようになります。最初の値は端数処理の対象となり、次に切捨ての対象となります:

    mysql> SELECT id, tval FROM t ORDER BY id;
    +------+------------+
    | id   | tval       |
    +------+------------+
    |    1 | 00:00:01.6 |
    |    2 | 00:00:01.5 |
    +------+------------+

    セクション11.2.6「時間値での小数秒」も参照してください。

組み合わせ SQL モード

次の特殊なモードは、前リストのモード値の組み合わせを表す省略表現として提供されています。

  • ANSI

    REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE および ONLY_FULL_GROUP_BY と同等です。

    また ANSI モードは、外部参照 S(outer_ref) を持つ設定関数 S が、外部参照が解決される外部クエリー内で集約できない場合のクエリーに、サーバーがエラーを返します。 このようなクエリーを次に示します。

    SELECT * FROM t1 WHERE t1.a IN (SELECT MAX(t1.b) FROM t2 WHERE ...);

    ここで、MAX(t1.b) はそのクエリーの WHERE 句に指定されているため、外部クエリーで集約できません。 標準的な SQL では、この状況ではエラーになります。 ANSI モードが有効でない場合、サーバーはそのようなクエリー内の S(outer_ref) を、S(const) を解釈する同じ方法で扱います。

    セクション1.7「MySQL の標準への準拠」を参照してください。

  • TRADITIONAL

    TRADITIONAL は、STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO および NO_ENGINE_SUBSTITUTION と同等です。

厳密な SQL モード

厳密モードは、MySQL が INSERTUPDATE などのデータ変更ステートメントで無効な値または欠落した値を処理する方法を制御します。 値はいくつかの理由で無効になることがあります。 たとえば、カラムに対して正しくないデータ型を持っていたり、範囲外であったりすることがあります。 値の欠落が発生するのは、挿入される新しい行の非 NULL カラムに値が含まれておらず、そのカラムに明示的な DEFAULT 句が定義されていない場合です。 (NULL カラムの場合、値が欠落しているときは NULL が挿入されます。) 厳密モードは、CREATE TABLE などの DDL ステートメントにも影響します。

厳密モードが有効でない場合、MySQL は無効または欠落した値に対して調整された値を挿入し、警告を生成します (セクション13.7.7.42「SHOW WARNINGS ステートメント」を参照してください)。 厳密モードでは、INSERT IGNORE または UPDATE IGNORE を使用すると、この動作を実行できます。

データを変更しない SELECT などのステートメントの場合、厳密モードでは無効な値はエラーでなく警告を生成します。

厳密モードでは、最大キー長を超えるキーを作成しようとするとエラーが発生します。 厳密モードが有効になっていない場合、これにより警告が発生し、キーが最大キー長に切り捨てられます。

厳密モードは、外部キー制約が検査されるかどうかに影響されません。foreign_key_checks を検査に使用できます。 (セクション5.1.8「サーバーシステム変数」を参照してください。)

厳密な SQL モードは、STRICT_ALL_TABLES または STRICT_TRANS_TABLES のいずれかが有効な場合に有効になりますが、これらのモードの影響はいくらか異なります。

  • トランザクションテーブルの場合、STRICT_ALL_TABLES または STRICT_TRANS_TABLES のいずれかが有効なとき、データ変更ステートメント内の無効な値または欠落した値に対してエラーが発生します。 ステートメントは中止されてロールバックされます。

  • 非トランザクションテーブルの場合、挿入または更新される最初の行に不適切な値があるとき、どちらのモードでも動作は同じになり、ステートメントが中止されて、テーブルはそのまま変更されません。 ステートメントが複数行を挿入または変更し、2 行目以降に不適切な値がある場合、どちらの厳密モードが有効になっているかによって結果は異なります。

    • STRICT_ALL_TABLES では、MySQL はエラーを返し、残りの行を無視します。 ただし、それより前の行が挿入または更新されているため、結果は部分更新となります。 これを防ぐには、テーブルを変更することなく中止できる単一行ステートメントを使用します。

    • STRICT_TRANS_TABLES では、MySQL は無効な値をカラムについてのもっとも近い有効な値に変換し、調整された値を挿入します。 値が欠落している場合、MySQL はカラムデータ型の暗黙のデフォルト値を挿入します。 いずれの状況でも MySQL はエラーでなく警告を生成し、ステートメントの処理を続行します。 暗黙的なデフォルトについては、セクション11.6「データ型デフォルト値」に記載されています。

厳密モードは、日付のゼロ、ゼロ日付およびゼロによる除算の処理に次のように影響します:

  • 厳密モードは、MOD(N,0) を含むゼロによる除算の処理に影響します:

    データ変更操作 (INSERTUPDATE):

    • 厳密モードが有効になっていない場合、ゼロ除算によって NULL が挿入され、警告は生成されません。

    • 厳密モードが有効な場合、IGNORE も指定されていないかぎり、ゼロによる除算でエラーが発生します。 INSERT IGNORE および UPDATE IGNORE の場合、ゼロによる除算は NULL を挿入し、警告が生成されます。

    SELECT の場合、ゼロによる除算は NULL を返します。 厳密モードを有効にすると、警告も生成されます。

  • 厳密モードは、サーバーが'0000-00-00'を有効な日付として許可するかどうかに影響します:

    • 厳密モードが有効になっていない場合、'0000-00-00'は許可され、挿入によって警告は生成されません。

    • 厳密モードが有効な場合、IGNORE も指定されていないかぎり、'0000-00-00'は許可されず、挿入によってエラーが生成されます。 INSERT IGNORE および UPDATE IGNORE の場合、'0000-00-00' は許可され、挿入によって警告が生成されます。

  • 厳密モードは、年の部分がゼロ以外で月または日の部分が 0 ('2010-00-01''2010-01-00'などの日付) である日付をサーバーが許可するかどうかに影響します:

    • 厳密モードが有効になっていない場合、ゼロ部分の日付は許可され、挿入によって警告は生成されません。

    • 厳密モードが有効な場合、IGNORE も指定されていないかぎり、ゼロ部分の日付は許可されず、挿入によってエラーが生成されます。 INSERT IGNORE および UPDATE IGNORE の場合、ゼロ部分の日付は'0000-00-00' (IGNORE で有効とみなされます) として挿入され、警告が生成されます。

IGNORE に関する厳密モードの詳細は、IGNORE キーワードと厳密な SQL モードの比較 を参照してください。

厳密モードは、ERROR_FOR_DIVISION_BY_ZERONO_ZERO_DATE および NO_ZERO_IN_DATE モードと組み合せた日付でのゼロ、ゼロ日付およびゼロによる除算の処理に影響します。

IGNORE キーワードと厳密な SQL モードの比較

このセクションでは、IGNORE キーワード (エラーを警告にダウングレード) および厳密な SQL モード (警告をエラーにアップグレード) のステートメント実行への影響を比較します。 影響を受けるステートメントと、それらが適用されるエラーについて説明します。

次のテーブルに、デフォルトでエラーが生成された場合と警告が生成された場合のステートメントの動作のサマリー比較を示します。 デフォルトでエラーが生成されるのは、NOT NULL カラムへの NULL の挿入などです。 デフォルトでは、誤ったデータ型の値をカラムに挿入するという警告が生成されます (文字列'abc'を整数カラムに挿入するなど)。

操作モード ステートメントのデフォルトが Error の場合 ステートメントのデフォルトが警告の場合
IGNORE または厳密な SQL モードなし エラー 警告
IGNORE を使用 警告 警告 (IGNORE または厳密な SQL モードを使用しない場合と同じ)
厳密な SQL モード エラー (IGNORE または厳密な SQL モードを使用しない場合と同じ) エラー
IGNORE および厳密な SQL モード 警告 警告

テーブルから描画する結論の 1 つは、IGNORE キーワードと厳密な SQL モードの両方が有効な場合に、IGNORE が優先されることです。 つまり、IGNORE と厳密な SQL モードはエラー処理に反対の影響を与えると考えられますが、一緒に使用しても取り消されません。

IGNORE がステートメントの実行に与える影響

MySQL のいくつかのステートメントでは、オプションの IGNORE キーワードがサポートされます。 このキーワードを使用すると、サーバーは特定のタイプのエラーをダウングレードし、かわりに警告を生成します。 複数行のステートメントの場合、IGNORE はステートメントを中断するのではなく、次の行にスキップします。 (無視できないエラーの場合、IGNORE キーワードに関係なくエラーが発生します。)

例: テーブル t に主キーカラム i がある場合、複数の行に同じ値の i を挿入しようとすると、通常、重複キーエラーが発生します:

mysql> INSERT INTO t (i) VALUES(1),(1);
ERROR 1062 (23000): Duplicate entry '1' for key 't.PRIMARY'

IGNORE では、重複キーを含む行は挿入されませんが、エラーのかわりに警告が発生します:

mysql> INSERT IGNORE INTO t (i) VALUES(1),(1);
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 1  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 't.PRIMARY' |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

次のステートメントは、IGNORE キーワードをサポートしています:

  • CREATE TABLE ... SELECT: IGNORE は、ステートメントの CREATE TABLE または SELECT 部分には適用されませんが、SELECT によって生成された行のテーブルへの挿入には適用されます。 一意キー値の既存の行を複製する行は破棄されます。

  • DELETE: IGNORE では、行の削除プロセス中に MySQL でエラーが無視されます。

  • INSERT: IGNORE では、一意キー値の既存の行を複製する行は破棄されます。 データ変換エラーの原因となる値に設定された行は、かわりに最も近い有効な値に設定されます。

    特定の値に一致するパーティションが見つからないパーティションテーブルの場合、IGNORE では、一致しない値を含む行に対して挿入操作が暗黙的に失敗します。

  • LOAD DATA, LOAD XML: IGNORE では、一意キー値の既存の行を複製する行は破棄されます。

  • UPDATE: IGNORE では、一意キー値で重複キーの競合が発生した行は更新されません。 データ変換エラーの原因になる値に更新された行は、代わりに、もっとも近い有効な値に更新されます。

IGNORE キーワードは、無視できる次のエラーに適用されます:

ER_BAD_NULL_ERROR
ER_DUP_ENTRY
ER_DUP_ENTRY_WITH_KEY_NAME
ER_DUP_KEY
ER_NO_PARTITION_FOR_GIVEN_VALUE
ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT
ER_NO_REFERENCED_ROW_2
ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
ER_ROW_IS_REFERENCED_2
ER_SUBQUERY_NO_1_ROW
ER_VIEW_CHECK_FAILED
ステートメントの実行に対する厳密な SQL モードの影響

MySQL Server は異なる SQL モードで動作でき、sql_mode システム変数の値に応じて異なるクライアントにこれらの異なるモードを適用できます。 strict SQL モードでは、サーバーは特定の警告をエラーにアップグレードします。

たとえば、非厳密 SQL モードでは、文字列'abc'を整数カラムに挿入すると、値が 0 に変換され、警告が表示されます:

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t (i) VALUES('abc');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'abc' for column 'i' at row 1 |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

厳密な SQL モードでは、無効な値は次のエラーで拒否されます:

mysql> SET sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t (i) VALUES('abc');
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'i' at row 1

sql_mode システム変数の可能な設定の詳細は、セクション5.1.11「サーバー SQL モード」 を参照してください。

厳密な SQL モードは、一部の値が範囲外であるか、無効な行がテーブルに挿入またはテーブルから削除される可能性がある場合に、次のステートメントに適用されます:

  • ALTER TABLE

  • CREATE TABLE

  • CREATE TABLE ... SELECT

  • DELETE (単一テーブルと複数テーブルの両方)

  • INSERT

  • LOAD DATA

  • LOAD XML

  • SELECT SLEEP()

  • UPDATE (単一テーブルと複数テーブルの両方)

ストアドプログラム内では、厳密モードが有効なときにプログラムが定義されていた場合、リストされている型の個々のステートメントは厳密な SQL モードで実行されます。

厳密な SQL モードは、入力値が無効または欠落しているエラーのクラスを表す次のエラーに適用されます。 カラムのデータ型が間違っているか、値が範囲外である可能性がある場合、値は無効です。 挿入する新しい行の定義に明示的な DEFAULT 句がない NOT NULL カラムの値が含まれていない場合、値は欠落しています。

ER_BAD_NULL_ERROR
ER_CUT_VALUE_GROUP_CONCAT
ER_DATA_TOO_LONG
ER_DATETIME_FUNCTION_OVERFLOW
ER_DIVISION_BY_ZERO
ER_INVALID_ARGUMENT_FOR_LOGARITHM
ER_NO_DEFAULT_FOR_FIELD
ER_NO_DEFAULT_FOR_VIEW_FIELD
ER_TOO_LONG_KEY
ER_TRUNCATED_WRONG_VALUE
ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
ER_WARN_DATA_OUT_OF_RANGE
ER_WARN_NULL_TO_NOTNULL
ER_WARN_TOO_FEW_RECORDS
ER_WRONG_ARGUMENTS
ER_WRONG_VALUE_FOR_TYPE
WARN_DATA_TRUNCATED
注記

継続的な MySQL 開発では新しいエラーが定義されるため、前述のリストにないエラーがあり、厳密な SQL モードが適用される場合があります。


関連キーワード:  モード, IGNORE, NO, エラー, 挿入, サーバー, 警告, テーブル, ステートメント, ZERO