Setting auto increment lower than the current highest value?

Question

I have a MySQL table with an auto-increment ID field. I'm filling the table with data from one source, with its own unique ID (starting from around 4 million up). I also want to insert my own data but don't want it to conflict with the IDs already in the database.

Ideally I'd just insert it all at a lower ID. However, I have to use a function outside of my control (a built-in Joomla function) which will insert a new row if the ID is 0, but try and update it if it's any other number.

So obviously if I try and insert a new row with and ID of 1, it will try to update the row with ID of 1, which doesn't exist. So no data gets inserted.

I've tried setting the auto-increment value lower than the current max id but it doesn't work. Is there another solution to my problem?

UPDATE: To try and clarify my question, I have data from another source with a high ID (from 4 million and increasing). I want to insert rows with a low ID, from 1 and up (the external source will not conflict with this). However, for the reasons described above, I can't just find an unused low ID and use that, I have to either use zero or find some other solution.

Answer 1

Are you trying to preserve the IDs from the other database? If so, how about a bulk insert with mysqldump?

Otherwise, why not simply let the auto increment do the job?

Answer 2

Are you trying to get the last id inserted? If so, you can put this into your logic:

SELECT LAST_INSERT_ID() as last_id

And use that to get the last ID inserted and put that in your new record.

Answer 3

If the function you're referring to is JTable::store(), you could create a class extending JTable, then write the store() function to behave the way you want.

Answer 4

Sounds like you need to do it programmatically and bypass the fact that the table has a auto-increment identity. I would programmatically start counting from row #1 until you find a blank row and then manually specify that as the row-id instead of relying on the auto-increment.

Answer 5

I found a solution: store the external IDs in a different field!

The IDs that are coming from the external source are now stored in an "extid" field, so I can keep a check on that field (for my own updating). I made the main ID field an auto-incrementing primary key, which I can use to refer to the articles in various places.