MySQL v5.6でSTRICTモードにした時のクエリ挙動差異まとめ

MySQL5.6から、SQL_MODEがSTRICTデフォルト化してたので、クエリ挙動の差異についてまとめた。

SQL_MODEによる挙動変化

sql_mode=""の状態だと、不適切なデータ値が許可されており、データ入力の際にそれらを有効値に強制変換する仕様がある。
MySQL5.5まではデフォルトがsql_mode=""だったが、MySQL5.6からはデフォルトで勝手にSTRICT_MODEが設定されるようになった。

my.ini

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

MySQL5.5までSTRICTモードにしなかったんだけど？

sql_mode=""で運用してきたシステムを、sql_mode="STRICT_TRANS_TABLES"にすると以下に述べるようなクエリは全部エラーになる。
残念ながらALLOW_INVALID_DATES(日付の完全チェックを行わない)はtimestampには意味を成さない。

SQL_MODEの確認方法は、select @@global.sql_mode;　で出来る。

対策としては、my.iniを以下のように修正するしかない。

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sql-mode=''

オンラインで変更もできる。

set sql_mode = "";

１、timstampカラムで、範囲外の時間を指定する

検証テーブル

CREATE TABLE eudyptes_test_timestamp (
  timestamp timestamp NOT NULL,
  datetime datetime NOT NULL
) ENGINE=InnoDB;

実行クエリ

> insert into eudyptes_test_timestamp (timestamp, datetime) values ('1013-01-01 00:00:00', '2013-01-01 00:00:00');
> insert into eudyptes_test_timestamp (timestamp, datetime) values ('2013-01-01 00:00:00', '1013-01-01 00:00:00');

NOT_STRICT

Query OK, 1 row affected, 1 warning (0.00 sec)
Warning (Code 1264): Out of range value adjusted for column 'timestamp' at row 1

Query OK, 1 row affected (0.01 sec)

+---------------------+---------------------+
| timestamp           | datetime            |
+---------------------+---------------------+
| 0000-00-00 00:00:00 | 2013-01-01 00:00:00 |
| 2013-01-01 00:00:00 | 1013-01-01 00:00:00 |
+---------------------+---------------------+

STRICT;

ERROR 1292 (22007): Incorrect datetime value: '1013-01-01 00:00:00' for column 'timestamp' at row 1
Query OK, 1 row affected (0.01 sec)

+---------------------+---------------------+
| timestamp           | datetime            |
+---------------------+---------------------+
| 2013-01-01 00:00:00 | 1013-01-01 00:00:00 |
+---------------------+---------------------+

timestampはエラーになる。　datetimeは変わらない。

２、NOT NULL補完

検証テーブル

create table eudyptes_not_nulls (
    `id` int(11) not null,
    `id2` int(11) default null
) ENGINE=InnoDB;

実行クエリ

insert into eudyptes_not_nulls (id2) values(100);

NOT_STRICT

Query OK, 1 row affected, 1 warning (0.00 sec)
Warning (Code 1364): Field 'id' doesn't have a default value

+----+------+
| id | id2  |
+----+------+
|  0 |  100 |
+----+------+

０で補完される。

STRICT;

ERROR 1364 (HY000): Field 'id' doesn't have a default value

３、varchar の文字列溢れ

検証テーブル

create table eudyptes_varchar (text varchar(10));

実行クエリ

insert eudyptes_varchar (text) values('aaabbccddeeffgghhiijj');

NOT_STRICT

Query OK, 1 row affected, 1 warning (0.00 sec)
Warning (Code 1265): Data truncated for column 'text' at row 1

+------------+
| text       |
+------------+
| aaabbccdde |
+------------+

STRICT;

set sql_mode = 'STRICT_ALL_TABLES';
ERROR 1406 (22001): Data too long for column 'text' at row 1