如何使用一个insert语句插入多个项目，同时检测唯一列是否具有重复值？

Please mark it as duplicate if the same question exists already.

如果已存在相同的问题,请将其标记为重复。

I am using MySQL to do the insertion, and I am using PDO prepared statements. I now want to insert an array of rows into a database. A similar question can be found here Multiple Values Insert with PDO Prepared Statements. However, the author didn't consider the case when duplicates occur on unique column(s). For example, consider the following table:

我正在使用MySQL进行插入,我正在使用PDO预处理语句。我现在想要将一个行数组插入到数据库中。可以在此处找到类似的问题。使用PDO准备语句插入多个值。但是,作者在唯一列上发生重复时没有考虑这种情况。例如,请考虑下表:

id    name    counter

where name has unique property and whenever an existing name is trying to be inserted the counter increases by 1. Now I want to insert an array with three names as follows:

其中name具有唯一属性,并且每当尝试插入现有名称时,计数器增加1.现在我想插入一个具有三个名称的数组,如下所示:

$name_array = array('User A','User B','User C');

Suppose my table currently has User A only and its corresponding counter is 1. In that case, I would like the table after insertion be as follows:

假设我的表目前只有用户A,其对应的计数器为1.在这种情况下,我希望插入后的表格如下:

id    name      counter
1     User A    2
2     User B    1
3     User C    1

Is it possible for me to do it using one insert statement? If yes, then how?

我可以使用一个插入语句来完成它吗?如果是,那怎么样?

1 个解决方案

#1

First of all: IMHO this has nothing to do with PHP, PDO or prepared statements - it is about converting an INSERT into an UPDATE for special cases.

首先:恕我直言,这与PHP,PDO或预处理语句无关 - 它是关于将INSERT转换为特殊情况的UPDATE。

That being so, I would solve this by using the INSERT ON DUPLICATE KEY UPDATE construct, somewhere along the lines of

既然如此,我会通过使用INSERT ON DUPLICATE KEY UPDATE结构来解决这个问题。

INSERT INTO tablename(name)
VALUES ('User A'), ('User B'), ('User C')
ON DUPLICATE KEY UPDATE counter=counter+1;

How you send this query to the MySQL server (be it PHP/PDO or whatever) should be of no importance.

如何将此查询发送到MySQL服务器(无论是PHP / PDO还是其他)都不重要。

#1