MySQL 游標 與 LOAD DATA 實現文本資料高效導入與轉換

場景背景

需將 CSV 格式文本檔 (含日期字串 2/8/2013 9:00:00 PM) 導入 MySQL，並將日期字串轉為 DATETIME 型別儲存。對比兩種方案：儲存過程 + 游標逐行處理 vs LOAD DATA 內建轉換。

方案一：儲存過程 + 游標 (適用於複雜邏輯、需行級控制)

核心步驟

1. 聲明變數：DECLARE v_str VARCHAR(150); DECLARE v_dt DATETIME; DECLARE done INT DEFAULT FALSE;
2. 聲明游標：DECLARE cur CURSOR FOR SELECT get_time FROM raw_table;
3. 聲明結束處理器：DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
4. 打開游標：OPEN cur;
5. 循環 FETCH：FETCH cur INTO v_str; → 判斷 done → 處理 → 寫入目標表。
6. 關閉游標：CLOSE cur;

完整儲存過程範例

DELIMITER //
CREATE PROCEDURE sp_str_to_datetime()
BEGIN
  DECLARE v_str VARCHAR(150);
  DECLARE v_dt DATETIME;
  DECLARE done INT DEFAULT FALSE;
  
  DECLARE cur CURSOR FOR SELECT get_time FROM raw_data;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  OPEN cur;
  
  read_loop: LOOP
    FETCH cur INTO v_str;
    IF done THEN LEAVE read_loop; END IF;
    
    -- 字串轉日期：格式 'M/D/YYYY h:mm:ss A'
    SET v_dt = STR_TO_DATE(v_str, '%c/%e/%Y %l:%i:%s %p');
    
    INSERT INTO clean_data (event_time) VALUES (v_dt);
  END LOOP;
  
  CLOSE cur;
  
  SELECT * FROM clean_data LIMIT 10;
END //
DELIMITER ;

CALL sp_str_to_datetime();

STR_TO_DATE 格式符號對照

方案二：LOAD DATA INFILE 內建轉換 (高效、推薦大數據量)

MySQL LOAD DATA 支援 SET col = expr 子句，可在導入過程中直接轉換欄位值，無需中間表、無需游標循環，速度快數量級。

完整範例

-- 1. 建立目標表
CREATE TABLE traindata (
  id INT AUTO_INCREMENT PRIMARY KEY,
  station_id VARCHAR(10),
  get_time DATETIME,
  PM25 INT, PM10 INT, NO2 INT,
  temperature INT, pressure INT,
  humidity INT, wind DOUBLE, weather INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 導入並轉換
LOAD DATA LOCAL INFILE 'f:\\dataset\\beijing\\crawleddata.txt'
INTO TABLE traindata
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 LINES  -- 若有標題行
(station_id, @var_time, PM25, PM10, NO2, temperature, pressure, humidity, wind, weather)
SET get_time = STR_TO_DATE(@var_time, '%c/%e/%Y %l:%i:%s %p');

-- 3. 驗證
SELECT * FROM traindata LIMIT 10;

關鍵語法解析

• @var_time：用戶變數暫存原始 CSV 欄位值。
• SET get_time = STR_TO_DATE(@var_time, ...)：導入時即時轉換，直接寫入目標欄位。
• FIELDS TERMINATED BY ','：CSV 分隔符。
• LINES TERMINATED BY '\n'：換行符 (Windows 檔案可能為 \r\n)。
• IGNORE 1 LINES：跳過標題行。
• LOCAL：從客戶端讀取檔案 (需客戶端與 Server 皆啟用 local_infile=1)。

效能對比

選型建議

• 資料清洗/轉換邏輯複雜 (需調用外部 API、多表關聯、條件分支) → 儲存過程 + 游標 或應用層程式 (Python/Java) 處理後再導入。
• 純格式轉換、大量資料初始化、定期 ETL** → LOAD DATA INFILE + SET 子句，效能最優、維護最簡。
• 生產環境導入** 必須先在測試環境驗證資料量、時間、錯誤處理 (如 REPLACE/IGNORE 處理重複鍵)。

1. 上一篇文章： MySQL Error 1101: BLOB/TEXT 欄位不能有預設值解決方案
2. 下一篇文章： MySQL LOAD DATA INFILE 高效導入資料完整語法與實戰指南

MySQL 綜合教學

MySQL 啟動報錯：Can't create PID file - No space left on device 磁盤滿解決實錄

MySQL 啟動報錯：Cant create PID fil

MyBatis 配置文件編寫與進階用法完整指南

前言 MyBatis 是一款優秀的半自動化 ORM 框架，支

MySQL 數據庫操作全攻略：創建、查詢、修改、刪除與命名規範

MySQL 數據庫操作全攻略：創建、查詢、修改、刪除與命名規

MySQL 客戶端查詢中文亂碼解決：SET NAMES GBK/UTF8

MySQL 客戶端查詢中文亂碼解決：SET NAMES GB

MySQL 索引使用原則深度解析：前綴索引、組合索引最左前綴、OR 條件、ORDER BY 與單鍵/組合選擇策略 MySQL 依指定欄位順序排序：ORDER BY FIELD 實戰技巧 MySQL 免安裝版 (ZIP/壓縮包) 配置與部署完整教程 Shell 腳本檢測 MySQL 主從複制狀態並告警 Linux MySQL 1130 錯誤解決：Host not allowed to connect (權限設定圖解) MySQL「Column in WHERE clause is ambiguous」錯誤原因與解決方案 MySQL 徹底卸載指南：避免殘留文件導致再次安裝失敗

欄目導航

MySQL 入門

SQL 基礎

MySQL 資料庫相關知識

MariaDB 資料庫

MySQL 綜合教學

Copyright © 程式碼之家 All Rights Reserved

指標	游標循環	LOAD DATA
100 萬行導入耗時	~30-60 分鐘	~10-30 秒
記憶體佔用	低 (逐行)	中 (緩衝區)
邏輯靈活度	高 (可行級複雜判斷)	中 (僅支援表達式)
事務控制	可精細控制	整個導入為一事務 (或關閉自動提交分批)