Mysql: set the primary key to automatically increase the starting value

Achievement goal: The value of the self-incrementing primary key under mysql will start from 10000, that is, the seed for realizing the self-incrementing primary key is 10000.

Option 1) Use alter table `tablename` AUTO_INCREMENT=10000

After creating the auto-incrementing primary key, use alter table `tablename` AUTO_INCREMENT=10000 to modify the starting value of the table.

drop table if exists `trace_test`;CREATE TABLE `trace_test` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;alter table `trace_test` AUTO_INCREMENT=10000;insert into `trace_test`(`name`)values('name2');select * from `trace_test`;

Result:

id name10000 name2

Scenario 2) Set the AUTO_INCREMENT 10000 parameter when creating a table

drop table if exists `trace_test`;CREATE TABLE `trace_test` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT 10000 DEFAULT CHARSET=utf8 ;insert into `trace_test`(`name`)values('name2');select * from `trace_test`;

Result:

id name10000 name2

3) If the table already has data, set auto_increment=10000 after truncate, which is feasible.

drop table if exists `trace_test`;CREATE TABLE `trace_test` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;insert into `trace_test`(`name`)values('name1');select * from `trace_test`;truncate table `trace_test`;alter table `trace_test` AUTO_INCREMENT=10000;insert into `trace_test`(`name`)values('name2');select * from `trace_test`;

Result1:

id name10000 names

Result2:

id name10000 name2

4) If the table already has data, set auto_increment=10000 after delete from, which is feasible.

drop table if exists trace_test;CREATE TABLE trace_test (id int(20) NOT NULL AUTO_INCREMENT,name varchar(255) DEFAULT NULL,PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;insert into trace_test(name)values('name1');select * from trace_test;delete from `trace_test`;alter table trace_test AUTO_INCREMENT=10000;insert into trace_test(name)values('name2');select * from trace_test;

Result1:

id name10000 names

Result2:

id name10000 name2