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);