MySQL:在插入过程中重复使用自动增量 [英] MySQL: re-use auto-increment during insert

问题描述

我正在设计一个注释MySQL数据库，并且我的注释表包含以下字段:

I'm designing a comments MySQL db, and my comments table has fields:

• id主键，自动递增
• thread int，不为空
• content

• id primary key, auto-incr
• thread int, not null
• content

除该根注释外，所有对同一注释的回复必须共享相同的thread.当用户回复评论时，这很简单，但是发布新的根评论时该怎么办?我想我会为根注释设置thread=id.

All replies to the same comment, in addition to that root comment must share the same thread. This is simple when a user is replying to a comment but what about when a new root comment is posted? I figured I would set thread=id for root comments.

问题是，我不知道如何编写一个查询，该查询将在填充thread时在同一查询中重用刚刚创建的id值.这有可能吗?

Problem is, I don't know how to write a query that will reuse the just created id value within the same query when filling thread. Is this even possible?

我尝试过

INSERT INTO `comments`
VALUES (NULL, LAST_INSERT_ID(), 'hi there')

这给了我上一个插入的ID，而不是当前的ID.我必须使用2个查询吗?

This gives me the id from the previous insert, not the current one. Do I have to use 2 queries?

推荐答案

我必须使用2个查询吗?

Do I have to use 2 queries?

是的.如您所知，尚未在INSERT触发器中生成ID值.但是您不能在AFTER INSERT触发器中更改NEW.thread值.

Yes. As you discovered, the id value hasn't been generated yet in a BEFORE INSERT trigger. But you can't change your NEW.thread value in an AFTER INSERT trigger.

您不能依赖阅读INFORMATION_SCHEMA，因为这可能会导致竞争状况.

You can't rely on reading the INFORMATION_SCHEMA, because you can cause a race condition.

您只需要执行INSERT，然后立即执行:

You'll just have to do the INSERT, and then immediately execute:

UPDATE comments SET thread=id WHERE id=LAST_INSERT_ID() AND thread IS NULL;

如果是根注释.

另请参阅我过去关于类似主题的答案:

See also my past answers on the similar topic:

• 串联字符串和主键ID插入时
• 两个自动增量列或自动递增，并在其他列中使用相同的值

• Concatenating a string and primary key Id while inserting
• Two autoincrements columns or autoincrement and same value in other column

这篇关于MySQL:在插入过程中重复使用自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！