AUTO_INCREMENT QUESTION

[dadms]

I have started a new database and have added a few records to make sure the work and have deleted others. Is there a way to make the auto_increment start from number 8? 7 is the last record that I would like to keep.

 

[sleipnir214]

If you insert 10 records with auto_increment fields (the list value being 10), and delete 5, MySQL will generate 11 as the number for the next insert. When you delete records, auto_increment numbers are not reused.



Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[cdlvj]

From the manual

ALTER TABLE tbl_name AUTO_INCREMENT = 100
will start your records at 100

ALTER TABLE tbl_name AUTO_INCREMENT = 1000
will start your records at 1000

etc...

 

[cdlvj]

In addition on reuse, this is from the manual

AUTO_INCREMENT values will be reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.)

 

[dadms]

thanx that did it

 

[tshot]

Hi, dadms
i have never tested it extensively only is development version. my results were as per th manual states i.e per connection basis. i am quoting below the exceprt from on-line manual

http://www.mysql.com/doc/en/Getting_unique_ID.html

The most recently generated ID is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0). 

 If you want to use the ID that was generated for one table and insert it into a second table, you can use SQL statements like this: 

INSERT INTO foo (auto,text)
    VALUES(NULL,'text');              # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
    VALUES(LAST_INSERT_ID(),'text');  # use ID in second table

would pls post ur experience so thtra we can share it and let mysql know about it




----------------
ur feedback is a very welcome desire