Help us understand the problem. What is going on with this article?

MySQLのSQLモードをstrictモードで設定する。

More than 1 year has passed since last update.

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変数を上書きする。

参考


  1. 厳密モードとはSTRICT_TRANS_TABLESまたはSTRICT_ALL_TABLESのいずれかあるいは両方が有効なモードを意味する。 

  2. TRADITIONALは、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 と同等だ。 

park-jh
プルスタックエンジニアになれるまで頑張ろう。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした