MySQL 5.7からデフォルトになるSTRICT_TRANS_TABLEはMyISAMにも影響を及ぼす

恥ずかしながら完全に誤解してた。

MySQL :: MySQL 5.6 Reference Manual :: 5.1.7 Server SQL Modes

For nontransactional tables, the behavior is the same for either mode if the bad value occurs in the first row to be inserted or updated: The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the result depends on which strict mode is enabled:

For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, because the earlier rows have been inserted or updated, the result is a partial update. To avoid this, use single-row statements, which can be aborted without changing the table.

For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and inserts the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. In either case, MySQL generates a warning rather than an error and continues processing the statement. Implicit defaults are described in Section 11.6, “Data Type Default Values”.

strictとして扱われるかかどうかは (STRICT_ALL_TABLES | STRICT_TRANS_TABLES) で決まり、ストレージエンジンがトランザクション対応かどうかは関係ない。

つまり、

mysql56> CREATE TABLE t1 (val varchar(1)) Engine= MyISAM;
Query OK, 0 rows affected (0.07 sec)

mysql56> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.01 sec)

mysql56> INSERT INTO t1 VALUES ('ab');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql56> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'val' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql56> SELECT * FROM t1;
+------+
| val  |
+------+
| a    |
+------+
1 row in set (0.01 sec)

sql_modeが空っぽならワーニングで入るけど、

mysql56> CREATE TABLE t2 (val varchar(1)) Engine= MyISAM;
Query OK, 0 rows affected (0.10 sec)

mysql56> SELECT @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)

mysql56> INSERT INTO t2 VALUES ('ab');
ERROR 1406 (22001): Data too long for column 'val' at row 1

mysql56> SELECT * FROM t2;
Empty set (0.00 sec)

STRICT_TRANS_TABLESだと入らない。


更に、

mysql56> INSERT INTO t3 VALUES ('a'), ('ab'), ('abc');
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 2

mysql56> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1406 | Data too long for column 'val' at row 2 |
| Warning | 1406 | Data too long for column 'val' at row 3 |
+---------+------+-----------------------------------------+
2 rows in set (0.01 sec)

mysql56> SELECT * FROM t3;
+------+
| val  |
+------+
| a    |
| a    |
| a    |
+------+
3 rows in set (0.01 sec)

1個目が正しいデータで、2個目移行が間違ったデータを突っ込もうとすると、warningが起こるがINSERTはされる。

mysql56> INSERT INTO t4 VALUES ('abc'), ('ab'), ('a');
ERROR 1406 (22001): Data too long for column 'val' at row 1

mysql56> SELECT * FROM t4;
Empty set (0.00 sec)

1個目が正しくなかった場合、あとに正しいデータが入っていてもそれもabortされる。


STRICT_TRANS_TABLESは操作対象がトランザクション対応の時にstrict_modeになって、トランザクション非対応な時はnon-strict_modeだと完全に誤解していた。。