Mysql Auto_icrement

[borrob]

Ok this is not really a php question but maybe somebody can help me.

i have a mysql table with the field id that's an auto_increment column.

but now i want the auto_increment to start at 'somenumber' and not at 1
anybody know the correct syntax for this when creating the table?
only for this table so no global setting
:cons:

[bwh2]

i always forget the syntax, but it's called a seed.

[ramie]

Create the table... (example)


CREATE TABLE `test` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`title` VARCHAR( 255 ) NOT NULL ,
`created_at` DATETIME NOT NULL
) ENGINE = MYISAM;
and then modify it.



ALTER TABLE test AUTO_INCREMENT = 100;
(and some test code if you like)



INSERT INTO `test` ( id` , `title` , `created_at` )
VALUES (
NULL , 'test title', '2007-02-22 18:08:28'
), (
NULL , 'another test title', '2007-02-22 18:08:39'
);


SELECT id FROM test;
should return 100, 101

[bwh2]

yeah, it sucks but i don't think there's a way to define the seed when you CREATE TABLE.

[kdd]

Why don't you download mysql's control center? instead of writing all this code, you can put 1 for auto increment. it has a gui, that's why...

[borrob]

Thanks very much a was breaking my head about this....

Time is here to consume.

[foodpk]

I saw this thread and I thought I'd chime in. There is a way to set the AUTO_INCREMENT start when creating a table without using ALTER. Do it like so:


CREATE TABLE something (
....
fields here, bla bla
PRIMARY KEY( bla,blarg)
....
) TYPE=MyISAM AUTO_INCREMENT=1001;

[evildrummer]

Why don't you download mysql's control center? instead of writing all this code, you can put 1 for auto increment. it has a gui, that's why...
Because it makes people feel less manly! :angry:

[bwh2]

^^ excellent. i should have known because i've definitely exported CREATE TABLE statements before which included the AUTO_INCREMENT.

[borrob]

Yes it worked. Thanks very much. This information is unbelievably hard to find. thanks foodpk... this is exactly what i needed.