INSERT IGNORE因auto_increment失败

Question

My goal is to only execute a SQL Insert if the same value not exists.

this is my table1:

id(auto_increment)
version(string)

My Query looks like this:

INSERT IGNORE INTO table1 (version) VALUES ('12345');

The problem is that the ignore statement not work because every row is different because of the id column (auto_increment).

Anybody here have a Solution to avoid double saving the same values?

Solution:

INSERT INTO table1 (version)
SELECT * FROM (SELECT '12345') AS tmp
WHERE NOT EXISTS (
    SELECT version FROM table1 WHERE version = '12345'
) LIMIT 1;

Answer 1

Your issue has nothing to do with the auto-incremented column.

You need a unique constraint/index on version:

alter table table1 constraint unq_table1_version unique (version);

I also don't recommend insert ignore for this purpose. It might ignore other errors. Instead, use on duplicate key update:

insert into table1 (version) 
    values ('12345')
    on duplicate key update version = values(version);  -- this is a no-op