文系プログラマによるTIPSブログ

文系プログラマ脳の私が開発現場で学んだ事やプログラミングのTIPSをまとめています。

MySQLのLOAD DATAとトランザクション:まとめ

知っておいた方がいいかもしれません。


f:id:treeapps:20180418131549p:plain

前回のトピック、自分で見なおしてちょっと解りづらかったので、もう一度まとめてみようと思います。

お題は、LOAD DATAとトランザクション、です。

テストデータ

まず、使う材料は以下の通り。engineとload data localは適宜書き換えます。

1	11	111
2	22	222
1	33	333
CREATE TABLE `load_test1` (
  `i1` int(11) NOT NULL DEFAULT '0',
  `i2` int(11) DEFAULT NULL,
  `i3` int(11) DEFAULT NULL,
  PRIMARY KEY (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
set autocommit=0;
load data local infile 'load.tsv'  into table load_test1 fields terminated by ',' lines terminated by '\n';
commit;

engine=innodb、load data local infileの場合

結果は、エラー無し、トランザクション無効。

$ mysql -uroot test < loaddata.sql 
$ 
mysql> select * from load_test1;
+----+------+------+
| i1 | i2   | i3   |
+----+------+------+
|  1 | NULL | NULL |
|  2 | NULL | NULL |
+----+------+------+
2 rows in set (0.00 sec)

engine=innodb、load data infileの場合

結果は、エラー有り、トランザクション有効。

$ mysql -uroot test < loaddata.sql 
ERROR 1062 (23000) at line 3: Duplicate entry '1' for key 'PRIMARY'
mysql> select * from load_test1;
Empty set (0.00 sec)

engine=myisam、load data local infileの場合

結果は、エラー無し、トランザクション無効。

$ mysql -uroot test < loaddata.sql 
$ 
mysql> select * from load_test1;
+----+------+------+
| i1 | i2   | i3   |
+----+------+------+
|  1 | NULL | NULL |
|  2 | NULL | NULL |
+----+------+------+
2 rows in set (0.00 sec)

engine=myisam、load data infileの場合

結果は、エラー有り、トランザクション有効。

$ mysql -uroot test < loaddata.sql 
ERROR 1062 (23000) at line 3: Duplicate entry '1' for key 'PRIMARY'
mysql> select * from load_test1;
Empty set (0.00 sec)

雑感

localオプションを付けるとトランザクションが無効になり、付けないと有効になっていますね。

この辺マニュアルに明示した方がいい気がする。。

ちなみにlocalを付けない場合ですが、

AmazonRDSのように、DBサーバのファイルシステムに直接アクセスできない環境の場合は使えません。

ちょっと邪道ですがlocalを付ける場合は、csvの行数(末尾の空行除く)と、load data後のselect count(*)を比較すれば、一応差分を調べられますね。