在MySQL下指令匯入資料初體驗

因為開始使用Docker的關係現在玩系統跟資料庫變得簡單多了，這兩天嘗試把玩MySQL資料庫，但在做匯入資料時卻發生一些障礙，趕緊把過程筆記起來避免日後遺忘:)

先來看目前的p1這個資料表結構

mysql> desc p1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| q1    | varchar(50) | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

嘗試下指令把csv檔案匯入mysql但卻發生錯誤

USE lab;
LOAD DATA LOCAL INFILE  '/123/01.csv'
INTO TABLE p1
CHARACTER SET UTF8
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

錯誤訊息內容如下

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
mysql> show variables like 'local_infile';

查了一下是預設本地檔案沒有啟用的關係

mysql> show variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

下指令去啟用一下

mysql> SET GLOBAL local_infile=1;
Query OK, 0 rows affected (0.00 sec) 

然後嘗試再匯入但還是一樣有錯 Orz

mysql> LOAD DATA LOCAL INFILE  '/123/01.csv'
    -> INTO TABLE p1
    -> CHARACTER SET UTF8
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 ROWS;
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

根據錯誤訊息繼續查，得知要在登入mysql時就要指定參數

mysql -u root -p --local-infile

然後重新登入mysql在匯入一次就可以了

mysql> LOAD DATA LOCAL INFILE  '/123/01.csv'
    -> INTO TABLE p1
    -> CHARACTER SET UTF8
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n'
    -> IGNORE 1 ROWS;
Query OK, 5945 rows affected, 1 warning (0.12 sec)
Records: 5945  Deleted: 0  Skipped: 0  Warnings: 1

確認一下筆數資料，確實資料真的有匯入

mysql> select count(*) from p1;
+----------+
| count(*) |
+----------+
|     5945 |
+----------+
1 row in set (0.01 sec)

參考資料

1. [MySQL / MariaDB] CSV檔匯入資料表
2. MYSQL load data 2068错误