问题描述
我有一个表，比如 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.
关于唯一索引的有用链接
这篇关于“插入到 .. 重复密钥更新"只插入新条目而不是替换?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！