MySQL 批量插入、去重、導入導出十大實用腳本合集

MySQL 批量插入、去重、導入導出十大實用腳本合集

整理十個高頻實戰場景，涵蓋 Shell/Python 批量寫入、存儲過程迴圈、INSERT ... SELECT、ON DUPLICATE KEY UPDATE、多種去重姿勢、mysqldump/mysqlimport 導入導出，可直接拿來改造生產腳本。

1. Shell 腳本批量插入 (百萬級秒入)

#!/bin/bash
# batch_insert.sh
DB="test"; TBL="logs"; SQL_FILE="/tmp/insert_${TBL}.sql"
> $SQL_FILE
for i in {1..1000000}; do
  echo "INSERT INTO ${TBL} (col1,col2) VALUES (${i}, 'val_${i}');" >> $SQL_FILE
  # 每 5000 行執行一次，避免單事務過大
  if (( i % 5000 == 0 )); then
    mysql -uroot -p${PWD} ${DB} < $SQL_FILE
    > $SQL_FILE
  fi
done
# 餘數
[ -s $SQL_FILE ] && mysql -uroot -p${PWD} ${DB} < $SQL_FILE

要點：分批提交、關閉自動提交 (SET autocommit=0)、關閉唯一檢查 (SET unique_checks=0) 可再提速 3-5 倍。

2. 存儲過程迴圈插入測試數據

DELIMITER $$
CREATE PROCEDURE batch_insert(IN rows INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  START TRANSACTION;
  WHILE i <= rows DO
    INSERT INTO test_tbl (name, val) VALUES (CONCAT('row_', i), i);
    IF i % 1000 = 0 THEN COMMIT; START TRANSACTION; END IF;
    SET i = i + 1;
  END WHILE;
  COMMIT;
END$$
DELIMITER ;
CALL batch_insert(1000000);

3. INSERT ... SELECT 大表複製/分表

-- 全量複製結構+數據
CREATE TABLE new_tbl LIKE old_tbl;
INSERT INTO new_tbl SELECT * FROM old_tbl;

-- 條件分表 (按月)
INSERT INTO orders_202401 SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';

4. 存在更新、不存在插入 (UPSERT)

INSERT INTO users (id, name, email, updated_at)
VALUES (101, 'Alice', 'alice@example.com', NOW())
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  email = VALUES(email),
  updated_at = VALUES(updated_at);

需表有主鍵或唯一索引；VALUES(col) 參考插入值，避免重複寫字面量。

5. 刪除重複記錄 (保留 ID 最小)

-- 方式一：自連接刪除
DELETE t1 FROM tbl t1
JOIN tbl t2 ON t1.dup_col = t2.dup_col AND t1.id > t2.id;

-- 方式二：ROW_NUMBER() 窗口函數 (MySQL 8.0)
WITH cte AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY dup_col ORDER BY id) AS rn
  FROM tbl
)
DELETE FROM tbl WHERE id IN (SELECT id FROM cte WHERE rn > 1);

-- 方式三：建唯一索引忽略報錯 (需停寫)
ALTER IGNORE TABLE tbl ADD UNIQUE KEY uk_dup (dup_col); -- 5.7 已移除 IGNORE，需手動去重

6. 多值單語句插入 (比迴圈快 10 倍+)

INSERT INTO tbl (a,b,c) VALUES
(1,'x',100),(2,'y',200),(3,'z',300),...,(1000,'last',99999);

單語句不超過 max_allowed_packet (預設 4MB)，大批量分塊發送。

7. 批量更新 (CASE WHEN / ON DUPLICATE KEY)

-- CASE WHEN 單語句更新多行不同值
UPDATE tbl SET status = CASE id
  WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' ELSE status END
WHERE id IN (1,2,3);

-- 配合臨時表大批量更新
CREATE TEMPORARY TABLE tmp_upd (id INT PRIMARY KEY, new_val VARCHAR(20));
-- 批量載入 tmp_upd
UPDATE tbl t JOIN tmp_upd u ON t.id = u.id SET t.col = u.new_val;

8. mysqldump 導出 / 導入

# 導出單庫 (含建庫、單事務、主從同步點)
mysqldump -uroot -p --databases db1 --single-transaction --master-data=2 > db1_$(date +%F).sql

# 導出指定表
mysqldump -uroot -p db1 tbl1 tbl2 > db1_tbls.sql

# 導入
mysql -uroot -p < db1_2024-01-01.sql
# 或 mysql 客戶端內
SOURCE /path/db1_2024-01-01.sql;

9. SELECT ... INTO OUTFILE / LOAD DATA INFILE (極速文本互轉)

-- 導出 CSV (Server 端需 FILE 權限)
SELECT * FROM tbl
INTO OUTFILE '/tmp/tbl.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\"'
LINES TERMINATED BY '\r\n';

-- 導入 (LOCAL 表客戶端讀檔)
LOAD DATA LOCAL INFILE '/tmp/tbl.csv'
INTO TABLE tbl
FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

10. mysqlimport 命令列導入 (自動映射檔名→表名)

mysqlimport -uroot -p --fields-terminated-by=',' --ignore-lines=1 --local db_name /path/*.csv

適用於同結構 CSV 批量入庫，無需寫 SQL。

避坑提醒

• 批量操作務必 分批提交，單事務過大導致 Undo Log 暴增、鎖升級、主從延遲。
• ON DUPLICATE KEY UPDATE 會導致 AUTO_INCREMENT 增長跳躍 (InnoDB 預分鎖模式)，業務敏感場景慎用。
• 去重前 備份，驗證保留邏輯正確後再執行刪除。
• 導出導入全鏈路指定 --default-character-set=utf8mb4，防止字符集亂碼。