PDF (US Ltr)
- 26.8Mb
PDF (A4)
- 26.9Mb
HTML Download (TGZ)
- 7.1Mb
HTML Download (Zip)
- 7.2Mb
MySQL Server は異なる SQL モードで動作でき、sql_mode システム変数の値に応じて異なるクライアントにこれらの異なるモードを適用できます。DBA はサイトサーバーの動作要件に一致するグローバル SQL モードを設定でき、各アプリケーションはアプリケーションのセッション SQL モードをアプリケーション独自の要件に設定できます。
モードは MySQL がサポートする SQL 構文と、MySQL が実行するデータ検証に影響します。これにより、MySQL をさまざまな環境で使用したり、MySQL をほかのデータベースサーバーと一緒に使用したりすることが、さらに容易になります。
MySQL のサーバー SQL モードについてのよくある質問に対する回答は、セクションA.3「MySQL 5.6 FAQ: サーバー SQL モード」を参照してください。
InnoDB テーブルを操作するとき、innodb_strict_mode システム変数についても考慮してください。これによって、InnoDB テーブルの追加のエラー検査が可能になります。
SQL モードの設定
MySQL 5.6.6 以降でのデフォルトの SQL モードは NO_ENGINE_SUBSTITUTION で、MySQL 5.6.5 以前では、これは空白です (モードの設定なし)。
サーバー起動時に SQL モードを設定するには、コマンド行で --sql-mode=" オプションを使用するか、modes"my.cnf (Unix オペレーティングシステム) または my.ini (Windows) などのオプションファイル内で sql-mode=" を使用します。modes"modes は、カンマで区切られるさまざまなモードのリストです。SQL モードを明示的にクリアするには、コマンド行で --sql-mode="" を使用するかオプションファイル内で sql-mode="" を使用して、SQL モードを空の文字列に設定します。
注記
MySQL インストールプログラムはインストールプロセス中に SQL モードを構成することがあります。たとえば、mysql_install_db は、my.cnf という名前のデフォルトオプションファイルを基本インストールディレクトリに作成します。このファイルには、SQL モードを設定する行が含まれています。セクション4.4.3「mysql_install_db — MySQL データディレクトリの初期化」を参照してください。
SQL モードがデフォルトまたは期待されているモードと異なる場合、サーバーが起動時に読み取るオプションファイル内の設定を確認してください。
SQL モードを実行時に変更するには、SET ステートメントを使用して、グローバルまたはセッションの sql_mode システム変数を設定します。
SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';
GLOBAL 変数を設定するには SUPER 権限が必要で、この設定はその時点以降に接続するすべてのクライアントの動作に影響します。SESSION 変数を設定すると、現在のクライアントにのみ影響します。すべてのクライアントは、自分のセッションの sql_mode 値をいつでも変更できます。
現在のグローバルまたはセッションの sql_mode 値を確認するには、次のステートメントを使用します。
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;重要
SQL モードおよびユーザー定義のパーティショニング パーティション化されたテーブルを作成してデータを挿入したあとでサーバー SQL モードを変更すると、このようなテーブルの動作が大きく変更される可能性があり、データが失われたり破損したりすることがあります。ユーザー定義のパーティショニングを使用したテーブルを作成したら、SQL モードを変更しないことを強くお勧めします。
パーティション化されたテーブルをレプリケーションするとき、マスターとスレーブの SQL モードが違うことが問題につながることもあります。最適な結果を得るために、マスターとスレーブとで常に同じサーバー SQL モードを使用してください。
詳細については、セクション19.6「パーティショニングの制約と制限」を参照してください。
もっとも重要な SQL モード
次に、多くの場合でもっとも重要な sql_mode 値を示します。
-
このモードは、構文および動作が標準の SQL にさらに緊密に準拠するように変更します。これは、このセクションの末尾にリストされている、特殊な組み合わせモードの 1 つです。
-
値を指定したとおりにトランザクションテーブルに挿入できない場合、ステートメントを中止します。非トランザクションテーブルの場合、値が単一行ステートメントで発生するか、複数行ステートメントの先頭行で発生した場合、ステートメントを中止します。詳細については、このセクションのあとの方で説明します。
-
MySQL を 「従来型の」 SQL データベースシステムのように動作させます。このモードを簡単に説明すると、カラムに不正な値を挿入したときに「警告ではなくエラーを返し」ます。これは、このセクションの末尾にリストされている、特殊な組み合わせモードの 1 つです。
注記INSERTまたはUPDATEは、エラーが見つかるとすぐに中止します。非トランザクションストレージエンジンを使用している場合、これは期待する動作でない場合もあります。エラーの前に行われたデータ変更はロールバックされず、「部分的に実行された」更新になることがあるためです。
このマニュアルの「厳密モード」とは、STRICT_TRANS_TABLES または STRICT_ALL_TABLES のいずれかあるいは両方が有効なモードを意味します。
SQL モードの完全なリスト
次のリストは、サポートされるすべての SQL モードについて説明しています。
-
日付の完全な検査を実行しません。月が 1 から 12 までの範囲にあることと、日が 1 から 31 までの範囲にあることのみ検査します。これは、年、月、および日を 3 つの異なるフィールドで取得し、ユーザーが挿入したデータを (日付の検証を行わずに) そのまま格納する Web アプリケーションでは非常に便利です。このモードは
DATEおよびDATETIMEカラムに適用されます。TIMESTAMPカラムは有効な日付が常に必要なため、このカラムには適用されません。サーバーは、月と日の値がそれぞれ 1 から 12 または 1 から 31 の範囲にあることだけでなく、正しい値であることを要求します。厳密モードが無効になっていると、
'2004-04-31'のような無効な日付は'0000-00-00'に変換され、警告メッセージが表示されます。厳密モードが有効なときは、無効な日付によってエラーが発生します。このような日付を許可するには、ALLOW_INVALID_DATESを有効にします。 -
「
"」 を (「`」 引用符文字のような) 識別子引用符文字として扱い、文字列引用符文字として扱いません。このモードを有効にして、識別子を引用するために 「`」 を引き続き使用できます。ANSI_QUOTESを有効にすると、二重引用符を使用してリテラル文字列を引用できません (リテラル文字列が識別子として解釈されるため)。 -
ERROR_FOR_DIVISION_BY_ZEROモードは、MOD(を含むゼロ除算の処理に影響します。データ変更操作 (N,0)INSERT、UPDATE) の場合、この効果は厳密 SQL モードが有効であるかどうかにもよります。このモードが有効でない場合、ゼロによる除算は
NULLを挿入し、警告は生成されません。このモードが有効な場合、ゼロによる除算は
NULLを挿入し、警告が生成されます。このモードおよび厳密モードが有効な場合、ゼロによる除算はエラーを生成しますが、
IGNOREも指定されている場合は例外です。INSERT IGNOREおよびUPDATE IGNOREの場合、ゼロによる除算はNULLを挿入し、警告が生成されます。
SELECTの場合、ゼロによる除算はNULLを返します。ERROR_FOR_DIVISION_BY_ZEROを有効にすると、厳密モードが有効かどうかに関係なく警告も生成されます。MySQL 5.6.17 以降では、
ERROR_FOR_DIVISION_BY_ZEROは非推奨となり、これを含むようにsql_mode値を設定すると警告が生成されます。MySQL 5.7 では、このモードは何も行いません。その代わり、この効果は厳密モードの効果に含められています。 -
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 -
関数名と 「
(」 文字の間にスペースを許可します。これにより、組み込み関数名が予約語として扱われます。その結果、関数名と同じ識別子は、セクション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_SPACESQL モードは、ユーザー定義関数またはストアドファンクションではなく、組み込み関数に適用されます。IGNORE_SPACEが有効かどうかにかかわらず、UDF またはストアドファンクション名のあとにスペースを入れることが常に許可されます。IGNORE_SPACEに関する詳細は、セクション9.2.4「関数名の構文解析と解決」を参照してください。 -
認証情報が指定される場合を除き、ほかの方法で実行される場合は、
GRANTステートメントで新規ユーザーを自動的に作成しません。ステートメントはIDENTIFIED BYを使用した空以外のパスワードを指定するか、IDENTIFIED WITHを使用した認証プラグインを指定する必要があります。 -
NO_AUTO_VALUE_ON_ZEROはAUTO_INCREMENTカラムの処理に影響します。通常は、NULLまたは0をカラムに挿入することによって、カラムの次のシーケンス番号を生成します。NO_AUTO_VALUE_ON_ZEROは0のこの動作を抑制するため、NULLのみが次のシーケンス番号を生成します。このモードは、テーブルの
AUTO_INCREMENTカラムに0が格納されている場合に便利なことがあります。(ただし、0を格納することは、推奨される方法ではありません。)たとえば、mysqldump でテーブルをダンプして、テーブルをリロードする場合、MySQL は通常、0という値を検出すると、新たなシーケンス番号を生成するため、その結果、ダンプされたものとは異なる内容を持つテーブルになります。ダンプファイルをリロードする前にNO_AUTO_VALUE_ON_ZEROを有効にすると、この問題が解決されます。この問題を防ぐために、mysqldump には現在、NO_AUTO_VALUE_ON_ZEROを有効にするステートメントがその出力に自動的に含まれるようになりました。 -
バックスラッシュ文字 (「
\」) を文字列内でエスケープ文字として使用することを無効にします。このモードを有効にすると、バックスラッシュはほかの文字のように通常の文字になります。 -
テーブルを作成するとき、
INDEX DIRECTORYおよびDATA DIRECTORYディレクティブをすべて無視します。このオプションは、スレーブレプリケーションサーバー上で役立ちます。 -
CREATE TABLEまたはALTER TABLEなどのステートメントが無効またはコンパイルされていないストレージエンジンを指定したとき、デフォルトのストレージエンジンの自動置換を制御します。ストレージエンジンは実行時にプラガブルであるため、利用できないエンジンも同様に扱われます。
NO_ENGINE_SUBSTITUTIONを無効にすると、CREATE TABLEについては、目的のエンジンが利用できない場合にデフォルトエンジンが使用されて警告が発生します。ALTER TABLEでは、警告が発生してテーブルは変更されません。NO_ENGINE_SUBSTITUTIONを有効にすると、目的のエンジンが利用できない場合にエラーが発生し、テーブルは作成または変更されません。 -
SHOW CREATE TABLEの出力に MySQL 固有のカラムオプションを出力しません。このモードはポータビリティモードで mysqldump によって使用されます。 -
SHOW CREATE TABLEの出力で MySQL 固有のインデックスオプションを出力しません。このモードはポータビリティモードで mysqldump によって使用されます。 -
SHOW CREATE TABLEの出力で MySQL 固有のテーブルオプション (ENGINEなど) を出力しません。このモードはポータビリティモードで mysqldump によって使用されます。 -
デフォルトでは、いずれかのオペランドが
UNSIGNEDの場合、整数オペランド間の減算はUNSIGNEDの結果を生成します。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 | | | 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) | | | 0 | | +-------+------------+------+-----+---------+-------+このことは、
BIGINT UNSIGNEDがすべてのコンテキストで 100% 使用可能ではないことを意味します。セクション12.10「キャスト関数と演算子」を参照してください。mysql> SET sql_mode = ''; mysql> SELECT CAST(0 AS UNSIGNED) - 1; +-------------------------+ | CAST(0 AS UNSIGNED) - 1 | +-------------------------+ | 18446744073709551615 | +-------------------------+ mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION'; mysql> SELECT CAST(0 AS UNSIGNED) - 1; +-------------------------+ | CAST(0 AS UNSIGNED) - 1 | +-------------------------+ | -1 | +-------------------------+ -
NO_ZERO_DATEモードは、サーバーが'0000-00-00'を有効な日付として許可するかどうかに影響します。この影響は、厳密 SQL モードが有効かどうかにも依存します。このモードが有効でない場合、
'0000-00-00'は許可され、挿入によって警告が生成されません。このモードが有効な場合、
'0000-00-00'は許可され、挿入によって警告が生成されます。このモードおよび厳密モードが有効な場合、
IGNOREも指定されている場合を除き、'0000-00-00'は許可されず、挿入によってエラーが生成されます。INSERT IGNOREおよびUPDATE IGNOREの場合、'0000-00-00'は許可され、挿入によって警告が生成されます。
MySQL 5.6.17 以降では、
NO_ZERO_DATEは非推奨となり、これを含むようにsql_mode値を設定すると警告が生成されます。MySQL 5.7 では、このモードは何も行いません。その代わり、この効果は厳密モードの効果に含められています。 -
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'として挿入され、警告が生成されます。
MySQL 5.6.17 以降では、
NO_ZERO_IN_DATEは非推奨となり、これを含むようにsql_mode値を設定すると警告が生成されます。MySQL 5.7 では、このモードは何も行いません。その代わり、この効果は厳密モードの効果に含められています。 -
GROUP BY句で名前が指定されていない非集約カラムを、選択リスト、HAVING条件、または (MySQL 5.6.5 以降で)ORDERリストが参照するクエリーを拒否します。ONLY_FULL_GROUP_BYが有効な場合、次のクエリーは無効です。1 番目は、選択リスト内の非集約のaddressカラムがGROUP BY句で名前を指定されておらず、2 番目は、HAVING句のmax_ageがGROUP BY句で名前を指定されていないため、ともに無効になります。mysql> SELECT name, address, MAX(age) FROM t GROUP BY name; ERROR 1055 (42000): 't.address' isn't in GROUP BYmysql> SELECT name, MAX(age) AS max_age FROM t GROUP BY name -> HAVING max_age < 30; Empty set (0.00 sec) ERROR 1463 (42000): Non-grouping field 'max_age' is used in HAVING clause2 番目の例では、
HAVING MAX(age)を使用するようにクエリーを書き換えることで、集約関数で名前を指定されているカラムが参照されるようになります。(max_ageは集約関数そのものであるため失敗します。)クエリーに集約関数があって
GROUP BY句がない場合、ONLY_FULL_GROUP_BYが有効なときに、クエリーは選択リストまたはORDER BYリストに非集約カラムを含めることができません。mysql> SELECT name, MAX(age) FROM t; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause追加の説明については、セクション12.19.3「MySQL での GROUP BY の処理」を参照してください。
-
デフォルトでは、末尾のスペースは、取得時に
CHARカラム値から削除されます。PAD_CHAR_TO_FULL_LENGTHが有効な場合、削除は行われず、取得されたCHAR値は完全な長さになるまでパディングされます。このモードはVARCHARカラムには適用されず、この場合、末尾のスペースは取得時に保持されます。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) -
||を、ORのシノニムとしてではなく (CONCAT()と同様に) 文字列連結演算子として扱います。 -
REALをFLOATのシノニムとして扱います。デフォルトでは、MySQL はREALをDOUBLEのシノニムとして扱います。 -
すべてのストレージエンジンについて厳密モードを有効にします。無効なデータ値は拒否されます。追加の詳細は、あとの方で説明します。
-
トランザクションストレージエンジンの厳密モードを有効にし、可能な場合は非トランザクションストレージエンジンでも有効にします。追加の詳細は、あとの方で説明します。
厳密な SQL モード
厳密モードは、MySQL が INSERT や UPDATE などのデータ変更ステートメントで無効な値または欠落した値を処理する方法を制御します。値はいくつかの理由で無効になることがあります。たとえば、カラムに対して正しくないデータ型を持っていたり、範囲外であったりすることがあります。値の欠落が発生するのは、挿入される新しい行の非 NULL カラムに値が含まれておらず、そのカラムに明示的な DEFAULT 句が定義されていない場合です。(NULL カラムの場合、値が欠落しているときは NULL が挿入されます。)
厳密モードが有効でない場合、MySQL は無効または欠落した値に対して調整された値を挿入し、警告を生成します (セクション13.7.5.41「SHOW WARNINGS 構文」を参照してください)。厳密モードでは、INSERT IGNORE または UPDATE IGNORE を使用すると、この動作を実行できます。
データを変更しない SELECT などのステートメントの場合、厳密モードでは無効な値はエラーでなく警告を生成します。
厳密モードは、外部キー制約が検査されるかどうかに影響されません。foreign_key_checks を検査に使用できます。(セクション5.1.4「サーバーシステム変数」を参照してください。)
厳密な 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「データ型デフォルト値」に記載されています。
厳密モードは ERROR_FOR_DIVISION_BY_ZERO、NO_ZERO_DATE、および NO_ZERO_IN_DATE モードに関連して、ゼロによる除算、ゼロ日付、および日付内のゼロの処理にも影響を及ぼします。詳細については、これらのモードの説明を参照してください。
組み合わせ SQL モード
次の特殊なモードは、前リストのモード値の組み合わせを表す省略表現として提供されています。
-
REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACEと同等です。また
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 の標準への準拠」を参照してください。
-
PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONSと同等です。 -
PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USERと同等です。 -
PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONSと同等です。 -
NO_FIELD_OPTIONS、HIGH_NOT_PRECEDENCEと同等です。 -
NO_FIELD_OPTIONS、HIGH_NOT_PRECEDENCEと同等です。 -
PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USERと同等です。 -
PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONSと同等です。 -
STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER、およびNO_ENGINE_SUBSTITUTIONと同等です。