背景
在開發企業級應用時，我們經常需要處理批量數據的插入和更新操作。傳統的逐條處理方式性能低下，而簡單的REPLACE INTO或INSERT ... ON DUPLICATE KEY UPDATE在某些場景下又不夠靈活。本文將介紹一種基于臨時表的高效批量插入/更新方案，解決復雜業務場景下的數據同步問題。
場景
這個表需要大量插入和更新數據，頻繁的比對浪費時間，且效率不高，要減少數據庫連接時間開銷。可以采用臨時表的方式進行插入更新。假設下表，根據username和age組合判斷記錄是否存在，存在則更新，不存在則插入。
整體設計邏輯
創建mapper接口
/*** 批量插入或更新(根據username和age判斷)* @param list 批量數據列表* @return 影響的行數*/int batchInsertOrUpdateByUsernameAndAge(List<BatchTest> list);創建mapper.xml
<insert id="batchInsertOrUpdateByUsernameAndAge" parameterType="java.util.List"><!-- 創建臨時表存儲批量數據 -->CREATE TEMPORARY TABLE temp_batch_test (username varchar(50),age int,email varchar(100),status tinyint) ENGINE=MEMORY;<!-- 插入數據到臨時表 -->INSERT INTO temp_batch_test (username, age, email, status)VALUES<foreach collection="list" item="item" separator=",">(#{item.username}, #{item.age}, #{item.email}, #{item.status})</foreach>;<!-- 更新已存在的記錄(匹配username和age) -->UPDATE batch_test bJOIN temp_batch_test t ON b.username = t.username AND b.age = t.ageSETb.email = t.email,b.status = t.status;<!-- 插入新記錄(不存在的username和age組合) -->INSERT INTO batch_test (username, age, email, status)SELECT t.username, t.age, t.email, t.statusFROM temp_batch_test tLEFT JOIN batch_test b ON t.username = b.username AND t.age = b.ageWHERE b.username IS NULL;<!-- 刪除臨時表 -->DROP TEMPORARY TABLE IF EXISTS temp_batch_test;</insert>更新和插入的邏輯
案例數據流程
初始數據（batch_test表）
| id | username | age | status | |
|---|---|---|---|---|
| 1 | 張三 | 25 | zhangsan@old.com | 1 |
| 2 | 李四 | 30 | lisi@old.com | 1 |
批量輸入數據（temp_batch_test表）
| username | age | status | |
|---|---|---|---|
| 張三 | 25 | zhangsan@new.com | 0 |
| 李四 | 35 | lisi@new.com | 1 |
| 王五 | 28 | wangwu@new.com | 1 |
操作結果
-
更新操作：
-
匹配記錄：張三(25歲)
-
執行：
UPDATE ... SET email='zhangsan@new.com', status=0
-
-
插入操作：
-
新記錄：李四(35歲)、王五(28歲)
-
執行：
INSERT INTO ... VALUES ('李四',35,...), ('王五',28,...)
-
最終數據
| id | username | age | status | ||
|---|---|---|---|---|---|
| 1 | 張三 | 25 | zhangsan@new.com | 0 | ← 更新 |
| 2 | 李四 | 30 | lisi@old.com | 1 | |
| 3 | 李四 | 35 | lisi@new.com | 1 | ← 新增 |
| 4 | 王五 | 28 | wangwu@new.com | 1 | ← 新增 |
實現邏輯詳解
核心邏輯步驟
-
臨時表創建階段
CREATE TEMPORARY TABLE temp_batch_test (username varchar(50),age int,email varchar(100),status tinyint ) ENGINE=MEMORY;-
使用MEMORY引擎提高臨時表操作速度
-
只包含必要字段，減少內存占用
-
-
數據加載階段
INSERT INTO temp_batch_test VALUES ('張三',25,'zhangsan@new.com',0), ('李四',35,'lisi@new.com',1), ('王五',28,'wangwu@new.com',1);-
使用MyBatis的foreach實現動態批插
-
參數化查詢防止SQL注入
-
-
更新階段
UPDATE batch_test b JOIN temp_batch_test t ON b.username = t.username AND b.age = t.age SET b.email = t.email, b.status = t.status; -
插入階段（重點）
這是插入操作的核心技術，通過?LEFT JOIN + IS NULL?實現：
FROM temp_batch_test t
LEFT JOIN batch_test b ON t.username = b.username AND t.age = b.age
WHERE b.username IS NULL執行過程：
-
左連接：將臨時表(t)與主表(b)按username和age進行連接
-
過濾：只保留主表中不存在的記錄（即b.username為NULL的記錄）
內存中的連接結果示例：
| t.username | t.age | t.email | t.status | b.username | b.age | b.email | |
|---|---|---|---|---|---|---|---|
| 張三 | 25 | zhangsan@new.com | 0 | 張三 | 25 | ... | 主表存在 |
| 李四 | 35 | lisi@new.com | 1 | NULL | NULL | NULL | 主表不存在 |
| 王五 | 28 | wangwu@new.com | 1 | NULL | NULL | NULL | ?主表不存在 |
WHERE條件過濾后結果：
| t.username | t.age | t.email | t.status |
|---|---|---|---|
| 李四 | 35 | lisi@new.com | 1 |
| 王五 | 28 | wangwu@new.com | 1 |
執行批量插入
將過濾后的結果插入主表：
INSERT INTO batch_test (username, age, email, status) -- 上一步的查詢結果
執行效果等價于：
INSERT INTO batch_test (username, age, email, status) VALUES
('李四', 35, 'lisi@new.com', 1),
('王五', 28, 'wangwu@new.com', 1);
關鍵技術點解析
-
反連接(Anti-Join)模式：
-
通過LEFT JOIN + IS NULL實現"不存在于"的邏輯
-
比NOT IN或NOT EXISTS性能更好，特別是大數據量時
-
-
復合條件判斷：
ON t.username = b.username AND t.age = b.age
-
同時匹配username和age字段
-
只有當兩個字段都相等時才認為是重復記錄
-
-
NULL安全比較：
如果age可能為NULL，應該使用：ON t.username = b.username AND (t.age = b.age OR (t.age IS NULL AND b.age IS NULL))
-
批量插入優勢：
-
單次SQL執行所有插入操作
-
比循環執行單條INSERT效率高10-100倍
-
減少網絡往返和SQL解析開銷
-
? ?清理階段
- 顯式釋放臨時表資源
- 避免連接池復用時的表沖突
-
DROP TEMPORARY TABLE temp_batch_test;
實際執行案例
初始主表數據
| id | username | age | status | 備注 | |
|---|---|---|---|---|---|
| 1 | 張三 | 25 | zhangsan@old.com | 1 | |
| 2 | 李四 | 30 | lisi@old.com | 1 |
批量處理數據
| username | age | status | 操作說明 | |
|---|---|---|---|---|
| 張三 | 25 | zhangsan@new.com | 0 | 更新操作 |
| 李四 | 35 | lisi@new.com | 1 | 插入操作 |
| 王五 | 28 | wangwu@new.com | 1 | 插入操作 |
插入操作執行過程
-
臨時表與主表LEFT JOIN中間結果：
臨時表數據 主表匹配結果 張三(25) 匹配id=1的記錄 李四(35) 無匹配(NULL) 王五(28) 無匹配(NULL) -
過濾后待插入數據：
username age email status 李四 35 lisi@new.com 1 王五 28 wangwu@new.com 1 -
最終主表數據：
id username age email status 操作說明 1 張三 25 zhangsan@new.com 0 被更新 2 李四 30 lisi@old.com 1 3 李四 35 lisi@new.com 1 新插入 4 王五 28 wangwu@new.com 1 新插入
性能優化建議
-
索引優化：
ALTER TABLE batch_test ADD INDEX `idx_username_age` (`username`, `age`);
-
批量大小控制：
-
建議每批500-1000條記錄
-
過大的批次可能導致內存問題
-
-
臨時表優化：
CREATE TEMPORARY TABLE ... (INDEX `idx_temp` (`username`, `age`) ) ENGINE=MEMORY;
-
服務器參數：
# my.cnf配置 tmp_table_size = 256M max_heap_table_size = 256M
這種插入機制通過巧妙的SQL設計，實現了高效、準確的批量數據插入，是處理數據同步場景的理想解決方案。
必要配置
properties文件
# application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/dbname?allowMultiQueries=true
spring.datasource.hikari.connection-init-sql=SET SESSION sql_mode='NO_ENGINE_SUBSTITUTION'
測試數據：一定要自己動手試試
第一批數據（全部插入，status=1） - 20條
[{"username": "仇癸霖2", "age": 22, "email": "2244442", "status": 1},{"username": "靳浩然", "age": 33, "email": "hvbk3d38@vip.qq.com", "status": 1},{"username": "束雪", "age": 7, "email": "ssxtbf_ios@qq.com", "status": 1},{"username": "公孫雨", "age": 28, "email": "rain_gs@163.com", "status": 1},{"username": "歐陽明日", "age": 45, "email": "oymr@hotmail.com", "status": 1},{"username": "司馬青", "age": 19, "email": "smqing@126.com", "status": 1},{"username": "令狐沖", "age": 32, "email": "linghuchong@gmail.com", "status": 1},{"username": "東方不敗", "age": 40, "email": "dfbb@yeah.net", "status": 1},{"username": "西門吹雪", "age": 35, "email": "xmcx@sina.com", "status": 1},{"username": "慕容復", "age": 38, "email": "murongfu@qq.com", "status": 1},{"username": "趙靈兒", "age": 18, "email": "zle@163.com", "status": 1},{"username": "李逍遙", "age": 25, "email": "lxy@gmail.com", "status": 1},{"username": "林月如", "age": 22, "email": "lyr@126.com", "status": 1},{"username": "景天", "age": 30, "email": "jtian@qq.com", "status": 1},{"username": "唐雪見", "age": 27, "email": "txj@sina.com", "status": 1},{"username": "龍葵", "age": 20, "email": "lkui@163.com", "status": 1},{"username": "紫萱", "age": 300, "email": "zxuan@yeah.net", "status": 1},{"username": "徐長卿", "age": 35, "email": "xczq@hotmail.com", "status": 1},{"username": "重樓", "age": 500, "email": "chonglou@gmail.com", "status": 1},{"username": "花楹", "age": 15, "email": "huaying@qq.com", "status": 1}
]第二批數據（混合更新和插入，更新status=0/新插入status=1） - 30條
[// 需要更新的記錄（username+age與第一批重復）{"username": "仇癸霖2", "age": 22, "email": "new_2244442", "status": 0},{"username": "靳浩然", "age": 33, "email": "new_hvbk3d38@vip.qq.com", "status": 0},{"username": "束雪", "age": 7, "email": "new_ssxtbf_ios@qq.com", "status": 0},{"username": "公孫雨", "age": 28, "email": "new_rain_gs@163.com", "status": 0},{"username": "歐陽明日", "age": 45, "email": "new_oymr@hotmail.com", "status": 0},// 新插入的記錄{"username": "張無忌", "age": 28, "email": "zwj@mingjiao.org", "status": 1},{"username": "趙敏", "age": 25, "email": "zhaomin@yuandynasty.com", "status": 1},{"username": "周芷若", "age": 24, "email": "zzr@emei.org", "status": 1},{"username": "小昭", "age": 20, "email": "xiaozao@persia.com", "status": 1},{"username": "殷離", "age": 22, "email": "yinli@butterfly.com", "status": 1},{"username": "楊逍", "age": 40, "email": "yangxiao@mingjiao.org", "status": 1},{"username": "范遙", "age": 38, "email": "fanyao@mingjiao.org", "status": 1},{"username": "黛綺絲", "age": 42, "email": "daiqisi@persia.com", "status": 1},{"username": "謝遜", "age": 50, "email": "xiexun@lionking.com", "status": 1},{"username": "殷天正", "age": 60, "email": "yintianzheng@tiandihui.com", "status": 1},{"username": "韋一笑", "age": 45, "email": "weiyixiao@batman.com", "status": 1},{"username": "說不得", "age": 48, "email": "shuobude@monk.com", "status": 1},{"username": "冷謙", "age": 52, "email": "lengqian@cool.com", "status": 1},{"username": "彭瑩玉", "age": 55, "email": "pengyingyu@pearl.com", "status": 1},{"username": "周顛", "age": 50, "email": "zhoudian@crazy.com", "status": 1},{"username": "鐵冠道人", "age": 58, "email": "tieguandaoren@taoist.com", "status": 1},{"username": "朱元璋", "age": 35, "email": "zhuyuanzhang@emperor.com", "status": 1},{"username": "常遇春", "age": 38, "email": "changyuchun@general.com", "status": 1},{"username": "徐達", "age": 40, "email": "xuda@marshal.com", "status": 1},{"username": "湯和", "age": 42, "email": "tanghe@general.com", "status": 1},{"username": "鄧愈", "age": 37, "email": "dengyu@general.com", "status": 1},{"username": "沐英", "age": 30, "email": "muying@general.com", "status": 1},{"username": "藍玉", "age": 45, "email": "lanyu@general.com", "status": 1},{"username": "傅友德", "age": 50, "email": "fuyoude@general.com", "status": 1}
]
