MySQL sql_mode

MySqlのレコードのinsertで、開発環境で動作していたものが、チェック環境で動作しませんでした。
原因は、NotNull制約がついているカラムに空のデータをinsertしようとしたためでした。
開発環境とテスト環境でsql_modeの設定が異なっていたための挙動の違いです。
双方の環境で設定を合わせておかないとダメですね。。
解決方法をメモします。

挙動の違い

NotNull制約のついたcol1, col2というカラムを持つtest_tableがあるとして

テスト環境

mysql> insert into test_table(col1) values('aaa');
ERROR 1364 (HY000): Field 'col2' doesn't have a default value

insertに失敗

開発環境

mysql> insert into test_table(col1) values('aaa');
Query OK, 0 rows affected, 1 warning (0.00 sec)

insertが通ってしまう。

テスト環境の設定

mysql> SELECT @@global.sql_mode;
+--------------------------------------------+
| @@global.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

自分の開発環境の設定

mysql> SELECT @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

まったく設定がされていなかった！

sql_modeの設定

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

テスト環境と同じsql_modeをセット

mysql> insert into test_table(col1) values('aaa');
ERROR 1364 (HY000): Field 'col2' doesn't have a default value

空のデータをinsertすると怒られるようになった

---

主なMySQLのsql_mode

sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO'

デフォルトではMySQLでは0の除算は NULL になるが、これを指定しておくとエラー表示してくれる。

sql_mode = 'NO_ZERO_DATE'

DATE型 DATETIME型 に 0000-00-00 を入力しようとするとエラー表示してくれる。

sql_mode = 'NO_ZERO_DATE'

DATE型 DATETIME型 に 2017-12-00 を入力しようとするとエラー表示してくれる。

● MySQLのsql_modeを設定する
root ユーザーの場合は GLOBAL に設定することが可能です

・設定例

SET SESSION sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_DATE';

/etc/my.cnf を書き換える権限がある場合は、こちらを設定して mysql をリスタートします。

my.cnf

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES