(mysql.info) example-auto-increment
Info Catalog
(mysql.info) calculating-days
(mysql.info) examples
3.6.9 Using `AUTO_INCREMENT'
----------------------------
The `AUTO_INCREMENT' attribute can be used to generate a unique
identity for new rows:
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
Which returns:
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
You can retrieve the most recent `AUTO_INCREMENT' value with the
`LAST_INSERT_ID()' SQL function or the `mysql_insert_id()' C API
function. These functions are connection-specific, so their return
values are not affected by another connection which is also performing
inserts.
* 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 allows multiple-row inserts to be
reproduced correctly on other servers in a replication setup.
For `MyISAM' and `BDB' tables you can specify `AUTO_INCREMENT' on a
secondary column in a multiple-column 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)
);
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
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 |
+--------+----+---------+
Note that 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 will
generate 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.
To start with an `AUTO_INCREMENT' value other than 1, you can set that
value with `CREATE TABLE' or `ALTER TABLE', like this:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
More information about `AUTO_INCREMENT' is available here:
* How to assign the `AUTO_INCREMENT' attribute to a column:
create-table, and alter-table.
* How `AUTO_INCREMENT' behaves depending on the SQL mode:
server-sql-mode.
* Find the row that contains the most recent AUTO_INCREMENT value:
comparison-operators.
* Set the `AUTO_INCREMENT' value to be used: set-option.
* `AUTO_INCREMENT' and replication: replication-features.
* Server-system variables related to `AUTO_INCREMENT'
(`auto_increment_increment' and `auto_increment_offset') that can
be used for replication: server-system-variables.
Info Catalog
(mysql.info) calculating-days
(mysql.info) examples
automatically generated byinfo2html