やりたいこと
- MySQL8のテーブルにCSVファイルをまるごとインポートしたい。
- mysqlのコマンドラインを使いたい。
環境
- CentOS8+MySQL8
DBの構成
CREATE DATABASE yamato CHARACTER SET utf8;
usersテーブルが下記の構造で作成済みであるとする。
CREATE TABLE `users` (
`id` bigint unsigned NOT NULL,
`gender` varchar(255) NOT NULL,
`age` int unsigned NOT NULL,
`marriage` varchar(255) NOT NULL,
`pref` varchar(255) NOT NULL,
`job` varchar(255) NOT NULL,
`hobby` varchar(255) NOT NULL,
`y` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
この、yamato.users に、CSVファイルからデータをインポートしたい。
CSVファイルの構造
users.csv
文字コードはutf8とする。
id,性別,年齢,婚姻,都道府県,職業,趣味,y
1,男性,39,既婚,愛媛県,フリーランス,映画,no
2,男性,79,既婚,滋賀県,会社役員,アウトドア,no
3,男性,29,未婚,京都府,無職,音楽,no
4,女性,28,既婚,新潟県,会社役員,テレビゲーム,no
5,女性,73,未婚,青森県,会社員,映画,no
6,男性,71,未婚,石川県,学生,オートバイ,yes
7,女性,78,未婚,群馬県,学生,読書,no
8,女性,65,既婚,徳島県,フリーランス,映画,no
9,女性,34,既婚,宮崎県,自営業,アウトドア,no
10,女性,69,既婚,茨城県,会社員,映画,no
11,男性,67,既婚,山梨県,フリーランス,音楽,no
取り込み
CSVファイルの一行目（ヘッダ）を削除する。
id,性別,年齢,婚姻,都道府県,職業,趣味,y
をviで削除する。ちなみに、
LOAD DATA LOCAL INFILE
実行時、末尾に
IGNORE 1 LINES
を付与すれば、ヘッダ一行を飛ばしてくれるので、その場合はviで削除しなくてもOK。
CSVファイルを下記に置く。
/home/yamato/csv/users.csv
コマンドラインからMySQLに接続する。
MySQLにrootで接続する。
--local-infile=1 を付与しないと取り込めないので注意する。
mysql --local-infile=1 -u root -p
下記コマンドで取り込む。
mysql>
SET GLOBAL local_infile=on;
load data local infile "/home/yamato/csv/users.csv" into table yamato.users fields terminated by ',' optionally enclosed by '"';
結果を確認する。CSVの5万行がすべて正常に取り込まれた。
Query OK, 50000 rows affected (0.80 sec)
Records: 50000 Deleted: 0 Skipped: 0 Warnings: 0
テーブルの中身を確認する。
mysql> select * from users limit 10;
+----+--------+-----+----------+-----------+--------------------+--------------------+-----+
| id | gender | age | marriage | pref | job | hobby | y |
+----+--------+-----+----------+-----------+--------------------+--------------------+-----+
| 1 | 男性 | 39 | 既婚 | 愛媛県 | フリーランス | 映画 | no |
| 2 | 男性 | 79 | 既婚 | 滋賀県 | 会社役員 | アウトドア | no |
| 3 | 男性 | 29 | 未婚 | 京都府 | 無職 | 音楽 | no |
| 4 | 女性 | 28 | 既婚 | 新潟県 | 会社役員 | テレビゲーム | no |
| 5 | 女性 | 73 | 未婚 | 青森県 | 会社員 | 映画 | no |
| 6 | 男性 | 71 | 未婚 | 石川県 | 学生 | オートバイ | yes |
| 7 | 女性 | 78 | 未婚 | 群馬県 | 学生 | 読書 | no |
| 8 | 女性 | 65 | 既婚 | 徳島県 | フリーランス | 映画 | no |
| 9 | 女性 | 34 | 既婚 | 宮崎県 | 自営業 | アウトドア | no |
| 10 | 女性 | 69 | 既婚 | 茨城県 | 会社員 | 映画 | no |
+----+--------+-----+----------+-----------+--------------------+--------------------+-----+
10 rows in set (0.00 sec)
mysql> select count(id) from users;
+-----------+
| count(id) |
+-----------+
| 50000 |
+-----------+
1 row in set (0.06 sec)