How to add records to the table, only if there is no such record yet (without taking into account the auto-increment column when comparing).

You can like this:

INSERT IGNORE INTO `tab` SET `tovar` = "vedro", `money` = 100;

but then the auto-increment increases with each check, despite the fact that the record was not added, how to avoid it?

What is the fastest way to check?

  • if insert ignore does not suit you, then with a single select query you check the existence of a record, if not, then insert - madfan41k
  • one
    auto increment increases with each check, despite the fact that the record was not added, how to avoid it? The question is not "how", but "why." But since the question in this form has arisen, it means that you have imposed one more sense on the autoincrement, besides the unique index of the record. This is bad! If continuous numbering is needed - for this, one more field should be created additionally, and already in it do the required one. And do not even look at autoincrement. - Akina
  • @Akina I thank for the valuable advice - I will always try not to impose on the autoincrement of other roles, except for the unique index of the record. - Hertz

1 answer 1

Try this:

 INSERT INTO `tab` (tovar, money) SELECT 'vedro', '100' WHERE NOT EXISTS (SELECT 1 FROM `tab` WHERE (tovar, money) IN ('vedro', '100'));

PS Do you care for autoincrement? Well, let yourself increase, you will still forget his figs.

  • [offtop] And all the same there will be "holes" in the auto increment. The pool of generated values ​​is not going anywhere ... - Akina
  • In the DLE engine, the number of autoincrement is a unique part of the link (site.ru/id- name of news.html). The smaller the link, the better in terms of website promotion. Usually anyway. - Hertz