MySql - 重复键插入

2022-01-09 00:00:00 sql insert mysql on-duplicate-key

我了解存在 INSERT IGNORE 和 INSERT ... ON DUPLICATE KEY UPDATE.但是，当存在重复键时，我想对临时表执行 INSERT 以记录已违反的唯一键，以便将其输出给用户.

I understand that there exists INSERT IGNORE and INSERT ... ON DUPLICATE KEY UPDATE. However, when there is a duplicate key, I'd like to do a INSERT to a temporary table to keep a record of the unique key that has been violated, so that I can output it to the user.

有什么方法可以让我进行ON DUPLICATE INSERT?如果有帮助，我正在尝试进行批量插入.

Is there any way I can do a ON DUPLICATE INSERT? If it helps, I'm trying to do a bulk insert.

推荐答案

使用 ON DUPLICATE KEY UPDATE，您无法插入另一个表 - 也没有可用的替代函数.

With ON DUPLICATE KEY UPDATE, you cannot insert into another table - nor is there an alternative function available.

您可以通过两种自定义/替代方式来完成此操作:

Two custom/alternative ways you can accomplish this:

  1. 使用 存储过程 如该问题的公认答案中所述:MySQL ON DUPLICATE KEY 插入审计或日志表

创建一个触发器 将您的表的每个 INSERT 记录到另一个表中，并在充满日志"的表中查询任何重复项.

Creating a trigger that logged every INSERT for your table into another table and querying the table full of "logs" for any duplicates.

类似的东西应该可以工作:

Something similar to this should work:

CREATE TABLE insert_logs (
    id int not null
);

delimiter |
CREATE TRIGGER insert_logs_trigger BEFORE INSERT ON your_table
    FOR EACH ROW BEGIN
        INSERT INTO insert_logs SET id = NEW.id;
    END;
|

要获取表中重复项的列表，您可以:

To get a list of the duplicates in the table, you could us:

SELECT id FROM insert_logs HAVING COUNT(id) > 1 GROUP BY id;

相关文章