前言

LOAD DATA 是 MySQL 中一個非常強(qiáng)大且高效的指令，用于將文本文件（如 CSV、TSV）的內(nèi)容快速批量地讀入到數(shù)據(jù)庫表中。相比于使用多個 INSERT 語句，它在處理大量數(shù)據(jù)時速度要快幾個數(shù)量級。

核心功能

它的核心功能就是：將服務(wù)器主機(jī)上的一個文本文件，高速地導(dǎo)入到指定的數(shù)據(jù)庫表中。

基本語法

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']       -- 轉(zhuǎn)義字符，默認(rèn)是'\'
    ]
    [LINES
        [STARTING BY 'string']    -- 行開始符，很少用
        [TERMINATED BY 'string']  -- 行結(jié)束符，例如 '\n' 或 '\r\n'
    ]
    [IGNORE number {LINES | ROWS}] -- 忽略文件開頭的幾行（如標(biāo)題行）
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name = expr
        [, col_name = expr] ...]

關(guān)鍵參數(shù)詳解

1. LOCAL

   • 有 LOCAL：客戶端命令。文件位于客戶端主機(jī)上（即你執(zhí)行 mysql 命令的機(jī)器）。文件內(nèi)容會通過客戶端連接發(fā)送到服務(wù)器。安全性稍低，因?yàn)榉?wù)器無法控制客戶端文件。
   • 無 LOCAL：服務(wù)器端命令。文件必須位于服務(wù)器主機(jī)上，并且運(yùn)行 MySQL 服務(wù)的用戶（通常是 mysql）必須有讀取該文件的權(quán)限。這種方式更快，但需要直接訪問服務(wù)器文件系統(tǒng)。

2. INFILE 'file_name'

   • 指定源文件的路徑。對于服務(wù)器端加載，路徑可以是絕對路徑或相對路徑（相對於 secure_file_priv 系統(tǒng)變量指定的目錄）。

3. INTO TABLE tbl_name

   • 指定目標(biāo)表的名字。

4. FIELDS 子句 (定義每個字段的格式)

   • TERMINATED BY：字段之間的分隔符。常見的有 ',' (CSV)， '\t' (制表符，TSV)， '|' 等。
   • ENCLOSED BY：字段的包圍符。常用 '"'。如果使用 OPTIONALLY，則只用于字符串類型的字段。
   • ESCAPED BY：轉(zhuǎn)義字符。默認(rèn)是反斜杠 \。用于轉(zhuǎn)義特殊字符，例如字段中包含分隔符時。

5. LINES 子句 (定義每行的格式)

   • TERMINATED BY：行的結(jié)束符。在 Windows 上創(chuàng)建的文件通常是 '\r\n'，在 Linux/macOS 上是 '\n'?，F(xiàn)代 MySQL 通常能自動處理。
   • STARTING BY：行的開始符，很少使用。

6. IGNORE number LINES

   • 極其常用。用于跳過文件開頭的 number 行。例如，如果文本文件第一行是列標(biāo)題（如 id,name,age），則使用 IGNORE 1 LINES 來跳過它。

7. REPLACE 和 IGNORE

   • REPLACE：如果導(dǎo)入的數(shù)據(jù)與表中現(xiàn)有記錄的主鍵 (Primary Key) 或唯一索引 (Unique Index) 沖突，則用新數(shù)據(jù)替換掉原有數(shù)據(jù)。
   • IGNORE：（默認(rèn)行為）如果發(fā)生沖突，則忽略（丟棄）當(dāng)前導(dǎo)入的行，并繼續(xù)處理下一行。
   • 如果兩者都不指定，沖突會導(dǎo)致錯誤，并停止導(dǎo)入。

8. CHARACTER SET

   • 指定文件的字符編碼。如果文件編碼與數(shù)據(jù)庫默認(rèn)編碼不同，必須指定。例如，處理中文時如果遇到亂碼，可能需要設(shè)置 CHARACTER SET utf8mb4。

9. 列映射 (col_name, ...) 和 SET

   • 可以在語句最后指定一個列列表，例如 (col1, col2, col3)。這會告訴 MySQL 文件中的數(shù)據(jù)按此順序?qū)?yīng)到這些列。表的其他列將被設(shè)置為默認(rèn)值。
   • 如果文件中的列順序與表結(jié)構(gòu)不同，或者你需要對數(shù)據(jù)進(jìn)行一些簡單的轉(zhuǎn)換，這個功能就非常有用。
   • SET 子句可以用于對列進(jìn)行表達(dá)式計算。例如，如果文件中有日期字符串，你可以用 SET date_column = STR_TO_DATE(@var_date, '%Y-%m-%d') 來轉(zhuǎn)換格式。

工作流程與示例

假設(shè)場景：

我們有一個 employees 表：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10, 2),
    hire_date DATE
);

還有一個 employees_data.csv 文件，內(nèi)容如下：

id,name,department,salary,hire_date
105,'Alice Smith','Engineering',75000,'2022-03-15'
106,'Bob Johnson','Marketing',65000,'2021-11-01'
107,'Charlie Lee','Sales',NULL,'2023-01-20'  -- 注意：salary是NULL

目標(biāo)： 將此 CSV 文件導(dǎo)入到 employees 表中。

步驟 1：處理文件

確保文件格式正確，分隔符為逗號，字符串用單引號包圍（MySQL 默認(rèn)期望雙引號，所以我們需要指定），第一行是標(biāo)題。

步驟 2：執(zhí)行 LOAD DATA 語句

LOAD DATA LOCAL INFILE '/path/to/your/employees_data.csv'
INTO TABLE employees
-- 定義格式：字段用逗號分隔，字符串用單引號包圍
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '\'' 
-- 忽略第一行標(biāo)題
IGNORE 1 LINES 
-- 明確指定列名（順序和文件中的列順序一致）
(id, name, department, salary, hire_date); 

注意： 如果字符串包圍符是雙引號 "，則只需寫 ENCLOSED BY '"'。

重要注意事項(xiàng)與最佳實(shí)踐

1. secure_file_priv 系統(tǒng)變量：

   • 這是 MySQL 的一個安全限制。如果設(shè)置了此變量（通常默認(rèn)是設(shè)置的），LOAD DATA 只能讀寫該變量指定目錄下的文件。
   • 使用 SHOW VARIABLES LIKE 'secure_file_priv'; 查看其值。
   • 對于無 LOCAL 的加載，文件必須放在這個目錄下。
   • 對于有 LOCAL 的加載，此變量不適用。

2. 性能：

   • 在導(dǎo)入前禁用索引（特別是唯一索引）可以極大提升速度。導(dǎo)入完成后再重建索引。

   ALTER TABLE tbl_name DISABLE KEYS;
   -- 執(zhí)行 LOAD DATA ...
   ALTER TABLE tbl_name ENABLE KEYS;
   

   • 使用事務(wù)：將 LOAD DATA 包裝在一個事務(wù)中，要么全部成功，要么全部失敗。

   START TRANSACTION;
   LOAD DATA ...;
   COMMIT;
   

3. 錯誤處理：

   • 仔細(xì)檢查 FIELDS 和 LINES 的配置是否與文件格式完全匹配，一個字符的差別都可能導(dǎo)致整個導(dǎo)入失敗或數(shù)據(jù)錯亂。
   • 首次導(dǎo)入時，可以先用一個小的樣本文件進(jìn)行測試。

4. NULL 值處理：

   • 在文件中，NULL 值應(yīng)該用 \N（反斜杠加大寫 N）表示。如果你的文件里是空字符串或者 NULL 這個詞，可能需要使用 SET 子句進(jìn)行轉(zhuǎn)換，例如 SET salary = NULLIF(@var_salary, '')。

與INSERT的對比

總結(jié)： LOAD DATA INFILE 是 MySQL 數(shù)據(jù)導(dǎo)入的“王牌工具”，專門為高速批量數(shù)據(jù)處理而設(shè)計。只要熟練掌握其語法和選項(xiàng)，它就能成為你數(shù)據(jù)庫管理工作中不可或缺的利器。

總結(jié) 

到此這篇關(guān)于MySQL LOAD DATA與INSERT導(dǎo)入大批量數(shù)據(jù)的文章就介紹到這了,更多相關(guān)MySQL導(dǎo)入大批量數(shù)據(jù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論