Setting the starting value of an AUTO_INCREMENT field in MySQL

Question

I have a simple question in creating table in database. Here's my code:

$query="CREATE TABLE users (
    userid int(5) not null AUTO_INCREMENT, 
    firstname varchar(20),
    lastname varchar(20),
    username varchar(30),
    password varchar(32),
    email varchar(50),
    age int(2),
    PRIMARY KEY (userid)
)";

I want the USERID AUTO INCREMENT to start in a SPECIFIC NUMBER.

example, starting in 99001....

How can I do that?

Answer 1

You have two ways to set the start value of an AUTO_INCREMENT field. You can either call the following ALTER TABLE command if your table already exists:

ALTER TABLE users AUTO_INCREMENT = 99001;

Otherwise you can also use the set the start value directly in the CREATE TABLE command as follows:

CREATE TABLE users (
   userid int(5) not null AUTO_INCREMENT, 
   firstname varchar(20),
   lastname varchar(20),
   username varchar(30),
   password varchar(32),
   email varchar(50),
   age int(2),
   PRIMARY KEY (userid)
) AUTO_INCREMENT = 99001;

Test case:

CREATE TABLE users (
   userid int(5) not null AUTO_INCREMENT, 
   firstname varchar(20),
   lastname varchar(20),
   username varchar(30),
   password varchar(32),
   email varchar(50),
   age int(2),
   PRIMARY KEY (userid)
);
Query OK, 0 rows affected (0.04 sec)

ALTER TABLE users AUTO_INCREMENT = 99001;
Query OK, 0 rows affected (0.05 sec)

INSERT INTO users (firstname, lastname, username, password) 
           VALUES ('a', 'b', 'c', 'd');
Query OK, 1 row affected (0.00 sec)

SELECT * FROM users;
+--------+-----------+----------+----------+----------+-------+------+
| userid | firstname | lastname | username | password | email | age  |
+--------+-----------+----------+----------+----------+-------+------+
|  99001 | a         | b        | c        | d        | NULL  | NULL |
+--------+-----------+----------+----------+----------+-------+------+
1 row in set (0.00 sec)

Answer 2

You need to use the AUTO_INCREMENT keyword during table creation:

$query="CREATE TABLE users (
    userid int(5) not null AUTO_INCREMENT, 
    firstname varchar(20),
    lastname varchar(20),
    username varchar(30),
    password varchar(32),
    email varchar(50),
    age int(2),
    PRIMARY KEY (userid)
    ) AUTO_INCREMENT=99001";

Edit:

Corrected syntax.