How to set a value of an auto_increment field?

Anon

Hello,

Does anyone know how can I set a starting value for a MySQL auto_increment field?

I mean instead of that fiedl to start counting from "1" I want it to start counting from "10000".

IS this posible..... how???

Anon

Straight from the \"MySQL\" book by Paul DuBois:

\"...Supose you create a table with an AUTO_INCREMENT column, but you want a sequence to begin at 1000 rather than at 1. You can achieve this one of two ways. First, you can insert the first record with an explicit sequence value of 1000, then insert subsequent records by inserting NULL into the AUTO_INCREMENT column. Second, you can insert a fake record with a value of 999 in the AUTO_INCREMENT column. The first real record you insert after that will get a sequence number of 1000, after which you can delete the fake record.\"

Seems like there should be an easier way to do this, I\'m not sure though. Good luck.

Anon

Thanks Dennis!,

It worked!, I can't believe it was that easy!.

Thanks!

Anon

Straight from the mysql manual:

-- quote --
table_options:
...
or AUTO_INCREMENT = #
--end quote --

So,if you want an auto_increment field to start at a specific value, you can enter that value when you create the table, like this:

CREATE TABLE mytable (ID mediumint not null primary key auto_increment,Name varchar(128) not null) auto_increment=100;

Now the ID field will start at value 100;

Anon

Excellent post, I was not aware of this. I did a little research on it and saw that it's only available in version 3.23 or above. I guess it's time for me to upgrade 😉

Anon

That's nasty because the manual did not mention it was only for 3.23.
Sorry folks!

Anon

Guess this is a bit dumb question, wonder
if could help. I could not really figure
out how to use LAST_INSERT_ID().

I do this.

mysql> create table IDCounter (
-> ID int not null,
auto_increment,
-> index IDIdx(ID));
mysql> update IDCounter set
ID=LAST_INSERT_ID(ID+1);

In a program like java/perl, I do

update IDCounter set
ID=LAST_INSERT_ID(ID+1);

followed by
select LAST_INSERT_ID();

Am I doing it right. A small code sample
would help me a lot.

I am not a regular watcher of this. I would
appreciate a reply cced to me.

regards

Anon

mysql_insert_id() returns(!) the value
that was inserted into the of the auto_increment
column of your table during the latest insert operation
done through the connection that you are requesting the mysql_insert_id() for.

This means that if you run many copies of your script at the same time, each copy will return the insert_id of the record that was last inserted using it's own connection.
The scripts will not mess eachother up.

So, if I insert a record into a table where there is an auto_increment column, the new record will get a new unique value. This value is returned when you call mysql_insert_id().

You should never ever have to set this value, it is created automatically by the database engine.

If you want to insert a record and select that new record immediately afterwards;

mysql_query("insert bla",$db_link_identifier);
$insert_id=mysql_insert_id($db_link_identifier);
mysql_query("select bla where ID=$insert_id",$db_link_identifier);