Implementing sequences with a stored function and triggers

Use this when AUTO_INCREMENT on one table is not enough: shared counters across tables, non-PK business numbers, or audit streams that must not be the clustered key.

Counter table

CREATE TABLE seq_generator (
  seq_name VARCHAR(64) NOT NULL PRIMARY KEY,
  next_val BIGINT UNSIGNED NOT NULL
) ENGINE=InnoDB;

INSERT INTO seq_generator(seq_name, next_val) VALUES ('order_no', 1);

Allocate

DELIMITER //
CREATE FUNCTION next_seq(p_name VARCHAR(64))
RETURNS BIGINT UNSIGNED
READS SQL DATA
BEGIN
  UPDATE seq_generator
     SET next_val = LAST_INSERT_ID(next_val + 1)
   WHERE seq_name = p_name;
  RETURN LAST_INSERT_ID() - 1;
END//
DELIMITER ;

LAST_INSERT_ID(expr) keeps the allocated value connection-local without a racey second SELECT.

Trigger

CREATE TRIGGER orders_bi BEFORE INSERT ON orders
FOR EACH ROW
SET NEW.order_no = next_seq('order_no');

Concurrency and gaps

InnoDB row locks serialise the same seq_name. That is correct and a bottleneck at very high allocation rates — shard sequence names or batch-allocate ranges. Gaps after rollback are normal. Gapless fiscal numbers need a different, usually single-threaded, design with explicit business rules.

If the number is one table’s primary key and gaps are fine, use AUTO_INCREMENT.