LOAD DATA INFILE構文メモ

warningsがでない文を作成するのに時間かかってしまったのでメモしておく。
MySQL5.6を利用。

参考

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.6 LOAD DATA INFILE 構文
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.7 日付および時間関数
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.4 制御フロー関数

スキーマ、テーブル

CREATE DATABASE IF NOT EXISTS test;

CREATE TABLE IF NOT EXISTS test.performance_check (
    id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(1024),
    create_date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    p_id1 BIGINT,
    p_id2 BIGINT,
    area INT(1) NOT NULL,
    status INT(1) NOT NULL,
    PRIMARY KEY(id)
)

インポートするデータ

# /home/mfham/work/data.csv
"id","name","create_date","p_id1","p_id2","area","status"
1,"mfham1",2017-09-01 10:10:10,1000,,1,1
2,"mfham2",2017-09-02 10:10:10,1000,1000,1,1

LOAD DATA INFILE構文

• オートインクリメントのidは処理しない
• NULLIFを利用する

create_date = @v3 でも問題なかった。
%H:%i:%s は %Tでも良さそう。

LOAD DATA LOCAL INFILE '/home/mfham/work/data.csv'
    INTO TABLE test.performance_check
    FIELDS
        TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"'
    IGNORE 1 LINES
    (@v1, @v2, @v3, @v4, @v5, @v6, @v7)
    SET
        name = NULLIF(@v2, ''),
        create_date = STR_TO_DATE(@v3, '%Y-%m-%d %H:%i:%s'),
        p_id1 = NULLIF(@v4, ''),
        p_id2 = NULLIF(@v5, ''),
        area = @v6,
        status = @v7;

実行結果

mysql> CREATE TABLE IF NOT EXISTS test.performance_check (
    ->     id BIGINT NOT NULL AUTO_INCREMENT,
    ->     name VARCHAR(1024),
    ->     create_date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->     p_id1 BIGINT,
    ->     p_id2 BIGINT,
    ->     area INT(1) NOT NULL,
    ->     status INT(1) NOT NULL,
    ->     PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> LOAD DATA LOCAL INFILE '/home/mfham/work/data.csv'
    ->     INTO TABLE test.performance_check
    ->     FIELDS
    ->         TERMINATED BY ','
    ->         OPTIONALLY ENCLOSED BY '"'
    ->     IGNORE 1 LINES
    ->     (@v1, @v2, @v3, @v4, @v5, @v6, @v7)
    ->     SET
    ->         name = @v2,
    ->         create_date = STR_TO_DATE(@v3, '%Y-%m-%d %H:%i:%s'),
    ->         p_id1 = NULLIF(@v4, ''),
    ->         p_id2 = NULLIF(@v5, ''),
    ->         area = @v6,
    ->         status = @v7;
Query OK, 2 rows affected (0.15 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test.performance_check;
+----+--------+---------------------+-------+-------+------+--------+
| id | name   | create_date         | p_id1 | p_id2 | area | status |
+----+--------+---------------------+-------+-------+------+--------+
|  1 | mfham1 | 2017-09-01 10:10:10 |  1000 |  NULL |    1 |      1 |
|  2 | mfham2 | 2017-09-02 10:10:10 |  1000 |  1000 |    1 |      1 |
+----+--------+---------------------+-------+-------+------+--------+
2 rows in set (0.00 sec)