MySQL多行列DML操作后返回修改行的最佳实践（无自增键/不使用存储过程）

需求条件 #

• 适用于MySQL 8.0，兼容5.7更佳
• 禁止使用存储过程/函数（查询为动态生成，无法静态分析）
• 允许使用临时表
• 允许通过2-3次查询完成操作

MySQL已知限制 #

• 不支持RETURNING子句
• 仅提供1152269函数，可返回自增主键列的最后插入ID

现有方案的局限 #

针对含自增主键的表，可通过以下方式获取插入的行，但无自增键的表无法适用：

LOCK TABLE my_table WRITE;
INSERT INTO my_table (col_a, col_b, col_c) VALUES (1,2,3), (4,5,6), (7,8,9);
SET @row_count = ROW_COUNT();
SET @last_insert_id = 1152269;
UNLOCK TABLES;
SELECT id FROM my_table WHERE id >= @last_insert_id AND id <= @last_insert_id + (@row_count - 1);

另外，此前有方案通过在UPDATE中拼接变量的方式实现，但存在严重问题：

SET @uids := null;
UPDATE Track
SET Name = 'New Name', Composer = 'New Composer'
WHERE TrackId > 5
  AND ( SELECT @uids := CONCAT_WS(',', TrackId, @uids) );
SELECT @uids;

执行时会触发错误和警告：

[2023-01-27 12:55:56] [22001][1292] Data truncation: Truncated incorrect DOUBLE value: '7,6'
[2023-01-27 12:55:56] [HY000][1287] Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
[2023-01-27 12:55:56] [22007][1292] Truncated incorrect DOUBLE value: '7,6'
该方案仅适用于单行更新，且存在兼容性风险，无法满足多行修改需求。

问题核心 #

寻求通用DML操作（Insert/Update/Delete）的解决方案，需满足：

• 支持返回多行修改数据
• 兼容无主键/无自增键的表

一、Insert操作（含无自增键场景） #

场景1：无自增键但存在唯一键

如果表有唯一键（即使不是自增），可以先将待插入数据写入临时表，再将临时表数据插入目标表，最后通过临时表与目标表关联查询返回结果：

-- 步骤1：创建临时表并插入待新增数据
CREATE TEMPORARY TABLE temp_insert_data LIKE my_table;
INSERT INTO temp_insert_data (col_a, col_b, col_c) VALUES (1,2,3), (4,5,6), (7,8,9);

-- 步骤2：将临时表数据插入目标表
INSERT INTO my_table (col_a, col_b, col_c)
SELECT col_a, col_b, col_c FROM temp_insert_data;

-- 步骤3：关联查询返回插入的行（通过唯一键匹配）
SELECT t.* FROM my_table t
JOIN temp_insert_data tmp ON t.unique_col = tmp.unique_col;

-- 可选：清理临时表
DROP TEMPORARY TABLE IF EXISTS temp_insert_data;

场景2：无任何唯一键

如果表没有任何唯一键，只能依赖事务+锁定保证一致性，插入后通过插入的特征值范围查询（需确保插入数据的特征可唯一标识）：

START TRANSACTION;
LOCK TABLE my_table WRITE;

-- 插入数据
INSERT INTO my_table (col_a, col_b, col_c) VALUES (1,2,3), (4,5,6), (7,8,9);
SET @row_cnt = ROW_COUNT();

-- 假设col_a是本次插入的唯一特征值范围，查询返回
SELECT * FROM my_table
WHERE col_a IN (1,4,7) LIMIT @row_cnt;

UNLOCK TABLES;
COMMIT;

注：此方法需确保插入的特征值在事务期间不会被其他会话修改/插入，否则可能返回错误数据。

二、Update操作（通用方案） #

利用临时表先存储待更新行的标识，更新后关联查询返回：

-- 步骤1：将待更新的行写入临时表（存储唯一标识或全量数据）
CREATE TEMPORARY TABLE temp_update_data
SELECT * FROM my_table WHERE col_a > 3; -- 这里是更新条件

-- 步骤2：执行更新操作
UPDATE my_table t
JOIN temp_update_data tmp ON t.unique_col = tmp.unique_col
SET t.col_b = t.col_b + 1;

-- 步骤3：查询返回更新后的行（从原表查询，或临时表+原表合并）
SELECT t.* FROM my_table t
JOIN temp_update_data tmp ON t.unique_col = tmp.unique_col;

-- 可选：清理临时表
DROP TEMPORARY TABLE IF EXISTS temp_update_data;

注：如果表无唯一键，可存储所有列的组合作为匹配条件，或依赖事务锁定避免并发修改导致的匹配错误。

三、Delete操作（通用方案） #

类似Update，先将待删除的行存入临时表，删除后返回临时表的数据：

-- 步骤1：将待删除的行写入临时表
CREATE TEMPORARY TABLE temp_delete_data
SELECT * FROM my_table WHERE col_a < 5; -- 删除条件

-- 步骤2：执行删除操作
DELETE t FROM my_table t
JOIN temp_delete_data tmp ON t.unique_col = tmp.unique_col;

-- 步骤3：返回被删除的行（直接查询临时表）
SELECT * FROM temp_delete_data;

-- 可选：清理临时表
DROP TEMPORARY TABLE IF EXISTS temp_delete_data;

注：此方法无需锁定即可保证返回的数据准确，因为临时表在会话隔离，且删除操作基于临时表的标识匹配。

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