Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 75 ยท Tables, Constraints and Schema Design

AUTO_INCREMENT, UUIDs and ID Strategies

How MySQL AUTO_INCREMENT really behaves (gaps, LAST_INSERT_ID, offsets), plus UUID(), UUID_TO_BIN, ordered UUIDs and snowflake-style IDs, with live stand-ins.

Every row needs an identity, and the way you generate it affects insert speed, index size, security and how easily you can scale to several servers. MySQL's built-in answer is AUTO_INCREMENT: the server hands out 1, 2, 3 and so on. It is simple and fast, but it has quirks that surprise people, such as gaps that never fill. The alternatives, UUIDs and time-ordered IDs, solve different problems and bring their own trade-offs.

The AUTO_INCREMENT and UUID syntax in this lesson needs a MySQL server, so those blocks are read-only. For the live examples we use SQLite's AUTOINCREMENT and rowid as close stand-ins, and each one says where MySQL behaves differently. That contrast is useful, because the differences are exactly where bugs hide.

Four ways to make an ID

AUTO_INCREMENT Small, fast, ordered, one server decides.
Random UUID Unique anywhere, but scattered in the index.
Time-ordered UUID Unique and mostly sorted by creation.
Snowflake ID A 64-bit number built from time, node and counter.
1 / 2

How AUTO_INCREMENT works

You mark one column of the table with AUTO_INCREMENT. When an INSERT leaves the column out (or gives NULL or 0), MySQL assigns the next value. The column must be indexed, almost always as the primary key.

SQL
CREATE TABLE tickets (
  id    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1000;   -- optional: start at 1000

INSERT INTO tickets (title) VALUES ('a'), ('b'), ('c');
SELECT LAST_INSERT_ID();               -- 1000, the FIRST id of the multi-row insert

Pick the integer size deliberately. INT UNSIGNED stops at 4,294,967,295, and BIGINT UNSIGNED at 18,446,744,073,709,551,615. When the counter reaches the end of the type, the next insert fails with a duplicate key error on the maximum value. A busy table that burns a million numbers a day will exhaust an INT UNSIGNED in about twelve years. A signed INT, half that size, in about six. Because gaps are normal (see below), never size the column by counting rows. Size it by the number of IDs you will consume.

The next value is stored per table. Since MySQL 8.0 it is persistent: it survives a restart. In MySQL 5.7 and earlier the counter lived only in memory and was recalculated as MAX(id) + 1 at startup, so numbers of deleted top rows could be handed out again. You can change it by hand with ALTER TABLE tickets AUTO_INCREMENT = 5000; (MySQL will not go below MAX(id) + 1), and TRUNCATE TABLE resets it to the start.

Gaps are normal

Auto-increment values are not guaranteed to be consecutive. MySQL deliberately gives up gap-free numbering in exchange for concurrency. Gaps appear when:

  • A transaction rolls back. The numbers it took are not returned.
  • An insert fails after the number was taken, for example with a duplicate-key error. That includes INSERT IGNORE, and INSERT ... ON DUPLICATE KEY UPDATE, which burn a number even when no row is added.
  • A multi-row or INSERT ... SELECT statement reserves numbers in batches, so unused numbers from a batch are lost. With the default innodb_autoinc_lock_mode = 2 (interleaved, the default since MySQL 8.0), the numbers of concurrent bulk inserts may even interleave.
  • You delete rows. Deleted numbers are never reused.

So an AUTO_INCREMENT column gives you unique and increasing values per table. It does not give you counting, so do not use it as an invoice number or a "this is order number N" display value that must have no holes. Legal numbering needs its own counter table updated inside the transaction.

The live runner uses SQLite, where AUTOINCREMENT also never reuses numbers. The first example deletes the newest row and then triggers an ignored duplicate, and you can see both effects:

SQL Deleted and ignored inserts leave gaps

Press Run (or Ctrl + Enter) to see the result here.

After d, the table holds ids 1, 2 and 4: the deleted 3 is not reused. After the ignored insert, e gets 6, because 5 was spent on the skipped row. MySQL's INSERT IGNORE behaves the same way.

Here is one important place where MySQL and SQLite differ. A rolled-back insert does burn the numbers in MySQL, but SQLite rolls its counter back along with the transaction. Run it and compare with what MySQL would show:

SQL Rollback in SQLite (MySQL would leave a gap)

Press Run (or Ctrl + Enter) to see the result here.

SQLite prints ids 1 and 2. On an InnoDB table the same script produces ids 1 and 4, because the two numbers taken inside the rolled-back transaction are gone. This is the main reason a MySQL application can never assume gap-free ids.

LAST_INSERT_ID()

After an insert, you often need the new row's id, for example to insert child rows. LAST_INSERT_ID() returns the first auto-generated value of the most recent insert in your own connection. Other connections cannot change it, so it is safe under concurrency. Never use SELECT MAX(id), which is a race condition.

SQL
INSERT INTO orders (customer_id) VALUES (42);
SET @order_id = LAST_INSERT_ID();
INSERT INTO order_items (order_id, sku, qty) VALUES (@order_id, 'A-1', 2);

Details to remember:

  • For a multi-row insert it returns the first id of the batch, not the last. The others follow consecutively within that statement when the lock mode allows it.
  • It is per connection, so with a connection pool it must be read on the same connection that did the insert. Database drivers expose it as lastInsertId() (PDO) or insertId and handle this for you.
  • If the insert does not generate a value, it keeps its previous value, and a failed insert does not reset it.
  • LAST_INSERT_ID(expr) can store any value as a connection-local counter, a trick used for lightweight sequences.

SQLite's last_insert_rowid() is the live equivalent, as shown in the first example.

auto_increment_increment and auto_increment_offset

Two server variables change the stride: auto_increment_increment (default 1) is the step between values, and auto_increment_offset (default 1) is where the sequence starts. They exist for multi-source setups where several servers accept writes and must not create the same id. With two writers, one server uses an increment of 2 and offset 1 (1, 3, 5, ...), and the other an increment of 2 and offset 2 (2, 4, 6, ...).

SQL
-- on writer A, in my.cnf or SET PERSIST
SET PERSIST auto_increment_increment = 2;
SET PERSIST auto_increment_offset    = 1;
-- on writer B
SET PERSIST auto_increment_increment = 2;
SET PERSIST auto_increment_offset    = 2;

MySQL Group Replication sets this up for you in its multi-primary mode, with its own variables (group_replication_auto_increment_increment, default 7). The scheme works, but it wastes numbers and makes the stride a hard-coded cluster size. You can see the result with two plain tables standing in for two writers:

SQL Two writers with interleaved id ranges

Press Run (or Ctrl + Enter) to see the result here.

The merged table has ids 1 to 6 with no clash. Adding a third writer later means changing every server's increment, which is why modern designs prefer one of the approaches below.

UUIDs

A UUID is a 128-bit identifier written as 36 characters, such as 3f2504e0-4f89-11d3-9a0c-0305e82c3301. Any application or server can create one without coordination, with practically no chance of a collision. That makes them good for systems with several writers, for offline clients that create records before syncing, and for IDs you do not want people to guess or count.

MySQL's UUID() function returns a version 1 UUID, which contains a timestamp and the server's identity. The common application libraries generate version 4 (fully random), and the newer version 7 (time-ordered).

Storing UUIDs

The most common mistake is CHAR(36). It uses 36 bytes per value, and every secondary index copies it. Store the 16 raw bytes in BINARY(16), and convert at the edges:

SQL
CREATE TABLE sessions (
  id      BINARY(16) NOT NULL PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL
);

INSERT INTO sessions (id, user_id) VALUES (UUID_TO_BIN(UUID()), 7);

SELECT BIN_TO_UUID(id) AS id, user_id FROM sessions;

UUID_TO_BIN() packs the text into 16 bytes and BIN_TO_UUID() unpacks it. Both accept an optional second argument, the swap flag, explained next.

Why random UUIDs hurt InnoDB

InnoDB stores rows in primary key order. With an auto-increment key, every new row lands at the right edge of the B-tree, in a page that is already in memory. With a random UUIDv4, every insert goes to a random place, which means:

  • Many different pages must be read into the buffer pool and modified.
  • Pages split in the middle, leaving them half empty, so the table and its indexes grow larger.
  • The working set is the whole index, so a table that outgrows memory slows down sharply.

The effect is small on a table of a million rows and serious on a table of a billion.

Ordered UUIDs

The fix is a UUID that begins with the time, so new ones sort after old ones. Two ways:

  1. Swap flag for version 1. A version 1 UUID stores the time in an order that does not sort. UUID_TO_BIN(UUID(), 1) moves the high time bits to the front so values sort by creation time. Read them back with BIN_TO_UUID(id, 1). The flag is only meaningful for version 1 UUIDs. It does nothing useful for version 4.
  2. UUIDv7 (RFC 9562). The first 48 bits are a Unix millisecond timestamp, followed by random bits, so v7 values sort by time with no swapping. MySQL does not generate v7 natively. Generate it in your application and store it as BINARY(16) as above.

The runner has no UUID functions in SQLite, so this example uses plain text values with a time-like prefix to show the ordering idea that makes v7 friendly to indexes:

SQL IDs that start with the time sort by creation

Press Run (or Ctrl + Enter) to see the result here.

The values come back in creation order (n = 1, 2, 3), so in a real table a new row would be appended at the end of the index.

Ordered UUIDs also reveal the creation time of a row, which is a privacy consideration if the IDs are public.

Snowflake-style IDs

A snowflake ID packs three things into a 64-bit integer, which fits BIGINT (signed) and is half the size of a UUID: a timestamp in the high bits, a worker or node number in the middle, and a per-millisecond counter in the low bits. The classic layout is 41 bits of milliseconds since a custom epoch (about 69 years), 10 bits of worker id (1,024 workers) and 12 bits of sequence (4,096 ids per millisecond per worker).

The IDs are unique across workers without coordination, they sort by time, and they stay compact. You pay with an application-side generator, clock handling (what happens when the clock moves backwards), and worker-id management.

This example builds such IDs with bit operations and takes them apart again. You can run it live because SQLite has the same <<, >>, | and & operators:

SQL Build and decode snowflake-style ids

Press Run (or Ctrl + Enter) to see the result here.

The three ids sort by time, and shifting and masking recovers the worker and sequence. Here the custom epoch is 1700000000000 and the offsets are 0 and 1 milliseconds. MySQL has the same operators, so the decoding query runs unchanged, and the column would be BIGINT UNSIGNED PRIMARY KEY without AUTO_INCREMENT.

Choosing a strategy

Need Good choice
One database, one writer, simple app BIGINT UNSIGNED AUTO_INCREMENT
Public IDs you do not want guessable Keep the integer key internally and add a separate random or v7 public_id column with a unique index
Several writers or offline creation UUIDv7 in BINARY(16), or a snowflake ID
Existing v1 UUID source UUID_TO_BIN(uuid, 1)
Gap-free human numbering (invoices) A dedicated counter table updated in the same transaction

Whatever you pick, use the same type everywhere an ID is referenced. Foreign keys need exactly matching types, and joining a BIGINT to a CHAR(36) is slow.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A transaction inserts rows into an AUTO_INCREMENT table and then rolls back. What happens to the numbers it used?

    InnoDB never returns auto-increment numbers, so rolled-back inserts leave gaps. That is why the column can't be used as a gap-free counter.
  2. 2. You run INSERT INTO t (v) VALUES ('a'), ('b'), ('c') on an empty table. What does LAST_INSERT_ID() return?

    For a multi-row insert, LAST_INSERT_ID() returns the first automatically generated id of the statement, and it is private to your connection.
  3. 3. Why is BINARY(16) preferred over CHAR(36) for UUID primary keys?

    The raw 16 bytes carry the same information as the 36-character text. Since InnoDB copies the primary key into every secondary index, the saving is multiplied.
  4. 4. What problem do time-ordered IDs (UUIDv7, snowflake) solve compared with random UUIDv4 keys?

    A random key spreads inserts across the whole B-tree. A time-ordered key appends, which keeps pages full and the working set small.

Summary

  • AUTO_INCREMENT gives unique, increasing values per table. They are not gap-free: rollbacks, failed or ignored inserts, batch reservations and deletes all leave holes, and numbers are never reused.
  • The counter is persistent since MySQL 8.0. Size the integer type by the IDs you will consume, not the rows you will keep.
  • LAST_INSERT_ID() is connection-local and returns the first id of a multi-row insert. Never use MAX(id).
  • auto_increment_increment and auto_increment_offset interleave ranges for multi-writer setups, at the cost of a fixed stride.
  • Store UUIDs as BINARY(16) with UUID_TO_BIN and BIN_TO_UUID. Random v4 keys fragment InnoDB indexes, so prefer ordered ones: v1 with the swap flag, or v7 generated by the application.
  • Snowflake IDs fit a 64-bit integer: timestamp, worker and sequence. Public IDs are best kept in a separate column.

Next: Views, which let you save a query and reuse it like a table.