MySql-在重复键插入上

问题描述：

我知道存在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:

使用 stored procedure 对此问题的可接受答案中所述:将MySQL ON DUPLICATE KEY插入审核表或日志表中

创建 trigger 将表的每个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;

相关推荐