即使methai的答案是正确的,如果您手动运行查询,当2个并发事务/连接实际上在生产中的运行时(例如)执行此查询时,会出现问题。
只需在MySQL工作台中手动尝试,同时打开2个连接:
CREATE TABLE translation (
id BIGINT PRIMARY KEY AUTO_INCREMENT
);
# Suppose we have already 20 entries, we execute 2 new inserts:
交易1:
21 = SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName' AND TABLE_NAME = 'translation';
insert into translation (id) values (21);
交易2:
21 = SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName' AND TABLE_NAME = 'translation';
insert into translation (id) values (21);
# commit transaction 1;
# commit transaction 2;
插入事务1是可以的:事务2的插入出错:错误代码:1062。对于'PRIMARY'键重复输入'21'。
一个好的解决方案是jvdub的答案,因为每个事务/连接2个插入将是:
交易1:
insert into translation (id) values (null);
21 = SELECT LAST_INSERT_ID();
交易2:
insert into translation (id) values (null);
22 = SELECT LAST_INSERT_ID();
# commit transaction 1;
# commit transaction 2;
但是我们必须在插入之后执行last_insert_id()!我们可以重用该id来插入其他需要外键的表中!
此外,我们无法执行以下2个查询:
insert into translation (id) values ((SELECT AUTO_INCREMENT FROM
information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE()
AND TABLE_NAME='translation'));
因为我们实际上有兴趣在其他表中获取/重用该ID或返回!