MySQL INSERT IGNORE not working

Question

Here is my table with some sample data

a_id | b_id ------------ 1 225 2 494 3 589 

When I run this request

 INSERT IGNORE INTO table_name (a_id, b_id) VALUES ('4', '230') ('2', '494') 

He inserts both of these lines when he should ignore the second pair of values ​​(2, 494)

There are no pointers, none of these columns is primary.

What I do not know?

Answer 1

From docs :

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are considered warnings. For example, without IGNORE, a row that duplicates an existing UNIQUE or PRIMARY KEY index in a table causes an error with a duplicate key, and the statement is aborted. With IGNORE, the row is still not inserted, but no error was thrown.

(italics mine).

Your row does not duplicate the “existing UNIQUE index or PRIMARY KEY value”, since you do not have a primary key or any unique restrictions.

---

If, as you noted in one of your comments, you want no field to be unique, but you want the combination to be unique, you need a composite primary key for both columns (get rid of any duplicates first):

 alter table MYTABLE add primary key (a_id,b_id) 

Answer 2

Unless you set UNIQUE criteria or set PRIMARY KEY , MySql will not know that your new record is a duplicate.

Answer 3

if there is no primary key, the duplicate key cannot be ignored. you should always set the primary key, so do it, and if you want to have additional decks that should not be duplicated, set them as “unique”.

Answer 4

If you understand correctly, after running the insert command, your table looks like this:

 1 225 2 494 3 589 4 230 2 494 

If so, then the answer is because your table design allows duplication.

If you want the second record not to be inserted, you need to define the a_id column as the primary key or a unique index. If you do this, the insert ignore statement will work as you expect, for example, to insert records, to ignore errors such as trying to add a duplicate record.