October 2002
Intermediate to advanced
1024 pages
27h 26m
English
Sequences start at 1, but you want to use a different starting value.
Add an AUTO_INCREMENT clause to your
CREATE TABLE statement when you
create the table. If the table has already been created, use an
ALTER TABLE statement to set
the starting value.
By default, AUTO_INCREMENT sequences start at one:
mysql>CREATE TABLE t->(id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id));mysql>INSERT INTO t (id) VALUES(NULL);mysql>INSERT INTO t (id) VALUES(NULL);mysql>INSERT INTO t (id) VALUES(NULL);mysql>SELECT id FROM t ORDER BY id;+----+ | id | +----+ | 1 | | 2 | | 3 | +----+
For MyISAM tables, you can begin the sequence at a specific initial
value n by including an
AUTO_INCREMENT =
n clause at the end of the
CREATE TABLE statement:
mysql>CREATE TABLE t->(id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id))->AUTO_INCREMENT = 100;mysql>INSERT INTO t (id) VALUES(NULL);mysql>INSERT INTO t (id) VALUES(NULL);mysql>INSERT INTO t (id) VALUES(NULL);mysql>SELECT id FROM t ORDER BY id;+-----+ | id | +-----+ | 100 | | 101 | | 102 | +-----+
Alternatively, you can create the table and then set the initial
sequence value with ALTER
TABLE:
mysql>CREATE TABLE t->(id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id));mysql>ALTER TABLE t AUTO_INCREMENT = 100;mysql>INSERT INTO t (id) VALUES(NULL);mysql>INSERT INTO t (id) VALUES(NULL);mysql>INSERT INTO t (id) VALUES(NULL);mysql>SELECT id FROM ...