データ移行におけるMySQLのインサート失敗時の動作検証

フリーランスエンジニアの[@haya_go](https://x.com/haya_go)です。是非、Xでフォローお願いします！


直近でスプレッドシートのデータをMySQLデータベースに移行する必要があり、移行方法について検証したので内容を記載します。（小ネタです）

# 検証したいこと
今回のデータ移行はバッチ処理等を実装するのではなく、「スプレッドシート -> テキスト出力 -> SQLでレコード挿入」の流れで行うことを前提とします。

その際に、データの一貫性を保つために、下記の点を担保したいと思いました。

- 不正な値を含むレコードを挿入しようとした場合はエラーになること
  - DBに不正な値を含むレコードが入らず、また不正な値の代わりにデフォルト値やNULLが入らないことを担保したい
- 全レコードの挿入に成功するか、全レコードの挿入に失敗するかのどちらかになること
  - 中途半端にレコードが挿入されてしまうことを防ぎたい
- 挿入に失敗した場合は、どのレコードの値が不正だったか分かること
  - できれば一度に不正なレコードの全てを知りたい
# 環境
- MySQL8.0

# 今回検証するデータの挿入方法

1. 一件ずつINSERTする
1. 全件まとめてINSERTする（VALUES構文を使う）
1. 1. LOAD DATAを使う

# 検証の準備
テストのためこのようなテーブルを作成します。
```
CREATE TABLE `test` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `varchar_col` VARCHAR(255) NOT NULL,
  `date_col` DATE,
  `boolean_col` BOOLEAN,
  `int_col` INT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
```

## 一件ずつINSERTを検証
テストデータを用意します。このテストデータは2行目と4行目のレコードのカラムに不正な値が存在します。
``` insert_test.sql
INSERT INTO test (varchar_col, date_col, boolean_col, int_col) VALUE ('レコード1', '2024-06-01', 1, 123);
INSERT INTO test (varchar_col, date_col, boolean_col, int_col) VALUE ('レコード2', '2024年6月', 'ブーリアン', 'イント');
INSERT INTO test (varchar_col, date_col, boolean_col, int_col) VALUE ('レコード3', '2024-06-02', 0, 456),
INSERT INTO test (varchar_col, date_col, boolean_col, int_col) VALUE ('レコード4', '2024/6', 'boolean', 'int');
```

```
mysql -uroot -p testdb < insert_test.sql 
ERROR 1292 (22007) at line 2: Incorrect date value: '2024年6月' for column 'date_col' at row 1
```

エラーが発生して、どの行の値が不正であるか出力されました。ただし、不正なレコードは2行目と4行目のあるものの、最初にエラーになったレコード（2行目のレコード）しか表示されない点が微妙です。

```
select * from test;
+----+---------------+------------+-------------+---------+
| id | varchar_col   | date_col   | boolean_col | int_col |
+----+---------------+------------+-------------+---------+
| 15 | レコード1     | 2024-06-01 |           1 |     123 |
+----+---------------+------------+-------------+---------+
1 row in set (0.000 sec)
```
エラーが発生した行より前のレコードはDBに挿入され、それ以降のレコードはDBに挿入されません。

中途半端にレコードが入ってしまうのを防ぐには、トランザクションを張る必要があります。
``` insert_with_transaction.sql
START TRANSACTION;
INSERT INTO test (varchar_col, date_col, boolean_col, int_col) VALUE ('レコード1', '2024-06-01', 1, 123);
INSERT INTO test (varchar_col, date_col, boolean_col, int_col) VALUE ('レコード2', '2024年6月', 'ブーリアン', 'イント');
INSERT INTO test (varchar_col, date_col, boolean_col, int_col) VALUE ('レコード3', '2024-06-02', 0, 456);
INSERT INTO test (varchar_col, date_col, boolean_col, int_col) VALUE ('レコード4', '2024/6', 'boolean', 'int');
COMMIT;
```
```
mysql -uroot -p testdb < insert_with_transaction.sql 
ERROR 1292 (22007) at line 3: Incorrect date value: '2024年6月' for column 'date_col' at row 1
```
```
select * from test;
Empty set (0.001 sec)
```




##  全件まとめてINSERT（VALUES構文）を検証
テストデータを用意します。このテストデータも2行目と4行目のレコードのカラムに不正な値が存在します。
```
INSERT INTO test (varchar_col, date_col, boolean_col, int_col) VALUES
('レコード1', '2024-06-01', 1, 123),
('レコード2', '2024年6月', 'ブーリアン', 'イント'),
('レコード3', '2024-06-02', 0, 456),
('レコード4', '2024/6', 'boolean', 'int');
```

```
mysql -uroot -p testdb < insert_bulk_test.sql 
ERROR 1292 (22007) at line 1: Incorrect date value: '2024年6月' for column 'date_col' at row 2
```
エラーが発生して、どの行の値が不正であるか出力されました。ただし、不正なレコードは2行目と4行目のあるものの、最初にエラーになったレコード（2行目のレコード）しか表示されない点が微妙です。

```
select * from test;
Empty set (0.000 sec)
```
全てのレコードがDBに挿入されていないことが確認できました。

## LOAD DATAを検証
テストデータを用意します。このテストデータは2行目と4行目のレコードのカラムに不正な値が存在します。
``` test.tsv
id	varchar_col	date_col	boolean_col	integer_col
レコード1	2024-06-01	1	123
レコード2	2024年6月	ブーリアン	イント
レコード3	2024-06-02	0	456
レコード4	2024/6	boolean	int
```

```
LOAD DATA LOCAL INFILE 'test.tsv' INTO TABLE test FIELDS TERMINATED BY '\t' IGNORE 1 LINES (varchar_col, date_col, boolean_col, int_col);
Query OK, 4 rows affected, 6 warnings (0.011 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 6
```

```
select * from test;
+-----+---------------+------------+-------------+---------+
| id  | varchar_col   | date_col   | boolean_col | int_col |
+-----+---------------+------------+-------------+---------+
| 112 | レコード1     | 2024-06-01 |           1 |     123 |
| 113 | レコード2     | 0000-00-00 |           0 |       0 |
| 114 | レコード3     | 2024-06-02 |           0 |     456 |
| 115 | レコード4     | 0000-00-00 |           0 |       0 |
+-----+---------------+------------+-------------+---------+
4 rows in set (0.001 sec)
```

エラーは発生せず、全てのレコードが挿入されてしまいました。なお、不正な値はデフォルト値に変更されていました。

LOAD DATAの際にwarningsが出ていたので、どのレコードに不正な値が含まれていたか確認はできました。前述の方法とは異なり、不正なレコードの全て（2行目と4行目のレコードの両方）が出力されています。そのため、データ全体から不正なレコードを一気に確認できます。

```
SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1265
Message: Data truncated for column 'date_col' at row 2
*************************** 2. row ***************************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: 'ブーリアン' for column 'boolean_col' at row 2
*************************** 3. row ***************************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: 'イント' for column 'int_col' at row 2
*************************** 4. row ***************************
  Level: Warning
   Code: 1265
Message: Data truncated for column 'date_col' at row 4
*************************** 5. row ***************************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: 'boolean' for column 'boolean_col' at row 4
*************************** 6. row ***************************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: 'int' for column 'int_col' at row 4
6 rows in set (0.000 sec)
```

WARNINGが出ていたらトランザクションをロールバックするようなスクリプトを書くことで、1件でもレコードを正しく挿入できなかった場合は切り戻す、ということが可能そうです。



# 結論
冒頭にも記載しましたが、今回は下記の点を重視して検証しました。
- 不正な値を含むレコードを挿入しようとした場合はエラーになること
- 全レコードの挿入に成功するか、全レコードの挿入に失敗するかのどちらかになること
- 挿入に失敗した場合は、どのレコードの値が不正だったか分かること

これらを踏まえ検証の結果、今回はLOAD DATAの方法が良さそうです。というのも、warningsを確認することで不正なレコードを一度に全て検知できるのと、warningsの有無によってトランザクションをロールバックすれば、不正なデータが入ってしまうことを防げるためです。