MYSQL Tutorial

MySQL AUTO_INCREMENT

The AUTO_INCREMENT attribute automatically generates a unique sequential number for new rows. It’s most commonly used with integer primary key columns to ensure each record has a distinct identifier.

Defining AUTO_INCREMENT in CREATE TABLE

CREATE TABLE users (
  user_id   INT         NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username  VARCHAR(50) NOT NULL,
  email     VARCHAR(100)
);
  

How It Works

• First row inserted → user_id = 1, next → 2, and so on.
• If a row is deleted, its number isn’t reused by default.
• Only one AUTO_INCREMENT column allowed per table, and it must be indexed (usually PRIMARY KEY).

Resetting or Setting the Start Value

-- Start from 1000 instead of 1
ALTER TABLE users AUTO_INCREMENT = 1000;
  

Inserting Explicit Values

You can manually specify a value for the AUTO_INCREMENT column; however, it should be ≥ current counter, or MySQL will adjust the counter to max(existing, inserted)+1.

INSERT INTO users (user_id, username, email)
VALUES (500, 'alice', 'alice@example.com');
-- Next auto value becomes 501 (if higher than current)
  

Best Practices & Tips

• Use AUTO_INCREMENT for surrogate keys rather than business data.
• Avoid gaps in sequences if consistency is critical; MySQL may skip numbers on rolled-back transactions.
• To reuse deleted IDs, you must explicitly reset AUTO_INCREMENT, but this can risk duplicates.
• For distributed systems, consider UUIDs or other strategies since AUTO_INCREMENT is single-node.