検証したいこと

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

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

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

環境

MySQL8.0

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

一件ずつINSERTする
全件まとめてINSERTする（VALUES構文を使う）
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の有無によってトランザクションをロールバックすれば、不正なデータが入ってしまうことを防げるためです。