MySQLのsql_mode正しく設定されていますか?

MySQLのこんな事象に遭遇したことはありませんか?

・MySQLのint型カラムに 空文字 ” をインサートすると 0 になる。
・MySQLのdatetime型カラムに 空文字 ” をインサートすると 0000-00-00 00:00:00 になる。

はい。おそらく低いバージョンのMySQLで、かつ sql_mode が未設定(デフォルトのまま)です。
今後MySQLのバージョンが上がった際にデフォルトの sql_mode が変わって、それにともないMySQLの挙動が変わる可能性があります。
結構危険ですので、MySQLのsql_modeの確認と設定を行いましょう。

 

● MySQLのsql_modeを確認する

sql_mode を確認するには次のクエリーを投げます。

SHOW VARIABLES LIKE "%sql_mode%";

 

また、グローバルスコープの設定を確認するにはこちら

SELECT @@global.sql_mode;

 

また、セッションスコープの設定を確認するにはこちら

SELECT @@session.sql_mode;

 

結果例

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | STRICT_ALL_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO |
+---------------+-------+
1 row in set (0.00 sec)

 

● 主なMySQLのsql_modeの種類を知る

 

sql_mode = ‘STRICT_ALL_TABLES’ または sql_mode = ‘STRICT_TRANS_TABLES’

INT型のカラムに ‘1234abcd’ を 入力しようとすると 数値 1234 で入力されてしまうが、
、これを指定しておくとエラー表示してくれる。

 

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 GLOBAL sql_mode = 'modes';

root以外のユーザーの場合は そのセッションのみの設定を SESSION スコープにセットできます

SET SESSION sql_mode = 'modes';

 

・設定例

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

 

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

my.cnf
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

 

● MySQLのバージョンごとの sql_mode のデフォルト値

MySQLのバージョンによってデフォルト値が違うことを認識しておきましょう。

・MySQL 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
・MySQL 5.7.7
ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION
・MySQL 5.7.6
ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ENGINE_SUBSTITUTION
・MySQL 5.7.4
NO_ENGINE_SUBSTITUTION
・MySQL 5.6
NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode

バージョン 5.7 になってからころころ変わっていますね。

デフォルトのまま放置することがいかに危険であるかがわかります。(苦笑)

MySQLの sql_mode は必ず設定しましょう。

 

 

MySQLのsql_mode正しく設定されていますか?
Tagged on:             

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

CAPTCHA