Making exceptions to auto increment?

sleepyw

Weird issue just came up. I'm creating an interface for data that will be imported into MySQL. One of the fields to be imported is a number field (like an id field). It's set to auto increment so when a new record is created, it uses the next number available as the id.

However, I just found out that there are some records being imported that had been given some unusually high numbers for that field. So when these records are all imported, it's going to auto increment on top of those HIGH numbers, not continue from the normal numbers.

For example, let's say I import the data and there are records ranging from 1-1000. So I want new records to start at 1001, etc. However, there are a few records given id's in the 9000's. I want those ignored when auto-assigning a number number.

Is this possible to write in PHP?

etully

The only solution that I can see is essentially to write your own autoincrement code. Something like this:

To insert any single record:

LOCK tables
start a counter $i at 950
select record with id = $i
If record exists, increment counter and repeat
else insert record with id = $i
UNLOCK tables

This is slow, inefficient code. It will be fine if you are only using this import tool a few hundred times a day. If you are using it a few hundred thousands of times a day... or hundreds of times a minute, then you are going to need to consider a more robust solution like:

a. fix those high numbered records and reset your autoincrement number back to 1001

or possibly:

b. run an experiment where you don't fix the high numbered records and JUST reset your autoincrement number back to 1001. This might be sufficient, I'm not sure.

sleepyw

Thanks for the input. This would only be needed a few times a day at most. I'm trying to convince others to 'fix' the high numbered items and avoid this altogether.

NogDog

Is it necessary to retain the ID numbers from the imported source to the target DB? If not, just leave the ID column out of the insert statement and let the DB assign the next auto-increment value. Or, if you need those number for reference, make that a separate column in the table, so you still assign an auto-incremented value when you import a record and keep the external ID in its own column.