6.5.1. MySQL Sequences

CREATE TABLE cities (id      INT  NOT NULL PRIMARY KEY AUTO_INCREMENT,
                     name    VARCHAR(100),
                     pop     MEDIUMINT,
                     founded DATE)

The first time you insert a row, the id field for your first row will be 1 so long as you use NULL or for that field in the INSERT statement. For example, this command takes advantage of the AUTO_INCREMENT feature:

INSERT INTO cities (id, name, pop)
VALUES (NULL, 'Houston', 3000000)

If no other values are in that table when you issue this command, MySQL will set this field to 1, not NULL (remember, it cannot be NULL). If other values are present in the table, the value inserted will be one greater than the largest current value for id.

Another way to implement sequences is by referring to the value returned by the LAST_INSERT_ID function:

UPDATE table SET id=LAST_INSERT_ID (id+1);

6.5.2. mSQL Sequences

CREATE SEQUENCE ON table_name [VALUE start STEP incr]

The start value is the number to start with. The incr value is the amount to increment on each access. By default, a sequence starts with 1 and increments 1 at a time. For example:

CREATE SEQUENCE ON mytable VALUE 100 STEP 5

This command creates a sequence on the mytable table whose first value will be 100 and will increase by 5 each time some accesses the sequence. The second value under this scheme would therefore be 105.

In order to access a sequence, you need to select a special column called _seq from the table:

SELECT _seq FROM table_name