在重复键更新（非唯一字段）

I'm working on a CSV import script in PHP attached to a MySQL database. I want to give the users the option to either insert new rows if duplicates are found or update them.

我正在使用附加到MySQL数据库的PHP中的CSV导入脚本。我想让用户可以选择在找到重复项时插入新行或更新它们。

I can't use ON DUPLICATE KEY UPDATE because the column cannot be unique, otherwise it won't cater for when they want to have duplicates.

我不能使用ON DUPLICATE KEY UPDATE，因为该列不能是唯一的，否则它将无法满足他们想要重复的时间。

How would I go about this?

我怎么会这样呢？

3 个解决方案

#1

Use 2 queries - a select query to determine if the row exists, then based on that result either an insert or update query.

使用2个查询 - 一个选择查询来确定该行是否存在，然后根据该结果进行插入或更新查询。

#2

I would script it for use on the command line. In your loop, check if an entry with the same key already exists. If it does, prompt the user to decide what to do using fgets(STDIN).

我会编写脚本以便在命令行中使用。在循环中，检查是否已存在具有相同键的条目。如果是，则提示用户决定使用fgets（STDIN）做什么。

#3

why not just add a UNIQUE key to the table, possibly spanning multiple fields? here is an example given the following simple phonebook table:

为什么不只是在表中添加一个UNIQUE键，可能跨越多个字段？这是一个给出以下简单电话簿表的示例：

CREATE TABLE phonebook (
    firstname VARCHAR(255),
    lastname VARCHAR(255),
    home VARCHAR(50),
    mobile VARCHAR(50)
)

you can easily add a key making firstname AND lastname together unique.

您可以轻松添加一个密钥，使名字和姓氏一起独特。

ALTER TABLE phonebook ADD UNIQUE (firstname, lastname)

that way you can use the ON DUPLICATE KEY UPDATE statement to easily update the phone numbers when the name is already on the list:

这样，当名称已经在列表中时，您可以使用ON DUPLICATE KEY UPDATE语句轻松更新电话号码：

INSERT INTO phonebook (firstname, lastname, home, mobile) 
VALUES ("john", "doe", "12345", "45678")
ON DUPLICATE KEY UPDATE home = "12345", mobile = "45678"

everything else is getting unnecessarily complex.

其他一切都变得越来越复杂。

#1