Designing Short Numeric ID Generation Using MySQL Auto‑Increment and Segment Allocation

In order to generate short numeric account IDs, the team first considered the classic Snowflake algorithm, which produces 64‑bit IDs that are too long for user‑friendly display and storage.

Because the length was unacceptable, they explored using MySQL auto‑increment features. An accid column in a sharded account table stores the numeric ID, while a separate tbl_accid table with a single auto‑increment row is used to generate new IDs via REPLACE INTO .

CREATE TABLE `tbl_accid` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `stub` char(1) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UQE_tbl_accid_stub` (`stub`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The REPLACE INTO tbl_accid(`stub`) VALUES('a'); statement increments the id while keeping only one row, but under concurrent load it caused InnoDB deadlocks, prompting a switch to plain INSERT with duplicate‑key handling.

Several alternative schemes were evaluated, including pre‑allocating ID blocks across multiple MySQL servers (similar to Meituan’s MT‑Leaf) and using per‑shard auto‑increment offsets, but they introduced operational complexity.

The final solution introduced a dedicated tbl_account_freeid segment table. Each login server reserves a block of 1 000 IDs by inserting a row; the segment column determines the range [(segment‑1)*1000, segment*1000) . When a server stops, the unused count is written back to the left column to avoid waste.

INSERT INTO tbl_account_freeid (login_svr, segment, left) VALUES ('login01', 1, 0);

This design keeps the write side limited to a single row per server, eliminates deadlocks, and provides automatic handling of server scaling and failover while keeping IDs short and mostly sequential.