Mysql Auto_increment

mysql> CREATE TABLE animals (
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO animals (name) VALUES
-> ('dog'),('cat'),('penguin'),
-> ('lax'),('whale'),('ostrich');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from animals;
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)

If the column is declared NOT NULL, it is also possible to assign NULL to the column to generate
sequence numbers. For example:

mysql> INSERT INTO animals (id,name) VALUES(NULL,'squirrel');
Query OK, 1 row affected (0.00 sec)

mysql> select * from animals;
+----+----------+
| id | name |
+----+----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | squirrel |
+----+----------+
7 rows in set (0.00 sec)

When you insert any other value into an AUTO_INCREMENT column, the column is set to that value
and the sequence is reset so that the next automatically generated value follows sequentially from the
largest column value. For example:
INSERT INTO animals (id,name) VALUES(100,'rabbit');
INSERT INTO animals (id,name) VALUES(NULL,'mouse');

mysql> INSERT INTO animals (id,name) VALUES(100,'rabbit');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO animals (id,name) VALUES(NULL,'mouse');
Query OK, 1 row affected (0.00 sec)

mysql> select * from animals;
+-----+----------+
| id | name |
+-----+----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | squirrel |
| 100 | rabbit |
| 101 | mouse |
+-----+----------+
9 rows in set (0.00 sec)

Updating an existing AUTO_INCREMENT column value in an InnoDB table does not reset the
AUTO_INCREMENT sequence as it does for MyISAM and NDB tables.

mysql> update animals set id=200 where id=101;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> insert into animals(name) values('pig');
Query OK, 1 row affected (0.00 sec)

mysql> select * from animals;
+-----+----------+
| id | name |
+-----+----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | squirrel |
| 100 | rabbit |
| 102 | pig |
| 200 | mouse |
+-----+----------+
10 rows in set (0.00 sec)

You can retrieve the most recent automatically generated AUTO_INCREMENT value with the
LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function.

For a multiple-row insert, LAST_INSERT_ID() and mysql_insert_id()
actually return the AUTO_INCREMENT key from the first of the inserted rows.
This enables multiple-row inserts to be reproduced correctly on other servers in
a replication setup.

mysql> select * from animals;
+-----+----------+
| id | name |
+-----+----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | squirrel |
| 100 | rabbit |
| 102 | pig |
| 200 | mouse |
+-----+----------+
10 rows in set (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 102 |
+------------------+
1 row in set (0.00 sec)

mysql> insert into animals(name) values('horse'),('sheep'),('goat');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from animals;
+-----+----------+
| id | name |
+-----+----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | squirrel |
| 100 | rabbit |
| 102 | pig |
| 103 | horse |
| 104 | sheep |
| 105 | goat |
| 200 | mouse |
+-----+----------+
13 rows in set (0.00 sec)

mysql> select last_insert_id() from animals;
+------------------+
| last_insert_id() |
+------------------+
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
+------------------+
13 rows in set (0.00 sec)

mysql> insert into animals values(110,'donkey'),(111,'bull'),(112,'pony');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from animals;
+-----+----------+
| id | name |
+-----+----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | squirrel |
| 100 | rabbit |
| 102 | pig |
| 103 | horse |
| 104 | sheep |
| 105 | goat |
| 110 | donkey |
| 111 | bull |
| 112 | pony |
| 200 | mouse |
+-----+----------+
16 rows in set (0.00 sec)

mysql> select last_insert_id() from animals;
+------------------+
| last_insert_id() |
+------------------+
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
| 103 |
+------------------+
16 rows in set (0.00 sec)

mysql> insert into animals(name) values('ass');
Query OK, 1 row affected (0.00 sec)

mysql> select * from animals;
+-----+----------+
| id | name |
+-----+----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | squirrel |
| 100 | rabbit |
| 102 | pig |
| 103 | horse |
| 104 | sheep |
| 105 | goat |
| 110 | donkey |
| 111 | bull |
| 112 | pony |
| 113 | ass |
| 200 | mouse |
+-----+----------+
17 rows in set (0.00 sec)

mysql> select last_insert_id() from animals;
+------------------+
| last_insert_id() |
+------------------+
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
+------------------+
17 rows in set (0.00 sec)

mysql> delete from animals where id in (112,113);
Query OK, 2 rows affected (0.01 sec)

mysql> select * from animals;
+-----+----------+
| id | name |
+-----+----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | squirrel |
| 100 | rabbit |
| 102 | pig |
| 103 | horse |
| 104 | sheep |
| 105 | goat |
| 110 | donkey |
| 111 | bull |
| 200 | mouse |
+-----+----------+
15 rows in set (0.00 sec)

mysql> select last_insert_id() from animals;
+------------------+
| last_insert_id() |
+------------------+
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
| 113 |
+------------------+
15 rows in set (0.00 sec)

mysql> insert into animals(name) values ('zebra');
Query OK, 1 row affected (0.00 sec)

mysql> select * from animals;
+-----+----------+
| id | name |
+-----+----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | squirrel |
| 100 | rabbit |
| 102 | pig |
| 103 | horse |
| 104 | sheep |
| 105 | goat |
| 110 | donkey |
| 111 | bull |
| 114 | zebra |
| 200 | mouse |
+-----+----------+
16 rows in set (0.00 sec)

mysql> select last_insert_id() from animals;
+------------------+
| last_insert_id() |
+------------------+
| 114 |
| 114 |
| 114 |
| 114 |
| 114 |
| 114 |
| 114 |
| 114 |
| 114 |
| 114 |
| 114 |
| 114 |
| 114 |
| 114 |
| 114 |
| 114 |
+------------------+
16 rows in set (0.00 sec)

To start with an AUTO_INCREMENT value other than 1, set that value with CREATE TABLE or ALTER
TABLE, like this:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiplecolumn
index. In this case, the generated value for the AUTO_INCREMENT column is calculated as
MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you
want to put data into ordered groups.

CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
) ENGINE=MyISAM;
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
248

Using MySQL with Apache
SELECT * FROM animals ORDER BY grp,id;
Which returns:
+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+

In this case (when the AUTO_INCREMENT column is part of a multiple-column index),
AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value
in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally
are not reused.

• If the AUTO_INCREMENT column is part of multiple indexes, MySQL generates sequence values
using the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the
animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would
ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a
single sequence, not a sequence per grp value.