萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 批次處理實戰指南:從基礎操作到 LOAD DATA INFILE 進階技巧

在資料庫管理的日常工作中,批次處理是提升效率、減少人為錯誤的關鍵技能。MySQL 提供了多種批次匯入資料的方式,無論是透過命令列重導向、LOAD DATA INFILE 指令,還是結合 mysqlimport 工具,都能讓大量資料的匯入變得輕鬆自如。本文將系統性地介紹這些方法的操作步驟、參數配置與實戰場景,幫助你掌握 MySQL 批次資料操作的核心技巧。

什麼是 MySQL 批次處理?

批次處理是指將多條 SQL 指令寫入文字檔,再一次性提交給 MySQL 伺服器執行的非互動式操作模式。與在 mysql 客戶端逐行輸入指令不同,批次處理適用於:

  • 大量資料的初始化匯入
  • 從舊系統遷移資料(利用現有 SQL Log)
  • 自動化部署腳本中建立結構與填充資料
  • 減少網路往返與手動輸入錯誤

方法一:SQL 檔案重導向匯入

準備 SQL 腳本檔

建立一個純文字檔(如 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 — 命令列與應用程式的通用利器

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 ...

欄位格式自訂(FIELDS 子句)

支援自訂欄位分隔符、包覆字元與跳脫字元:

LOAD DATA INFILE "/home/mark/Orders.txt" 
REPLACE INTO TABLE Orders 
FIELDS TERMINATED BY ',' ENCLOSED BY '"';
  • TERMINATED BY:欄位分隔符,預設 \t
  • ENCLOSED BY:欄位包覆字元(如雙引號)
  • ESCAPED BY:跳脫字元,預設 \\

指定匯入欄位 — 解決結構不匹配難題

當來源檔案欄位與目標表結構不一致時(如從 Access 遷移至 MySQL 新增欄位),可明確指定對應欄位:

LOAD DATA INFILE "/home/Order.txt" 
INTO TABLE Orders (Order_Number, Order_Date, Customer_ID);

這是 mysqlimport 不具備的關鍵優勢,讓異構資料遷移更具彈性。

實戰建議與避坑指南

  1. 先驗證檔案編碼:確保文字檔編碼與資料庫字元集一致(如 UTF-8),避免中文亂碼。
  2. 大量資料分批提交:單次匯入超過百萬列時,建議拆分檔案或搭配事務控制,減少鎖定衝突。
  3. 備份優先:執行 REPLACE 或大量更新前,務必備份目標表。
  4. 權限檢查:執行 LOAD DATA INFILE 需要 FILE 權限;LOCAL 模式需客戶端與伺服器皆啟用 local_infile=1。

總結

MySQL 批次處理不僅是「把指令存成檔案跑一次」那麼簡單。從基礎的 SQL 重導向,到功能強大的 LOAD DATA INFILE,掌握各種參數與適用場景,能讓你在資料遷移、系統上線、日誌還原等高壓情境下遊刃有餘。下次面對幾百萬筆資料要入庫時,別再一行行 INSERT 了——善用批次工具,才是專業 DBA 的基本功。

copyright © 萬盛學電腦網 all rights reserved