はじめに
CSVファイルをMySQLにインポートするSQL文をよく使うのでこのSQL文についてメモします。
やりたいこと
以下のようなCSVファイルがあるとし、これらデータをMySQLの指定したテーブルにインポートします。
上記CSVファイルは、見て分かるとおり以下のカラムを以下の通りの順番で含んでいます。
- ID
- lastkana（姓フリガナ）
- firstkana（名フリガナ）
- lastname（姓）
- firstname（名）
- brithday（誕生日）
- email（メールアドレス）
- tel（電話番号）
前提と環境
以下の環境となります。
- OS : Ubuntu 18.04
- MySQL：5.7.27, for Linux (x86_64)
CSVファイルをMySQLにインポートするSQL文
基本的なSQL文としては以下になります。
mysql> load data local infile "/home/myuser/my.csv " into table mytable fields terminated by ',' optionally enclosed by '"';/home/myuser/my.csvがCSVファイルの絶対パスになります。また、mytableがインポート先となるテーブル名です。
なお、CSVファイルの先頭行はカラム名が入っており、上記SQL文を発行すると先頭行もデータとしてインポートされます。また、CSVファイルのカラム順がそのままMySQL側のテーブルのカラム順通りにインポートされます。したがって、もしインポート先であるmytableのカラムが以下のような場合、
mysql> show columns from mytable;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| firstname | varchar(255) | YES | | NULL | |
| lastname | varchar(255) | YES | | NULL | |
| firstkana | varchar(255) | YES | | NULL | |
| lastkana | varchar(255) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
| tel | varchar(255) | YES | | NULL | |
+--------------+--------------+------+-----+---------+----------------+CSVファイルのカラム順と異なるため、CSVファイルのlastkanaはmytableのfirstnameにインポートされるため注意が必要です。これらの解決方法について以降で説明します。
CSVファイルの先頭行を無視する
以下のようにignore 1 linesを使用します。
mysql> load data local infile "/home/myuser/my.csv " into table mytable fields terminated by ',' optionally enclosed by '"' ignore 1 lines;CSVファイルのカラムとMySQLテーブルのカラムを対応させる
以下のように、CSVファイル側のカラムを任意の変数に割り当てて、それをMySQL側のカラムに対応させることで可能です。以下の@id、@firstname等が変数名です。set句を使ってこの変数とMySQLテーブルのカラム名を対応付けています。
mysql> load data local infile "/home/myuser/my.csv " into table mytable fields terminated by ',' optionally enclosed by '"' ignore 1 lines
(@id,@lastkana,@firstkana,@lastname,@firstname,@birthday,@email,@tel)
set id = @id,
firstname = @firstname,
lastname = @lastname,
firstkana = @firstkana,
lastkana = @lastkana,
birthday = @birthday,
email = @email,
tel = @tel;なお、変数名は上記のように具体的な名前にせずに、以下のようにCSVファイル側のカラム番号でも指定できます。
mysql> load data local infile "/home/myuser/my.csv" into table students fields terminated by ',' optionally enclosed by '"'
ignore 1 lines
(@1,@2,@3,@4,@5,@6,@7,@8)
set id = @1,
firstname = @5,
lastname = @4,
firstkana = @3,
lastkana = @2,
birthday = @6,
email = @7,
tel = @8;まとめ
古いシステムに含まれるデータをCSV形式で書き出して新しいシステムに移行するような機会が多く、よく使うのでまとめてみました。
SPONSORED LINK