如何用最少MySQL语句实现Excel数据与表的增改删同步？

针对4万行级别的Excel数据同步需求，最高效的方案是利用临时表批量处理，既能减少SQL执行次数，又能一次性完成增改和删除操作，避免内存溢出或脚本超时。以下是具体实现步骤：

1. 创建临时表 #

临时表结构与目标表保持一致（仅保留需要同步的字段即可），用于暂存Excel数据：

CREATE TEMPORARY TABLE temp_myTable (
    id INT PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255)
) ENGINE=InnoDB; -- 若服务器内存充足，可改用MEMORY引擎进一步提升速度

2. 批量导入Excel数据到临时表 #

借助Spout读取Excel时，不要一次性加载所有数据到内存，而是分批次（比如每1000行一批）执行批量插入，减少SQL执行次数：

-- 示例：批量插入2行，实际可扩展到1000行
INSERT INTO temp_myTable (id, first_name, last_name) VALUES 
(23334, 'Mark', 'Spencer'),
(342217, 'Gerald', 'Butler');

注意：使用预处理语句绑定参数可以避免SQL注入，同时提升插入效率。

3. 一次性完成增改操作（UPSERT） #

通过INSERT ... SELECT ... ON DUPLICATE KEY UPDATE语句，将临时表的数据同步到目标表，自动插入新记录、更新已有记录：

INSERT INTO myTable (id, first_name, last_name)
SELECT id, first_name, last_name FROM temp_myTable
ON DUPLICATE KEY UPDATE
    first_name = VALUES(first_name),
    last_name = VALUES(last_name);

前提：目标表的id字段必须是主键或唯一索引，否则ON DUPLICATE KEY逻辑不生效。

4. 删除数据库中不存在于Excel的记录 #

使用LEFT JOIN替代NOT IN（性能更优，且避免NULL值导致的逻辑问题），一次性删除目标表中不在临时表的记录：

DELETE t FROM myTable t
LEFT JOIN temp_myTable tmp ON t.id = tmp.id
WHERE tmp.id IS NULL;

5. 清理临时表（可选） #

临时表会在数据库会话结束后自动销毁，也可以手动删除：

DROP TEMPORARY TABLE IF EXISTS temp_myTable;

关键优化点 #

• 批量操作：分批次插入临时表，避免一次性加载4万行数据到内存，同时减少SQL执行次数（从4万次降到40次左右）。
• 事务包裹：将上述步骤2-4放入一个事务中，保证数据一致性，避免同步过程中出现部分成功的情况：

  START TRANSACTION;
  -- 批量插入临时表语句
  -- UPSERT语句
  -- 删除语句
  COMMIT;
  

• 索引优化：临时表的id设为主键，目标表的id设为唯一索引，提升JOIN和UPSERT的执行速度。

内容的提问来源于stack exchange，提问作者Torf