 |
|||
Auto Increment fields - MYSQL
By: Sunil | 22 Sep 2008 2:29 pm
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 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 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 - 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 However - As that site says, you may have myltiple MYSQL queries 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 ) Comments
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 ?? This sounds like bad design, and it sounds as though you are storing problems up for yourself in the future. Depending on your logic, EITHER add a field to the table, and have it set to High sequence or Low Sequence, (I would use 1 and 2) OR create another table for the high sequence. You can always join the tables if you need a query that shows both. 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 ??? 10,000 The *next* insert after that will give you 10,001 I don't like SET insert_id, because you don't specify a table, and if the code is ever changed, you have to !remember! to keep SET insert_id with the INSERT that you want.
By: Sunil | 22 Sep 2008
The purpose of an auto incrementing field is to provide a quick general purpose primary key field (if indexed) AND/OR an audit trail to track records deletions, usually in financial database management. It is a bad programming practice to tamper with auto incrementation. It is advisable to use some other source to calculate and determine the value for the type of information one wishes to store in a column.
By: Shailesh Singh | 22 Sep 2008
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.
By: Sunil | 22 Sep 2008
|
