Modification method of starting value of MySQL auto increment ID

Many of my friends in mysql think that fields with an AUTO_INCREMENT type ID cannot be modified, but that’s actually a mistake. Here’s how to change and set the starting value of an autoincrement ID in mysql.

general method of setting autoincrement field:

add:

when creating table

create table table1(id int auto_increment primary key,...)

after creating the table add:

alter table table1 add id int auto_increment primary key 自增字段，一定要设置为primary key.

many times we hope that the id of the data in the table does not start from 1, like qq, id starts from 10000

code is as follows:

add:

when creating table

CREATE TABLE `orders` (
  `order_num` int(11) NOT NULL auto_increment,
  `order_date` datetime NOT NULL,
  `cust_id` int(11) NOT NULL,
  PRIMARY KEY  (`order_num`),
  KEY `fk_orders_customers` (`cust_id`),
  CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8;

create table add: 

alter table users AUTO_INCREMENT=10000;

and this statement is also applicable to modify the id of the existing table, such as after a large number of data deletion, id from 654321 back to 123456

alter table users AUTO_INCREMENT=123456;


Read More: