content_image/776136.jpg
## InnoDB Notes 

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100; 

For information about AUTO_INCREMENT  usage specific to InnoDB , see Section 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB” . 

## MyISAM Notes 

•For MyISAM  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) ) ENGINE=MyISAM; 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: 

<img src='content_image/776136.jpg'>

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. 

## Further Reading 

More information about AUTO_INCREMENT  is available here: 

•How to assign the AUTO_INCREMENT  attribute to a column: Section 13.1.20, “CREATE TABLE Statement” , and Section 13.1.9, “ALTER TABLE Statement” . 

•How AUTO_INCREMENT  behaves depending on the NO_AUTO_VALUE_ON_ZERO  SQL mode: Section 5.1.11, “Server SQL Modes” . 

