Add custom auto increment value

Question

Am trying to create a MySQL that tells

auto_increment

Where and how to start incrementing. Each time I run the code, it always tells me I have error near

auto.

Each time I removed the "=", it always work.

This is the code

CREATE TABLE staff(
id into(11) not null primary key auto_increment=001,
Names varchar(109) not null
);

What am I doing wrong

Answer 1

First create table like below:

CREATE TABLE staff( id into(11) not null primary key auto_increment, Names varchar(109) not null );

then alter to customized auto increment.

ALTER TABLE staff AUTO_INCREMENT=001;

Answer 2

Schema (MySQL v5.7)

CREATE TABLE staff(
id int not null primary key auto_increment,
Names varchar(255) not null
)auto_increment=001;

---

View on DB Fiddle

Answer 3

The default auto-increment value is an option on the table not on the column (perhaps counterintuitively, but a table is only allowed to have one such column).

The syntax looks like:

CREATE TABLE staff (
    id int not null primary key auto_increment,
    Names varchar(109) not null
) auto_increment = 10;

Here is a db<>fiddle.