I think what you want is to have an integer column without auto_inctament set on it (unique is optional) when you go to insert a new record, you can either set it to a number manually, or use max(id)+1 to get the next from highest number.
Gordon Stewart wrote: > http://www.liewcf.com/blog/archives/2004/04/mysql-reset-auto-increament-number/ > > Hi, > > i'm just looking at the MYSQL_Auto increments > > and have found what I want - But 2 queries > > Background > > I want (& have) an auto increment field. > > Sometimes, I want to start from "1" and find the next number / record > - that is available (this is normal..) > > but - what if I want to "temporariliy" start from record 10,000 ?? > > IE, > > I have records 1 to 45 - all OK... > > but I want to insert one at 10,000.. (I know i can Force a record > number to be used)... > > Then the next few times, will insert records 46, 47, 48 etc... > > But then - i want another added at 10,000 - as it is already used - > The next auto-increment will be 10,001 > > Looking at here :- > > http://www.liewcf.com/blog/archives/2004/04/mysql-reset-auto-increament-number/ > > I see 2 commands > > ALTER TABLE tablename AUTO_INCREMENT = 1 > > > (I can temporarily set the table auto increment to 10,000 & insert > my record...) > > However - As that site says, you may have myltiple MYSQL queries > (different people) running at the same time - So this will not work > (the other person will have their record added in the 10,000 range > instead of the 50-60 range (next number in sequence) > > > the SET insert_id = 4; command Looks good (I can set it to 10000... ) > > QUESTION :- > > If the set command works, & I put > > SET insert_id = 10000; > > In the next INSERT - will it force the insert command to use record 10,000 > > Or does it act like an auto-increment number, & insert 10,001 ??? > > (I think thats how it is, But I cant locate good docs to confirm this )
