DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(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