“INSERT INTO .. ON DUPLICATE KEY UPDATE” Only inse

问题:

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

回答1:

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.

Useful link on Unique indexes