(mysql.info.gz) example-AUTO_INCREMENT

Info Catalog

(mysql.info.gz) Calculating days

(mysql.info.gz) 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 value
 is not affected by another connection also doing inserts.
 
 Note: For a multiple-row insert, `LAST_INSERT_ID()'/`mysql_insert_id()'
 will 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 will be 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.)
 

Info Catalog

(mysql.info.gz) Calculating days

(mysql.info.gz) Examples