AUTO_RANDOM & AUTO_INCREMENT

TiDB supports AUTO_INCREMENT (MySQL compatible) and AUTO_RANDOM (a TiDB feature) for generating unique IDs automatically.

AUTO_INCREMENT

The syntax is the same as MySQL:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

INSERT INTO users (name) VALUES ('John');
INSERT INTO users (name) VALUES ('Mike');
SELECT * FROM users;

Differences from MySQL

• In MySQL, AUTO_INCREMENT is always sequential per node
• In TiDB, AUTO_INCREMENT is globally unique but not always sequential
• Values may skip around (1, 3, 5, etc.)

MySQL Compatibility Mode

-- Enable MySQL mode for sequential AUTO_INCREMENT
SET GLOBAL tidb_auto_increment_mode = 'mysql';

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

AUTO_RANDOM (TiDB Feature)

AUTO_RANDOM distributes IDs randomly to reduce hotspots on the primary key. This is very helpful for write-heavy workloads.

Syntax

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_RANDOM,
    user_id INT NOT NULL,
    amount DECIMAL(12, 2)
);

-- AUTO_RANDOM with a bit range
CREATE TABLE events (
    id BIGINT PRIMARY KEY AUTO_RANDOM(3, 16),
    -- 3 bits for the shard (8 shards)
    -- 16 bits for auto-increment
    event_type VARCHAR(50)
);

Comparison

When to Use AUTO_RANDOM?

• Tables with high write throughput
• Tables that use an ID as the primary key
• Tables that experience hotspots with AUTO_INCREMENT

AUTO_RANDOM vs ULID/UUID

-- Using AUTO_RANDOM (recommended in TiDB)
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_RANDOM,
    name VARCHAR(100)
);

-- Or using BIGINT with AUTO_RANDOM
CREATE TABLE logs (
    id BIGINT PRIMARY KEY AUTO_RANDOM(5, 32),
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Reset AUTO_INCREMENT/AUTO_RANDOM

-- Reset AUTO_INCREMENT
ALTER TABLE users AUTO_INCREMENT = 1;

-- Reset AUTO_RANDOM (remove the data first)
TRUNCATE TABLE users;

Tips

• Use AUTO_RANDOM for new tables with integer primary keys
• Use AUTO_INCREMENT when MySQL compatibility is required
• For composite primary keys, consider using SHARD_ROW_ID_BITS

In the next chapter, we will look at the statement cache in TiDB.