如何在MySQL中获取多个插入行的ID ?

I am inserting some words into a two-column table with this command:

我使用以下命令将一些单词插入一个两列的表中:

INSERT IGNORE INTO terms (term) VALUES ('word1'), ('word2'), ('word3');

How can I get the ID (Primary Key) of the row in which each word is inserted. I mean returning a value like "55,56,57" after executing INSERT. Does MySQL have such a response?

如何获得插入每个单词的行的ID(主键)。我的意思是在执行插入之后返回一个值，比如“55,56,57”。MySQL有这样的响应吗?
The term column is UNIQUE. If a term already exists, MySQL will not insert it. Is it possible to return the reference for this duplication (i.e. the ID of the row in which the term exists)? A response like "55,12,56".

列是唯一的。如果一个术语已经存在，MySQL将不会插入它。是否有可能返回此副本的引用(即术语存在的行的ID)?这样的反应“56 55岁,12日”。

3 个解决方案

#1

You get it via SELECT LAST_INSERT_ID(); or via having your framework/MySQL library (in whatever language) call mysql_insert_id().

通过选择LAST_INSERT_ID()获得;或者通过让框架/MySQL库(用任何语言)调用mysql_insert_id()。
That won't work. There you have to query the IDs after inserting.

是行不通的。在这里，您必须在插入后查询id。

#2

Why not just:

为什么不直接:

SELECT ID
  FROM terms
 WHERE term IN ('word1', 'word2', 'word3')

#3

First, to get the id just inserted, you can make something like :

首先，要获得刚刚插入的id，您可以创建如下内容:

SELECT LAST_INSERT_ID() ;

Care, this will work only after your last INSERT query and it will return the first ID only if you have a multiple insert!

注意，这将只在您的最后一个插入查询之后工作，并且只有当您有多个插入时，它才会返回第一个ID !

Then, with the IGNORE option, I don't think that it is possible to get the lines that were not inserted. When you make an INSERT IGNORE, you just tell MySQL to ignore the lines that would have to create a duplicate entry.

然后，使用IGNORE选项，我认为不可能得到没有插入的行。在执行插入忽略时，只需告诉MySQL忽略必须创建重复条目的行。

If you don't put this option, the INSERT will be stopped and you will have the line concerned by the duplication.

如果您不放置此选项，插入将被停止，并且您将得到与复制相关的行。

#1

You get it via SELECT LAST_INSERT_ID(); or via having your framework/MySQL library (in whatever language) call mysql_insert_id().

通过选择LAST_INSERT_ID()获得;或者通过让框架/MySQL库(用任何语言)调用mysql_insert_id()。
That won't work. There you have to query the IDs after inserting.

是行不通的。在这里，您必须在插入后查询id。

#2