MySQL 匯入 CSV

如果有大量的外部資料要匯入，一個一個 insert 肯定很累
就算寫成 SQL 腳本也不是很方便
而且有時候資料的提供者是客戶或不懂程式的人
總不能叫他們學 SQL 吧

最容易的做法就是萬能 Excel
我們可以提供範本 CSV 檔案給客戶
他們所見即所得照著輸入
甚至是他們原本就在使用的 Excel 直接複製過來
正確又快速
取得檔案後就能輕鬆導入(import)資料庫了

語法

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

範例：

LOAD DATA INFILE 'C:/data.csv'
INTO TABLE user
fields terminated BY ','
lines terminated BY '\n'
(id, name, @email)
;

疑難排解

ERROR 3948

報錯

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

原因

解決

一、開啟設定

載入文件設置

show variables like 'local_infile';

開啟設置

SET GLOBAL local_infile=on;

二、若不成功則要登入時設定

SET GLOBAL local_infile=1;
quit;

mysql --local-infile=1 -u root -p

row 1 doesn’t contain data for all columns

原因

欄位資料不夠
例如你有三個欄位：id, name, email
擷取一行只找到兩個欄位的值
就會報這個錯誤

如果明明有給到三個欄位的值
卻仍然出現這個錯誤
通常就是 parsing 出錯
造成資料庫擷取的值不一致
應檢查 STARTING、TERMINATED、ENCLOSED、ESCAPED 相關設定