switch out XXX for the new value you wish, if the PK used that slot, I believe it'll reset to 1 past the max you have.
As for inserting on an "empty" spot, I wouldn't bother.. Just make your PK field large enough to hold a lot of records, or don't use an auto_increment.
When it comes to modifying the behaviour of AUTO_INCREMENT the first thing we need to know is why you need to (sometimes there is a better solution if things are done differently).
every site that is added to the table should have a number starting from 1.
There should be a special site in the system which is marked by the special ID 0.
I would like to add it to the table, but the auto_increment causes it to start with the value 1
switch out XXX for the new value you wish, if the PK used that slot, I believe it'll reset to 1 past the max you have.
As for inserting on an "empty" spot, I wouldn't bother.. Just make your PK field large enough to hold a lot of records, or don't use an auto_increment.
Even if you set auto_increment to -1, it sets to 0, and when its at zero, the next item you add is placed in row 1.
jasongr wrote:every site that is added to the table should have a number starting from 1.
There should be a special site in the system which is marked by the special ID 0.
I would like to add it to the table, but the auto_increment causes it to start with the value 1
The closest I've come to working around this issue is:
- Clear the db (sets the autoinc to 0)
- Do the first row insert (automatically placed at #1)
- Update the first row to 0 (this much DOES work)
- Then add rows like normal.
Its a little obnoxious, but it does work. I haven't found a way to get the db to let me do an autoinsert that STARTS at 0. If someone else has a different solution, I'm extremely interested in hearing it!!!
Sorry bout that, kept finding new info and testing new solutions.
Also found this out..
mysql manual wrote:
As of MySQL 4.1.1, specifying the NO_AUTO_VALUE_ON_ZERO flag for the --sql-mode server option or the sql_mode system variable allows you to store 0 in AUTO_INCREMENT columns as 0 without generating a new sequence value.
I ended up keeping the AUTO_INCREMENT in the table definition and simply modified the requirements.
The special site will have the reserved ID 1
All the other sites will start from ID 2 and will continue on from there
0 value might be good for a default value. I create 1 entry and change it to 0. The next entry I add with ID set to 1 and for the next the auto-increment is in order.