warningsがでない文を作成するのに時間かかってしまったのでメモしておく。
MySQL5.6を利用。
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)