“插入到 .. 重复密钥更新"只插入新条目而不是替换?

2022-01-09 00:00:00 duplicates insert mysql sql-server

我有一个表，比如 table1，它有 3 列:id、number 和 name.id 是 auto_incremented.

I have a table, say table1, which has 3 columns: id, number, and name. id is auto_incremented.

我想实现一个向表中插入条目的sql语句，但是如果该行已经存在，则忽略它.

I want to achieve an sql statement which inserts entries into a table, but if the row already exists, then ignore it.

然而，

每次我跑步:

INSERT INTO table1( number, name) 
VALUES(num, name) 
ON DUPLICATE KEY 
UPDATE number = VALUES(number), 
       name = VALUES(name)

它似乎忽略了具有匹配数字和名称值的行，并且无论如何都会将条目附加到表的末尾.

It seems to ignore rows with matching number and name values and appends entries to the end of the table no matter what.

我能做些什么来防止这种情况发生吗?我觉得这与拥有 auto_incrementing 主键有关吗?谢谢

Is there anything I can do to prevent this? I have a feeling it has something to do with having the auto_incrementing primary key? thanks

推荐答案

创建一个关于数字和名称的唯一复合索引.

Create a unique composite index on number and name.

Alter table table1 Add unique index idx_unq(`number`,`name`);

然后执行 Insert Ignore INTO table1(number,name) VALUES(num, name);

这应该可以防止重复插入到表中.

That should prevent duplicate from being inserted into the table.

关于唯一索引的有用链接

相关文章