How to set initial value and auto increment in MySQL?

user_146128 0
Viewed 701624

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.

auto-increment insert mysql
10 Answers

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;
user_98055900

You could also set it in the create table statement.

`CREATE TABLE(...) AUTO_INCREMENT=1000`
user_10611550

Alternatively, If you are too lazy to write the SQL query. Then this solution is for you. enter image description here

  1. Open phpMyAdmin
  2. Select desired Table
  3. Click on Operations tab
  4. Set your desired initial Value for AUTO_INCREMENT
  5. Done..!
user_13255440

SET GLOBAL auto_increment_offset=1;

SET GLOBAL auto_increment_increment=5;

auto_increment_increment: 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_12187260
Related