How to set initial value and auto increment in MySQL

Question

How do I set the initial value for an  id  column in a MySQL table that start from 1001   I want to do an insert  INSERT INTO users  name  email  VALUES     name       email       Without specifying the initial value for the id column

User · Answer

MySQL Workbench  If you want to avoid writing sql  you can also do it in MySQL Workbench by right clicking on the table  choose  Alter Table      in the menu   When the table structure view opens  go to tab  Options   on the lower bottom of the view   and set  Auto Increment  field to the value of the next autoincrement number   Don t forget to hit  Apply  when you are done with all changes   PhpMyAdmin   If you are using phpMyAdmin  you can click on the table in the lefthand navigation  go to the tab  Operations  and under Table Options change the AUTO INCREMENT value and click OK

User · Answer

For this you have to set AUTO INCREMENT value  ALTER TABLE tablename AUTO INCREMENT    lt INITIAL VALUE gt    Example   ALTER TABLE tablename AUTO INCREMENT   101

User · Answer

MySQL - Setup an auto-incrementing primary key that starts at 1001   Step 1  create your table   create table penguins    my id       int 16  auto increment     skipper     varchar 4000     PRIMARY KEY  my id      Step 2  set the start number for auto increment primary key   ALTER TABLE penguins AUTO INCREMENT 1001    Step 3  insert some rows   insert into penguins  skipper  values  We need more power     insert into penguins  skipper  values  Time to fire up    insert into penguins  skipper  values  kowalski s nuclear reactor       Step 4  interpret the output   select   from penguins   prints    1001    We need more power    1002    Time to fire up   1003    kowalski  s nuclear reactor

User · Answer

SET GLOBAL auto increment offset 1  SET GLOBAL auto increment increment 5  auto increment offset  interval between successive column values auto increment offset  determines the starting point for the AUTO INCREMENT column value  The default value is 1  read more here

User · Answer

You could also set it in the create table statement    CREATE TABLE      AUTO INCREMENT 1000

User · Answer

First you need to add column for auto increment  alter table users add column id int 5  NOT NULL AUTO INCREMENT FIRST   This query for add column at first   Now you have to reset auto increment initial value  So use this query   alter table users AUTO INCREMENT 1001   Now your table started with 1001

User · Answer

Also   in PHPMyAdmin   you can select table from left side list of tables  then do this by going there   Operations Tab- Table Options- AUTO INCREMENT   Now  Set your values and then press Go under the Table Options Box

User · Answer

Alternatively  If you are too lazy to write the SQL query  Then this solution is for you     Open phpMyAdmin Select desired Table Click on Operations tab Set your desired initial Value for AUTO INCREMENT Done

User · Answer

Use this   ALTER TABLE users AUTO INCREMENT 1001    or if you haven t already added an id column  also add it  ALTER TABLE users ADD id INT UNSIGNED NOT NULL AUTO INCREMENT      ADD INDEX  id

User · Answer

With CREATE TABLE statement CREATE TABLE my table     id INT UNSIGNED NOT NULL AUTO INCREMENT    name VARCHAR 100  NOT NULL    PRIMARY KEY  id    AUTO INCREMENT   100   or with ALTER TABLE statement ALTER TABLE my table AUTO INCREMENT   200

[mysql] How to set initial value and auto increment in MySQL?

Examples related to mysql

Examples related to insert

Examples related to auto-increment