“INSERT INTO .. ON DUPLICATE KEY UPDATE”只插入新条目而不是替换? [英] "INSERT INTO .. ON DUPLICATE KEY UPDATE" Only inserts new entries rather than replace?
问题描述
id是auto_incremented。
我想实现一个将条目插入到表中的sql语句,但是如果行已经存在,则忽略它。 p>
然而,
每次运行时:
INSERT INTO table1(number,name)
VALUES(num,name)
ON DUPLICATE KEY
UPDATE number = VALUES(number),
name = VALUES(name)
似乎忽略了具有匹配数字和名称值的行,并附加条目无论什么,表的结尾。
有什么可以做的,以防止这种情况吗?我有一个感觉,它与auto_incrementing主键有关系?
谢谢
在数字和名称上创建一个唯一的复合索引。
更改表table1添加唯一索引idx_unq(`number`,`name`);
然后执行 Insert Ignore INTO table1(number,name)VALUES(num ,name);
这应该可以防止重复插入表中。
I have a table, say table1, which has 3 columns: id, number, and name. id is auto_incremented.
I want to achieve an sql statement which inserts entries into a table, but if the row already exists, then ignore it.
However,
Every time I run:
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.
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`);
Then do an Insert Ignore INTO table1(number,name) VALUES(num, name);
That should prevent duplicate from being inserted into the table.
这篇关于“INSERT INTO .. ON DUPLICATE KEY UPDATE”只插入新条目而不是替换?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!