mysql self increment ID start value modification method

  • 2020-06-01 11:11:59
  • OfStack

In mysql, many friends think that it is impossible to modify the value of ID when the field is of type AUTO_INCREMENT. In fact, this understanding is wrong. The starting value modification and setting method of mysql self-incrementing ID is introduced below.
The usual way to set a self-incrementing field:

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

After creating the table, add:

alter table table1 add id int auto_increment primary key  Autoincrement field, 1 It's going to be zero primary key.

Many times you want id in table not to start at 1, like qq, id to start at 10000
The code is as follows:

alter table users AUTO_INCREMENT=10000;

This statement also applies to modifying the id of an existing table, such as when the data is deleted in large quantities and the id is returned from 654321 to 123456

alter table users AUTO_INCREMENT=123456;

However, after the actual test, there is no problem with the single-machine Mysql, and it is invalid under Mysql Cluster. Maybe the mechanism on the primary key is still different. We have time to study it for 1 time

In Rails migration, it is written as follows:

create_table :articles, :options => 'AUTO_INCREMENT = 1001' do |t|   
# xxx todo      

Set up ID from N

CREATE TABLE TABLE_1 ( ID INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, // ID Listed as an unsigned integer, the column value cannot be null, cannot be repeated, and is self-incrementing.  NAME VARCHAR(5) NOT NULL ) AUTO_INCREMENT = 100; ( ID The column from 100 Starting from scratch) 

If you want to increment ID from the default, just start


Can be

Related articles: