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

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

More than 1 year has passed since last update.

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 = "";

1、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は変わらない。

2、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 |
+----+------+

0で補完される。

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

3、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
peutes
Scala/JavaScript/PHP/Python/Erlang/C++/MVC/Java/Go/Perl 他:MySQL/memcached/Redis/emacs/IntelliJ/GCS/Gatling
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
ユーザーは見つかりませんでした