CSVファイルをMySQLデータベースにインポートする
・CSVファイルの作成
メモ帳 or エクセル からCSVファイル作成
population.csv
1,"福岡",5151000
2,"佐賀",832000
3,"長崎",1377000
4,"熊本",1786000
5,"大分",1166000
6,"宮崎",1104000
7,"鹿児島",1648000
8,"沖縄",1433000
・テーブルの作成
mysql> create table population(
-> id int(10),
-> prefectures varchar(50),
-> population int(10)
-> );
Query OK, 0 rows affected, 2 warnings (0.21 sec)
mysql> show create table population;
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| population | CREATE TABLE `population` (
`id` int DEFAULT NULL,
`prefectures` varchar(50) DEFAULT NULL,
`population` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> Load Data Local Infile '/csvdata/population.csv'
-> Into Table population
-> Fields Terminated By ','
-> Optionally Enclosed By '"';
Query OK, 8 rows affected (0.03 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from population;
+------+-------------+------------+
| id | prefectures | population |
+------+-------------+------------+
| 1 | 福岡 | 5151000 |
| 2 | 佐賀 | 832000 |
| 3 | 長崎 | 1377000 |
| 4 | 熊本 | 1786000 |
| 5 | 大分 | 1166000 |
| 6 | 宮崎 | 1104000 |
| 7 | 鹿児島 | 1648000 |
| 8 | 沖縄 | 1433000 |
+------+-------------+------------+
8 rows in set (0.01 sec)
・ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sidesの解決方法
＊セキュリティ強化のため、8.0から LOAD DATA INFILE LOCALはデフォルトで無効になっているらしい
mysql> SELECT @@local_infile;
+----------------+
| @@local_infile |
+----------------+
| ０ |
+----------------+
1 row in set (0.00 sec)
→LOAD DATA INFILE LOCALの確認
mysql>SET PERSIST local_infile= 1;
→有効にする
mysql> SELECT @@local_infile;
+----------------+
| @@local_infile |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)
＊その後、以下の要領で再ログイン
>mysql -u root -p --local_infile=1
Enter password: ************
Welcome to the MySQL monitor. Commands end with ; or \g.
・注意
mysql> Load Data Local Infile "C:\csvdata/goodstable.csv"
-> Into Table goods
-> Fields Terminated By ','
-> Optionally Enclosed By '"';
ERROR 2 (HY000): File 'C:csvdata\goodstable.csv' not found (OS errno 2 - No such file or directory)
C:\にするとエラーが発生するので要注意。
