Wednesday, February 8, 2012

Manipulating Auto Increment values in MySql



  1. Set the MySql variables auto_increment_increment and auto_increment_offset variables.
  2. Play with the Auto_Increment in Information schema.
To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this:
ALTER TABLE tbl AUTO_INCREMENT = 100;
Note:It is not possible to confine the effects of these two variables to a single table, and thus they do not take the place of the sequences offered by some other database management systems; these variables control the behavior of all AUTO_INCREMENT columns in all tables on the MySQL server. If the global value of either variable is set, its effects persist until the global value is changed or overridden by setting the session value, or until mysqld is restarted. If the local value is set, the new value affects AUTO_INCREMENT columns for all tables into which new rows are inserted by the current user for the duration of the session, unless the values are changed during that session.