54
44

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

csvをLOAD DATA INFILEするときよく発行するクエリ

Last updated at Posted at 2013-08-23

たまに依頼があるLOAD DATA INFILEでの作業

event_history
CREATE TABLE event_hisotry(
  `id` bigint unsigned not null auto_increment,
  `member_id`, bigint unsigned not null,
  `event_id` tinyint unsigned not null,
  `created_at` datetime not null,
  PRIMARY KEY(`id`),
  KEY `key_1` (`member_id`, `timestamp1)
)Engine=InnoDB

みたいなテーブルに

"member_id","event_id","created_at"
"100","23","20130823100000"
"100","24","20130823101510"
"101","23","20130823095959"
...

みたいなcsvをいれてくれという依頼。

  • 1行目に取り込む必要がないものが入っている
  • 各fieldがダブルクォーテーションで囲まれている
  • auto_incrementのfieldが省略されている。

発行するクエリは

LOAD DATA INFILE '/data/mysql_backup/filename.csv' INTO TABLE event_hisotry 
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' (member_id, event_id, `created_at`);

これでOK。

54
44
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
54
44

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?