MySQLのload dataで日付型にnullをセットする

皆大好きLOAD DATAについてのお話です〜

f:id:treeapps:20180418131549p:plain

LOAD DATAコマンドはinsertと比較して10倍以上のパフォーマンスでデータを取り込む事ができます。しかし、いろいろ癖があるため、扱いにくい点があります。

本題は日付型にnullをセットする事ですが、この際いろいろ見て行きたいと思います。

LOAD DATAでトランザクションは効くか
LOAD DATAする時にデータの加工もできるか
LOAD DATAでnull値を設定する方法その１
LOAD DATAでnull値を設定する方法その2

LOAD DATAでトランザクションは効くか

ずばり効きます。

しかし5.1等の古いバージョンでは効きません。もし効かない場合、beginとcommitの間にALTER TABLE 等のDDLが挟まってないか確認して下さい。

DDLは暗黙commitが実行されるため、トランザクションは強制コミットされます。

次の各ステートメント(そしてそれらの同義語)は、まるでステートメントを実行する前に COMMIT を行ったかのように、暗黙にトランザクションを終了します。
ALTER FUNCTION, ALTER PROCEDURE, ALTER TABLE, BEGIN, CREATE DATABASE, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, DROP DATABASE, DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP TABLE, LOAD DATA INFILE LOCK TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START TRANSACTION, TRUNCATE TABLE, UNLOCK TABLES.
MySQL 5.1.3 から、ALTER VIEW、CREATE TRIGGER、CREATE USER、CREATE VIEW、DROP TRIGGER、DROP USER、DROP VIEW、そして RENAME USER ステートメントは暗黙的なコミットを引き起こすようになりました。
http://dev.mysql.com/doc/refman/5.1/ja/implicit-commit.html

LOAD DATAする時にデータの加工もできるか

できます。

LOAD DATAにはSET句があり、そこで普通のSQLが書けるのです。後述のLOAD DATAのサンプルに記載しているので、参考にしてみて下さい。

LOAD DATAでnull値を設定する方法その１

方法1は、csv側に「\N」という文字列を指定すると、load dataした時にnullとして取り込めます。

しかしcsv側を編集できる場面は少ないし、元のcsvをMySQL用に修正する事は好ましくありません。従って私は推奨しません。

LOAD DATAでnull値を設定する方法その2

方法2です。これが本命です。

何も設定しないでcsvの日付項目にnullを設定すると「0000-00-00 00:00:00」という文字列がセットされます。

実際以下のテーブルでそうなる事を確認しています。

drop table if exists hoge;
create table hoge (
    v varchar(10)
    ,d datetime
    ,t time
) engine=innodb charset=utf8mb4;

投入するcsvは以下の通りです。

aaa,2013-06-12 12:34:56,12:34:56
,,

使用するload dataは以下の通りです。

set names utf8mb4;
set autocommit=0;

delete from hoge;

load data
    low_priority
    local infile 'hoge.csv'
    into table hoge
    character set 'utf8'
    fields terminated by ','
    enclosed by ''
    escaped by ''
    lines terminated by '\n'
    ignore 0 lines
set
    v = nullif(v, ''),
    d = nullif(d, ''),
    t = nullif(t, '')
;

commit;

このload dataでcsvを取り込んだ結果は以下の通りです。

mysql> select * from hoge;
+------+---------------------+----------+
| v    | d                   | t        |
+------+---------------------+----------+
| aaa  | 2013-06-12 12:34:56 | 12:34:56 |
| NULL | 0000-00-00 00:00:00 | 00:00:00 |
+------+---------------------+----------+

残念な結果になっていますね。

load dataの時にset句で空文字の場合はnullにするというnullifを設定しているにも関わらず、「0000-00-00 00:00:00」というおなじみの初期値がセットされました。

sql_modeでNO_ZERO_IN_DATEとNO_ZERO_DATEとSTRICT_TRANS_TABLESを指定する手もありますが、これだとエラー扱いされてnullとして取り込めません。

ではどうするか。実はload dataのset句に秘密があるのです。

以下のようにしてみて下さい。

set names utf8mb4;
set autocommit=0;

delete from hoge;

load data
    low_priority
    local infile 'hoge.csv'
    into table hoge
    character set 'utf8'
    fields terminated by ','
    enclosed by ''
    escaped by ''
    lines terminated by '\n'
    ignore 0 lines
set
    v = nullif(v, ''),
    d = nullif(d, '0000-00-00 00:00:00'),
    t = nullif(t, '00:00:00')
;

commit;

set句のnullifの第2引数に注目。

こう書いてあげると、なんとnullになるのです。実際にこのload dataで取り込んだ結果は以下の通りです。

mysql> select * from hoge;
+------+---------------------+----------+
| v    | d                   | t        |
+------+---------------------+----------+
| aaa  | 2013-06-12 12:34:56 | 12:34:56 |
| NULL | NULL                | NULL     |
+------+---------------------+----------+