SQLモード（sql_mode）
SQLモードとは
MySQLでは指定された値のままレコードに格納できなくても、なるべくエラーにならないように処理を続けるようになっている。
多分MyISAMなどのトランザクション機能がないストレージエンジンを使った時の名残ではないだろうか。
エラーが発生してもロールバックができないからとりあえずエラーが発生しないようにしておいた。
しかし、sql_modeをstrictモードで設定すれば、このようなMySQL特有の振る舞いを変更して他のSQLデータベースのように使える。
SQLモードのデフォルト値
まずsql_modeのデフォルト値を確認する。
MySQL 5.7.8以降のデフォルト値はこうなる。
mysql> select @@global.sql_mode \G
*************************** 1. row ***************************
@@global.sql_mode:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)
最初から色々設定されていたので、調べてみた。
MySQLドキュメントに説明された内容から必要な部分だけを抜粋して整理した。
- ONLY_FULL_GROUP_BY
GROUP BY句で名前が指定されていない非集約カラムを、選択リスト、HAVING条件、またはORDERリストが参照するクエリを拒否する。
- STRICT_TRANS_TABLES
値を指定した通りにトランザクションテーブルに挿入できない場合、ステートメントを中止する。
非トランザクションテーブルの場合、値が単一行ステートメントで発生するか、複数行ステートメントの先頭行で発生した場合、ステートメントを中止する。
- NO_ZERO_IN_DATE
年の部分はゼロではないが、月または日の部分が0である日付がこのモードの制御対象になる。
'0000-00-00'は制御対象ではない。'0000-00-00'についてはNO_ZERO_DATEモードを使う。
このモードおよび厳密モード1が有効な場合、IGNOREが指定されている場合を除き、ゼロ部分を含む日付は許可されず、エラーになる。
INSERT IGNOREおよびUPDATE IGNOREの場合、ゼロ部分を含む日付は'0000-00-00'として挿入され、警告になる。
- NO_ZERO_DATE
'0000-00-00'のデータを見たことがあるんだろうか。
そんなデータの入力を防ぐために必要。
このモードと厳密モードが有効な場合、IGNOREが指定されている場合を除き、'0000-00-00'は許可されず、エラーになる。
INSERT IGNOREおよびUPDATE IGNOREの場合は、'0000-00-00'が許可され、警告になる。
- ERROR_FOR_DIVISION_BY_ZERO
このモードおよび厳密モードが有効な場合、ゼロによる除算はエラーになる。
INSERT IGNOREおよびUPDATE IGNOREの場合は、ゼロによる除算はNULLを挿入し、警告になる。
- NO_AUTO_CREATE_USER
認証情報が指定される場合を除き、他の方法で実行される場合は、GRANTステートメントで新規ユーザーを自動的に作成しない。
- NO_ENGINE_SUBSTITUTION
CREATE TABLEまたはALTER TABLEなどのステートメントが無効またはコンパイルされていないストレージエンジンを指定した時、デフォルトのストレージエンジンの自動置換を制御する。
このモードが有効な場合、目的のエンジンが利用できない場合はエラーになり、テーブルは作成または変更されない。
オススメのSQLモードの設定
MySQLをstrictモードで使うため、次の三つのモードを設定する。
- TRADITIONAL
MySQLを伝統的なSQLデータベースのように動作させる。
カラムに不正な値を挿入した時は警告ではなくエラーを返す。
これは特殊な組み合わせモード2である。
注意：このモードを有効にする場合はトランザクション機能を持つストレージエンジンを使う。（InnoDBやNDB）
- NO_AUTO_VALUE_ON_ZERO
AUTO_INCREMENTカラムを制御する。
通常は、NULLまたは0をカラムに挿入することによって、カラムの次のシーケンス番号を生成する。
NO_AUTO_VALUE_ON_ZEROが有効な場合だと、NULLのみ次のシーケンス番号を生成する。
mysqldumpでテーブルをダンプして、テーブルをリロードする場合、MySQLは通常、0という値を検出すると、新たなシーケンス番号を生成するため、その結果、ダンプされたものとは異なる内容を持つテーブルになる。
NO_AUTO_VALUE_ON_ZEROを有効にすることでこの問題が解決される。
現在のmysqldumpはNO_AUTO_VALUE_ON_ZEROを有効にするステートメントがその出力に自動的に含まれるようになっている。
- ONLY_FULL_GROUP_BY
既に説明したので、省略する。
/etc/my.cnfファイルに下記の内容を追記して、mysqldを再起動する。
[mysqld]
... snip ...
sql_mode=TRADITIONAL,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY
これで、MySQLをstrictモードで使える。
ハマりポイント
sql_modeを設定して確認したら、期待したことと違った。
# 期待値
TRADITIONAL,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY
# 実際値
ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
色々調べてTRADITIONALが組み合わせモードということが分かった。
（MySQLドキュメントにも出ているが、見落としていた。）
デフォルトの設定と重なっているモードを除外すると、次の三つのモードだけが残る。
- NO_AUTO_VALUE_ON_ZERO
- STRICT_ALL_TABLES
- TRADITIONAL
最初、mysqlを初期化した時、/etc/my.cnfファイルにはsql_modeの設定がなかった。
それで、デフォルト値は必ず適用されてその上sql_modeにて指定した値の中でデフォルト値には存在しないモードが追加されることではないのかと考えたが、これも違った。
sql_modeにNO_AUTO_VALUE_ON_ZEROとSTRICT_ALL_TABLESだけを指定して試したところ、このモードだけが設定された。
- /etc/my.cnf
[mysqld]
... snip ...
sql_mode=NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES
- 結果
mysql> select @@global.sql_mode \G
*************************** 1. row ***************************
@@global.sql_mode: NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES
1 row in set (0.00 sec)
my.cnfファイルはglobal変数の設定をまとめて置いたものだ。
設定値に間違いがない限り必ずglobal変数を上書きする。
参考
- サーバーSQLモード
- データベースエンジニア教本MySQL＆PostgreSQL＆NoSQL編