在資料庫管理的日常工作中,批次處理是提升效率、減少人為錯誤的關鍵技能。MySQL 提供了多種批次匯入資料的方式,無論是透過命令列重導向、LOAD DATA INFILE 指令,還是結合 mysqlimport 工具,都能讓大量資料的匯入變得輕鬆自如。本文將系統性地介紹這些方法的操作步驟、參數配置與實戰場景,幫助你掌握 MySQL 批次資料操作的核心技巧。
批次處理是指將多條 SQL 指令寫入文字檔,再一次性提交給 MySQL 伺服器執行的非互動式操作模式。與在 mysql 客戶端逐行輸入指令不同,批次處理適用於:
建立一個純文字檔(如 New_Data.sql),內容包含完整的 SQL 陳述式,每句必須以分號結尾:
USE Meet_A_Geek;
INSERT INTO Customers (Customer_ID, Last_Name) VALUES(NULL, "Block");
INSERT INTO Customers (Customer_ID, Last_Name) VALUES(NULL, "Newton");
INSERT INTO Customers (Customer_ID, Last_Name) VALUES(NULL, "Simmons");
注意:檔案副檔名不強制為 .sql,.txt 或其他合法名稱皆可。
確認 MySQL 服務(mysqld)已啟動,然後在終端機執行:
bin/mysql –p < /home/mark/New_Data.sql
輸入密碼後,若腳本語法正確,資料即會寫入資料庫。此方式特別適合利用現有的 SQL Log 快速還原資料,省去手動敲打指令的麻煩。
LOAD DATA INFILE 是在 mysql 命令列或任何支援 API 的程式中直接匯入資料的指令,功能類似 mysqlimport,但更靈活。
USE Meet_A_Geek;
LOAD DATA INFILE "/home/mark/data.sql" INTO TABLE Orders;
若資料檔位於客戶端本機,而 MySQL 服務在遠端伺服器,加上 LOCAL 關鍵字即可避免手動 FTP 上傳:
LOAD DATA LOCAL INFILE "C:/MyDocs/SQL.txt" INTO TABLE Orders;
MySQL 會自動將本地檔案傳輸至伺服器並匯入,極大簡化跨機器部署流程。
| 參數 | 用途 | 範例 |
|---|---|---|
LOW_PRIORITY | 延遲插入,等待無讀取鎖定時才寫入 | LOAD DATA LOW_PRIORITY INFILE ... |
REPLACE | 遇到唯一鍵衝突時覆蓋舊資料 | LOAD DATA ... REPLACE INTO TABLE ... |
IGNORE | 遇到唯一鍵衝突時忽略新資料 | LOAD DATA ... IGNORE INTO TABLE ... |
支援自訂欄位分隔符、包覆字元與跳脫字元:
LOAD DATA INFILE "/home/mark/Orders.txt"
REPLACE INTO TABLE Orders
FIELDS TERMINATED BY ',' ENCLOSED BY '"';
TERMINATED BY:欄位分隔符,預設 \tENCLOSED BY:欄位包覆字元(如雙引號)ESCAPED BY:跳脫字元,預設 \\當來源檔案欄位與目標表結構不一致時(如從 Access 遷移至 MySQL 新增欄位),可明確指定對應欄位:
LOAD DATA INFILE "/home/Order.txt"
INTO TABLE Orders (Order_Number, Order_Date, Customer_ID);
這是 mysqlimport 不具備的關鍵優勢,讓異構資料遷移更具彈性。
REPLACE 或大量更新前,務必備份目標表。LOAD DATA INFILE 需要 FILE 權限;LOCAL 模式需客戶端與伺服器皆啟用 local_infile=1。MySQL 批次處理不僅是「把指令存成檔案跑一次」那麼簡單。從基礎的 SQL 重導向,到功能強大的 LOAD DATA INFILE,掌握各種參數與適用場景,能讓你在資料遷移、系統上線、日誌還原等高壓情境下遊刃有餘。下次面對幾百萬筆資料要入庫時,別再一行行 INSERT 了——善用批次工具,才是專業 DBA 的基本功。