auto_increment question

KashKurcura

hello,

i would like to start a new auto_increment column, and I would like it to start at number 1455645 instead of 1. Is there anyway that I can do that?

Any help would be great!

LordShryku

Specify it in your create table statement.

CREATE TABLE blah (
   id int(11) NOT NULL auto_increment,
   field1 varchar(25) NOT NULL,
   field2 longtext NOT NULL,
   field3 int(8), 
   PRIMARY KEY(id)
) TYPE=MyISAM AUTO_INCREMENT=1455645;

KashKurcura

thanks! what if my table is all ready created and have over 3000 recoreds in it... is there away to alter the table that already exsists??

LordShryku

Not that I know of, but check the manual. Shouldn't be hard to recreate the table and carry over the records though.

nemonoman

Once you manually insert or update autoincrement column with a specific value, all future auto_increments start with the next number;

$result=mysql_query("INSERT INTO mytable
SET myValue=1,
myAutoIncrement=9999");

//Next insert will get myAutoIncrementvalue 10000

$result=mysql_query("INSERT INTO mytable SET myValue=1");

echo mysql_insert_id() ;

=> 10000

if you want to add some value to all previous records

UPDATE mytable SET myAutoIncrement=myAutoIncrement+9999

Next autoincrement should be 10000+3000 or however many rows.

ONE FINAL NOTE:
if all rows of myAutoIncrementvalue <1000000

DELETE FROM mytable where myAutoIncrementvalue <10000000

Will remove all rows from the table, but the leave the next autoincrement value intact

By contrast:

DELETE FROM mytable
(no qualifiers in where clause)

Will remove all rows from the table, but set next autoincrement value = 0