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();

相關網頁

關於「Tsung」

對新奇的事物都很有興趣, 喜歡簡單的東西, 過簡單的生活.
分類: My_Note-Unix,標籤: , , , , , , , 。這篇內容的永久連結

發表迴響

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料