MySQL: Creating BIGINT unique random IDs

The inspiration for this post comes from the mysql forum.

The problem is common enough: you want your SQL tables to have unique, secure primary keys. You also don't want to kill your database performance by using string UUIDs as keys. You also don't want serialized auto numbering for keys that may present security risks later on.

One very solid solution to this problem is an IDs table. That is, create a table that holds the list of all important IDs to be used by all other tables in your schema. Such a table might look like this:

create table ids (
  id bigint unsigned not null primary key,
  taken tinyint default 0
);

Any time we run out of IDs, we can generate new ones with this stored procedure:

CREATE PROCEDURE create_ids (create_id_num INT)
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' BEGIN END;
  SET @i = 1;
  loop1: LOOP
      INSERT INTO ids (id) VALUES (FLOOR(1 + RAND() * POW(2,63)));
      SET @i = @i + 1;
      IF @i > create_id_num THEN
         LEAVE loop1;
      END IF;
  END LOOP loop1;
END;

Now when one of your tables needs a new id, simply grab a random open one from the IDs table:

SELECT id FROM ids WHERE taken = 0 LIMIT 100;
UPDATE ids SET taken=1 WHERE id=? AND taken=0;

In scripting language land, grab a set of ids and attempt to make the UPDATE with one of them. If the update fails, then some other process grabbed that ID. That's no biggie, just try another.

What if we run out of IDs? Create a trigger that ensures that there are more IDs created when the last one is taken:

CREATE TRIGGER make_additional_ids AFTER UPDATE ON ids
FOR EACH ROW
  BEGIN
    IF (SELECT COUNT(*) FROM ids WHERE taken = 0) = 0 THEN
       CALL create_ids(1000);
    END IF;
  END;

While there are some race conditions I've glossed over, this is a pretty good framework for having solid numeric IDs, which most databases want to fast comparisons.