Change auto increment starting number

Question

In MySQL  I have a table  and I want to set the auto increment value to 5 instead of  1   Is this possible and what query statement does this

User · Answer

Procedure to auto fix AUTO INCREMENT value of table  DROP PROCEDURE IF EXISTS update auto increment  DELIMITER    CREATE PROCEDURE update auto increment   table VARCHAR 64   BEGIN     DECLARE  max stmt VARCHAR 1024       DECLARE  stmt VARCHAR 1024           SET  inc    0       SET  MAX SQL    CONCAT  SELECT IFNULL MAX  id    0    1 INTO  inc FROM     table       PREPARE  max stmt FROM  MAX SQL      EXECUTE  max stmt      DEALLOCATE PREPARE  max stmt       SET  SQL    CONCAT  ALTER TABLE     table    AUTO INCREMENT        inc       PREPARE  stmt FROM  SQL      EXECUTE  stmt      DEALLOCATE PREPARE  stmt  END   DELIMITER    CALL update auto increment  your table name

User · Answer

If you need this procedure for variable fieldnames instead of id this might be helpful   DROP PROCEDURE IF EXISTS update auto increment  DELIMITER    CREATE PROCEDURE update auto increment   table VARCHAR 128    fieldname VARCHAR 128   BEGIN     DECLARE  max stmt VARCHAR 1024       DECLARE  stmt VARCHAR 1024           SET  inc    0       SET  MAX SQL    CONCAT  SELECT IFNULL MAX    fieldname     0    1 INTO  inc FROM     table       PREPARE  max stmt FROM  MAX SQL      EXECUTE  max stmt      DEALLOCATE PREPARE  max stmt       SET  SQL    CONCAT  ALTER TABLE     table    AUTO INCREMENT        inc       PREPARE  stmt FROM  SQL      EXECUTE  stmt      DEALLOCATE PREPARE  stmt  END    DELIMITER    CALL update auto increment  your table name    autoincrement fieldname

User · Answer

Yes  you can use the ALTER TABLE t AUTO INCREMENT   42 statement  However  you need to be aware that this will cause the rebuilding of your entire table  at least with InnoDB and certain MySQL versions  If you have an already existing dataset with millions of rows  it could take a very long time to complete   In my experience  it s better to do the following   BEGIN WORK  -- You may also need to add other mandatory columns and values INSERT INTO t  id  VALUES  42   ROLLBACK    In this way  even if you re rolling back the transaction  MySQL will keep the auto-increment value  and the change will be applied instantly   You can verify this by issuing a SHOW CREATE TABLE t statement  You should see    gt  SHOW CREATE TABLE t  G                             1  row                                    Table  t Create Table  CREATE TABLE  t          ENGINE InnoDB AUTO INCREMENT 43

User · Answer

You can also do it using phpmyadmin  Just select the table than go to actions  And change the Auto increment below table options  Don t forget to click on start

User · Answer

How to auto increment by one  starting at 10 in MySQL   create table foobar    id             INT PRIMARY KEY AUTO INCREMENT    moobar         VARCHAR 500      ALTER TABLE foobar AUTO INCREMENT 10   INSERT INTO foobar moobar  values   abc    INSERT INTO foobar moobar  values   def    INSERT INTO foobar moobar  values   xyz     select   from foobar    10    abc   11    def   12    xyz    This auto increments the id column by one starting at 10   Auto increment in MySQL by 5  starting at 10   drop table foobar create table foobar    id             INT PRIMARY KEY AUTO INCREMENT    moobar         VARCHAR 500      SET   auto increment increment 5  ALTER TABLE foobar AUTO INCREMENT 10   INSERT INTO foobar moobar  values   abc    INSERT INTO foobar moobar  values   def    INSERT INTO foobar moobar  values   xyz     select   from foobar   11    abc   16    def   21    xyz    This auto increments the id column by 5 each time  starting at 10

User · Answer

just export the table with data    then copy its sql like  CREATE TABLE IF NOT EXISTS  employees       emp badgenumber  int 20  NOT NULL AUTO INCREMENT     emp fullname  varchar 100  NOT NULL     emp father name  varchar 30  NOT NULL     emp mobile  varchar 20  DEFAULT NULL     emp cnic  varchar 20  DEFAULT NULL     emp gender  varchar 10  NOT NULL     emp is deleted  tinyint 4  DEFAULT  0      emp registration date  timestamp NOT NULL DEFAULT CURRENT TIMESTAMP     emp overtime allowed  tinyint 4  DEFAULT  1     PRIMARY KEY   emp badgenumber      UNIQUE KEY  bagdenumber    emp badgenumber      KEY  emp badgenumber    emp badgenumber      KEY  emp badgenumber 2    emp badgenumber     ENGINE InnoDB  DEFAULT CHARSET latin1 AUTO INCREMENT 111121326     now change auto increment value and execute sql

User · Answer

You can use ALTER TABLE to change the auto increment initial value   ALTER TABLE tbl AUTO INCREMENT   5    See the MySQL reference for more details

[mysql] Change auto increment starting number?

Examples related to mysql