如何在单个查询一对多关系中使用 LAST_INSERT_ID？

发布于 2024-11-28 08:20:34 字数 558 浏览 0 评论 0原文

我有两个表格，问题和答案。 answers 包含键 *question_id*

当我创建问题时，我将一条记录插入到 questions 表中，并将多条记录插入到 answers 表中。是否可以使用 LAST_INSERT_ID() 或类似方法在一个查询中完成所有这些操作？

即类似的东西

INSERT INTO questions VALUES(NULL, 'My question');

INSERT INTO answers VALUES(NULL, LAST_INSERT_ID(), 'Answer 1');
INSERT INTO answers VALUES(NULL, LAST_INSERT_ID(), 'Answer 2');
INSERT INTO answers VALUES(NULL, LAST_INSERT_ID(), 'Answer 3');

显然，这个查询不会工作，因为 LAST_INSERT_ID() 在每个语句后都会改变。无论如何，有没有办法让这项工作正常进行，或者我必须将其分成两个查询？

原文

I have two tables, questions and answers.
answers contains a key *question_id*

When I create a question, I insert one record to the questions table and several records into the answers table. Is it possible to use LAST_INSERT_ID() or similar to do all this in one query?

I.e. something like

INSERT INTO questions VALUES(NULL, 'My question');

INSERT INTO answers VALUES(NULL, LAST_INSERT_ID(), 'Answer 1');
INSERT INTO answers VALUES(NULL, LAST_INSERT_ID(), 'Answer 2');
INSERT INTO answers VALUES(NULL, LAST_INSERT_ID(), 'Answer 3');

Obviously, this query wouldn't work since LAST_INSERT_ID() changes after every statement. Is there anyway to make this work or will I have to break this up into two queries?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寄居者 2024-12-05 08:20:34

您可以像这样使用批量插入：

INSERT INTO answers VALUES
          (NULL, LAST_INSERT_ID(), 'Answer 1') , 
          (NULL, LAST_INSERT_ID(), 'Answer 2') , 
          (NULL, LAST_INSERT_ID(), 'Answer 3');

You can use bulk inserts like this :

INSERT INTO answers VALUES
          (NULL, LAST_INSERT_ID(), 'Answer 1') , 
          (NULL, LAST_INSERT_ID(), 'Answer 2') , 
          (NULL, LAST_INSERT_ID(), 'Answer 3');

回复收藏 0 原文

油饼 2024-12-05 08:20:34

尝试将 de 值存储到变量中，

INSERT INTO questions VALUES(NULL, 'My question');
SET @id = (SELECT LAST_INSERT_ID());
INSERT INTO answers VALUES(NULL, @id, 'Answer 1');
INSERT INTO answers VALUES(NULL, @id, 'Answer 2');
INSERT INTO answers VALUES(NULL, @id, 'Answer 3');

我认为还有另一种方法可以做到这一点，但也许它会给您一个想法。

Try to store de value into a variable

INSERT INTO questions VALUES(NULL, 'My question');
SET @id = (SELECT LAST_INSERT_ID());
INSERT INTO answers VALUES(NULL, @id, 'Answer 1');
INSERT INTO answers VALUES(NULL, @id, 'Answer 2');
INSERT INTO answers VALUES(NULL, @id, 'Answer 3');

I think there is another way to do it but maybe it will give you an idea.

回复收藏 0 原文

~没有更多了~