SQLモードについて

SQLモードとは

そもそもMySQLはSQL文を独自に解釈してより柔軟に動作するデータベースエンジン。

MySQLバージョン5あたりからSQL規格に則った実装で速度改善と、SQLの整合性を図ろうとしたものの、
その場合、旧バージョンで動かなくなる可能性もあった。

そこで作られたのがSQLモード。MySQL上で他のデータベースと同じような正規SQL文を高速に使用できるようになり設定を減らすことで旧バージョンのMySQL互換性を維持しつつ稼働できようにしている。

つまり、MySQLはSQL文を独自に解釈していた柔軟性と、SQL本来の厳密性の互換性を持っている

もともとMySQLはそういった柔軟性というか、ゆるふわだったので、例えば古いバージョンで動いていたデータベースを新しいバージョンのデータベースに移行させるときに、バージョンが新しいほど厳密になってくるので、厳密性がなかった古いバージョンで動いていたデーターベースを新しいデータベースで動かすときにSQLモードの設定で緩くする必要がある。

MySQLがゆるふわと言われる

MySQLはよくゆるふわと言われることが多いですが、例えば、

日付データの登録がゆるい

0000-00-00 00:00:00または0000-00-00という日付データが登録されてしまう。
(Zero Dateがなぜだめなのかはこちらに詳しく書かれています)
これはSQLモードでNO_ZERO_IN_DATEまたはNO_ZERO_DATEを有効にすれば上記のような日付データは登録できなくする。

集約してない列名も参照できてしまう

GROUP BY で集約した行データを SELECTで参照できるのは、集約に使用した列名だが
集約してない列名も参照できてしまう。

この場合SELECTのところでshohin_idを指定しているがGROUP BYでは指定されてないので本来であれば参照できないが、
標準できてしまうことがある
ONLY_FULL_GROUP_BYを有効にすればGROUBY BYでSELECTで指定してない行データを指定しないと警告がでる

mysql> select shohin_id,shohin_mei  from shohin group by  shohin_mei;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sql_mode.shohin.shohin_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

その他

他にも値が長いと勝手に切り詰められたりなど、いくつかあります。

SQLの設定について

SQLモードの設定は公式や調べてればいろいろと出てくるのですが、主な設定をいくつか挙げてみます

TRADITIONAL

不正なデータを入れようとするときにエラーがでるようにします。
アプリケーションを動かしているときに、不正なデータを入れるぐらいだったら、エラー出したほうが良いと思ってます。

STRICT_TRANS_TABLES

これは最初にあげたTRADITIONALと同等の設定です。
TRADITIONALの違いとしてはSTRICT_TRANS_TABLESは
無効なデータを有効なデータとして調整し、調整された値を挿入します。
TRADITIONALでは無効なデータは挿入せず弾くので厳密度でいうとTRADITIONALが一番上です

ONLY_FULL_GROUP_BY

これは先程あげたので省略

バージョンごとにデフォルト設定

MySQLのバージョン5.7あたりからデフォルトのSQLモードの設定がそれぞれ異なるようです。参考URL

バージョン	デフォルトの設定
5.7.8	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
5.7.7	ONLY_FULL_GROUP_BY、 STRICT_TRANS_TABLES、 NO_AUTO_CREATE_USER、 NO_ENGINE_SUBSTITUTION
5.7.5, 5.7.6	ONLY_FULL_GROUP_BY、 STRICT_TRANS_TABLES、 NO_ENGINE_SUBSTITUTION
5.7.4	NO_ENGINE_SUBSTITUTION

バージョンによってSQLモードの設定は異なり予期せぬ挙動を起こしたりするときがあるので
SQLモードは設定しておきましょう。