とりあえず8.0.26で実験する。
INSERT IGNORE INTO といえば、キー重複エラーを握りつぶして複数のINSERTを「先勝ち」であるかのように振る舞わせるのに使われることが多いやつ。
mysql80 10> CREATE TABLE t2 (pk int PRIMARY KEY);
Query OK, 0 rows affected (0.02 sec)
mysql80 10> INSERT INTO t2 VALUES (1);
Query OK, 1 row affected (0.01 sec)
mysql80 10> INSERT INTO t2 VALUES (1);
ERROR 1062 (23000): Duplicate entry '1' for key 't2.PRIMARY'
mysql80 10> INSERT IGNORE INTO t2 VALUES (1);
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql80 10> SELECT * FROM t2;
+----+
| pk |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
あるいは、あたかも sql_mode= STRICT_TRANS_TABLES であっても、non-strictな設定の時の振る舞いをさせるかのようなやつ。
mysql80 10> CREATE TABLE t1 (val varchar(1));
Query OK, 0 rows affected (0.03 sec)
mysql80 10> SELECT @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql80 10> INSERT INTO t1 VALUES ('aa');
ERROR 1406 (22001): Data too long for column 'val' at row 1
mysql80 10> INSERT IGNORE INTO t1 VALUES ('aa');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql80 10> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'val' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql80 10> SELECT * FROM t1;
+------+
| val |
+------+
| a |
+------+
1 row in set (0.00 sec)
なんか前者の動作(先勝ち)だけを期待していたのに、後者の動作(non-strict)も一緒についてきちゃってしょんぼりなんてネタがどこかにあったようななかったような気がする。
pt-oscやgh-ostで使う、 INSERT IGNORE INTO .. SELECT .. LOCK IN SHARE MODE でたまにロックタイムアウトしてエラるのは、 IGNORE は飽くまで INSERT にかかっているのであって、 SELECT のエラーを握りつぶす訳ではないから。
mysql80 11> SELECT * FROM t2 FOR UPDATE; -- 別トランザクションでロックして
+----+
| pk |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
mysql80 10> INSERT IGNORE INTO t1 SELECT pk FROM t2; -- ロックをタイムアウトさせる
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
さてここまでは良くて。
NOT NULL なカラムに明示的に NULL を入れようと(DEFAULT句があろうとも)すると当然怒られる。これを避けるには「明示的な NULL 」ではなくて「単にリストに指定しない」ようにしてやれば良くて。
mysql80 12> CREATE TABLE t3 (num int, val varchar(1) NOT NULL DEFAULT 'z');
Query OK, 0 rows affected (0.02 sec)
mysql80 12> INSERT INTO t3 VALUES (1, NULL);
ERROR 1048 (23000): Column 'val' cannot be null
mysql80 12> INSERT INTO t3 (num) VALUES (1);
Query OK, 1 row affected (0.00 sec)
mysql80 12> SELECT * FROM t3;
+------+-----+
| num | val |
+------+-----+
| 1 | z |
+------+-----+
1 row in set (0.00 sec)
これを間違って INSERT IGNORE INTO に食わせるとこうなる。
mysql80 12> INSERT IGNORE INTO t3 VALUES (2, NULL);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql80 12> SHOW WARNINGS;
+---------+------+-----------------------------+
| Level | Code | Message |
+---------+------+-----------------------------+
| Warning | 1048 | Column 'val' cannot be null |
+---------+------+-----------------------------+
1 row in set (0.00 sec)
mysql80 12> SELECT * FROM t3;
+------+-----+
| num | val |
+------+-----+
| 1 | z |
| 2 | | <-- NOT 'z'
+------+-----+
2 rows in set (0.00 sec)
NOT NULL vs NULLをIGNOREが検知して、NULLを「varcharの暗黙のデフォルト値である空文字列」に変換してから実際にテーブルに渡すので、テーブルから見ると「未指定だったらDEFAULT値を割り当てるけど空文字列が指定されてるから受け取ったそのままを入れる」という感じになるんだと思う。
緩く業務連絡なんですが、 pt-online-schema-change が1048で落ちるのは、これを避けるためにIGNOREではちゃんと無視されるけど敢えて SHOW WARNINGS を拾ってエラーにしている、そういう仕様でした。
0 件のコメント :
コメントを投稿