问题描述
我了解存在 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:
使用
存储过程如该问题的公认答案中所述: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;
这篇关于MySql - 重复键插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！