（我的）SQL：在“INSERT INTO ... SELECT”中获取新的 id

Question

我必须将从另一个表中选择的记录插入到一​​个表中，然后使用为后者生成的 id 更新前一个表。我该怎么做呢？

例如

input_table:

input_table:  code  new_id
               aa    NULL
               bb    NULL


output_table:  id(auto_increment)  code
                ...                ... 

SQL:

INSERT INTO output_table (code) SELECT code FROM input_table;

UPDATE input_table "WITH THE NEW IDS"

结果：

input_table:   code  new_id
                aa    100
                bb    101

output_table:   id   code
                ...  ...
                100  aa
                101  bb

由于多种原因，我不能依赖 output_table 中 code 的唯一性。

有什么提示吗？

Answer 1

set @max_orig_id = coalesce((select max(id) from output_table),0);
insert into output_table (code) select code from input_table;
update input_table join output_table on input_table.code=output_table.code and output_table.id > @max_orig_id set input_table.new_id=output_table.id;

Answer 2

我认为上面的答案对于多用户来说并不安全。如果你必须并发访问，我建议放弃自动增量并使用你自己的 ID 序列。不幸的是，MySQL 缺少 CREATE SEQUENCE，但您可以使用隐藏的最后值表支持的函数来模拟它。 （不要忘记在函数执行期间完全锁定表，以便多个用户获得不同的 ID。）然后从函数中获取新的 ID 号，并将其提供给 INSERT 和 UPDATE 语句。

某些方言支持 INSERT ... RETURNING 但 MySQL 不支持。