PHP, MySQL, Drupal, .htaccess, Robots.txt, Phponwebsites: Reset auto increment initial value in mysql

17 Feb 2014

Reset auto increment initial value in mysql

                     Auto_ increment is used to increase by 1 while insert a new data to mysql table. The auto increment column must be defined as key. By default auto_increment is start with 1. Suppose you want to auto_increment starts with 'N' values. Then you can change the auto_increment initial value in mysql. IT is possible. First you create table with auto_increment. Then use following mysql query :

                   Alter table table_name AUTO_INCREMENT=N

Where, N can be any numbers like 100,1000,10000...

Create table with auto_increment in mysql:

                      You can add auto_increment while creating new table in mysql. The mysql query is:

                       CREATE TABLE table1 
                               (No int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
                                 Game_name varchar(15), Image varchar(15)) 

Now the table is created with auto increment. If you add any values, then auto_increment starts with 1 in mysql. Consider following example. You add 3 values to mysql table. ie,

                   insert into table1 values('','sample1','sample1.jpg')
                   insert into table1 values('','sample3','sample2.jpg')
                   insert into table1 values('','sample3','sample3.jpg')

Now your table look like this:

reset auto increment initial value in mysql

Change auto_increment initial value in mysql:    

                                 You change auto_increment initial value to 100. The mysql query is:

                   Alter table table1 AUTO_INCREMENT=100

Now you insert data to your mysql table. Then it look like this:

change auto increment initial value in mysql


  1. Thank you for good article..

  2. Nice article .. Keep it up ..

  3. Really Nice article,
    But I have a table with 2 columns, for one column I need normal auto-increment which starts with 1 and for another column I need auto-increment with initial value N.

    Is it possible??
    Really appreciate your response..

    1. You can't create 2 auto_increment in a mysql table. You can make one column as an auto_increment. Then make another column as auto_increment manually in mysql.
      First column is an auto_increment column. when you insert values to table, you add manually "0" to second column.

      insert into table_name values('','0','name').

      Then get max(2nd column) and insert 2ndcolumn+1 values to mysql table. It can be done in PHP

  4. No doubt this is very nice query, i wrote about it, but i try to keep my article more simple and small :) hope you like it.