There is such a request:

LOAD DATA INFILE '%s' IGNORE INTO TABLE %s FIELDS TERMINATED BY ',' ENCLOSED BY \"'\" LINES TERMINATED BY '\n'

The problem is that in the downloadable file there are often values ​​that already exist in the database and stand as unique. When such a record occurs, it does not add, but the auto-increment still increases the value for inserting the next record. That is, I had a table:

 id string 1 text1 2 text2 ... 20 text20

Then I made an insert from the file, where all the records except 4, for example, were repetitions and it turned out:

 id string 20 text20 ... 500 text25

How to make the auto-increment not increase if an error occurred (such a field already exists in the database) preferably without a temporary table? Thank.

  • as an option, load into a temporary table, and then insert from that table only those records that are missing - BOPOH
  • I would like to leave it to the extreme option. - Alexander Pushkin
  • I just now noticed it is desirable without a temporary table ))) And what are you not satisfied with these id? Often load and many duplicate fields? Do you also not consider the option "before flooding to check the presence of the record"? You can search for patches, but it will be necessary to collect this from the source code - not always suitable - BOPOH
  • @BOPOH I thought it was possible to tweak the behavior of the muscle in this situation with some kind of setting. If not, then you probably have to do some checking. And they are not satisfied with the fact that there will be a lot of records, which, besides being out of order, will also go with enormous values. - Alexander Pushkin

1 answer 1

The AUTO_INCREMENT mechanism is not intended to create inseparable sequences. If it is important to assign identifiers inseparably, it is better to correct the source file so that it does not contain duplicate values. Alternatively, you can delete the resulting id column and create it in a new way (if the load operation is a one-time operation).