Starting a Sequence at a Particular Value
Problem
Sequences start at 1, but you want to use a different starting value.
Solution
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.
Discussion
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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.