11.12 Starting a Sequence at a Particular Value
11.12.1 Problem
Sequences start at 1, but you want to
use a different starting value.
11.12.2 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.
11.12.3 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 t ORDER BY id;
+-----+
| id |
+-----+
| 100 |
| 101 |
| 102 |
+-----+
To start a sequence at n for table types
other than MyISAM, you must use a hack: insert a
"fake" record with sequence value
n-1, then delete it
after inserting one or more "real"
records. The following example illustrates how to start a sequence at
100 for an InnoDB table:
mysql> CREATE TABLE t
-> (id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id))
-> TYPE = InnoDB;
mysql> INSERT INTO t (id) VALUES(99);
mysql> INSERT INTO t (id) VALUES(NULL);
mysql> INSERT INTO t (id) VALUES(NULL);
mysql> INSERT INTO t (id) VALUES(NULL);
mysql> DELETE FROM t WHERE id = 99;
mysql> SELECT * FROM t ORDER BY id;
+-----+
| id |
+-----+
| 100 |
| 101 |
| 102 |
+-----+
Remember that if you clear out the contents of a table with a
DELETE statement that has no
WHERE clause, the sequence may be reset to begin
with 1, even for types that normally do not reuse sequence values.
(See Recipe 11.5.) In this case, you should
reinitialize the sequence value explicitly after clearing the table
if you don't want it to begin with 1.
|
