MySQL 使用 LOAD DATA INFILE 遇到 duplicate 與 secure-file-priv 解法

MySQL 想要 Import 大量資料進去資料庫，很常遇到 CSV 的資料需要做這種動作，MySQL 預設就可以使用 LOAD DATA 的方式來達成。

• 註：此篇的 CSV 是使用 Tab 分隔的。

MySQL 使用 LOAD DATA INFILE 遇到 duplicate 與 secure-file-priv 解法

MySQL 使用 LOAD DATA 的方式，會遇到指定路徑也無法載入的問題，遇到下述錯誤：

• mysql> LOAD DATA INFILE 'load.csv' INTO TABLE table_name FIELDS TERMINATED BY "\t";
  • ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
• mysql> LOAD DATA INFILE '/home/user/load.csv' INTO TABLE table_name FIELDS TERMINATED BY "\t";
  • ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

查看系統的設定：

• mysql> show global variables like '%secure_file_priv%';

  +------------------+-----------------------+
  | Variable_name    | Value                 |
  +------------------+-----------------------+
  | secure_file_priv | /var/lib/mysql-files/ |
  +------------------+-----------------------+
  1 row in set (0.00 sec)

所以將 csv 放到 /var/lib/mysql-files/ 即可解決此問題。

• mysql> LOAD DATA INFILE '/var/lib/mysql-files/load.csv' INTO TABLE table_name FIELDS TERMINATED BY "\t";
  • Query OK, 1953000 rows affected, 65535 warnings (3 hours 29 min 41.16 sec)
  • Records: 1954000 Deleted: 0 Skipped: 818 Warnings: 46863016

若沒辦法改變路徑的話，只能調整 MySQL 設定，secure_file_priv 要怎麼調整呢？

MySQL 文件說明：secure_file_priv 限制 LOAD DATA、SELECT ... INTO OUTFILE、LOAD_FILE() 等等，會有安全考量，所以鎖定目錄比較安全。

secure_file_priv 是唯讀的，所以不能使用 set global 修改，只能修改 my.cnf 後，重啟 MySQL

• mysql> SET GLOBAL secure_file_priv='';
  • ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable

secure_file_priv 有下述幾個設定值：

• secure_file_priv = NULL：MySQL 不允許 Import 或 Export。
• secure_file_priv = /tmp：限制 MySQL 只能在 /tmp 執行 Import 或 Export。
• secure_file_priv = ''：不做任何限制，可以隨意的 Import 或 Export。

然後在大量 Import 過程，若有重複 Key 的情況，會出現下述錯誤：

• mysql> LOAD DATA INFILE '/var/lib/mysql-files/load.csv' INTO TABLE table_name FIELDS TERMINATED BY "\t";
  • ERROR 1062 (23000): Duplicate entry '8014' for key 'PRIMARY'

LOAD DATA 遇到 Duplicate entry 解法

遇到 Duplicate entry 的問題，LOAD DATA 可以使用 REPLACE 或 IGNORE 來處理。

• REPLACE：遇到重複就蓋掉
• IGNORE：遇到重複就忽略掉

語法如下：

• LOAD DATA INFILE '/var/lib/mysql-files/load.csv' IGNORE INTO TABLE table_name FIELDS TERMINATED BY "\t";

LOAD DATA 通常會很久，想知道花費多少時間，有個簡單的作法可以加入：(MySQL 要查看 Load data 花費多少時間)

1. mysql> SELECT NOW();

   +---------------------+
   | now()               |
   +---------------------+
   | 2020-10-22 09:25:18 |
   +---------------------+
   1 row in set (0.00 sec)

2. mysql> LOAD DATA INFILE ...;
3. mysql> SELECT NOW();

相關網頁

• MYSQL LOAD DATA INFILE ignore duplicate rows (autoincrement as primary key)
• High-speed inserts with MySQL