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 or InnoDB 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 ...
Get MySQL Cookbook, 2nd Edition 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.