create_accounts.sql

-- MySQL port of "Sharding & IDs at Instagram"
-- http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram

CREATE TABLE account_id_seq (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  stub CHAR(1) NOT NULL,
  UNIQUE KEY stub (stub)
);

INSERT INTO account_id_seq (stub) VALUES ('a');

DELIMITER $$
CREATE FUNCTION next_account_id()
RETURNS BIGINT
DETERMINISTIC
BEGIN
  DECLARE epoch BIGINT DEFAULT 1420070400000; -- 2015-01-01 00:00:00
  DECLARE seq_id BIGINT;
  DECLARE now BIGINT;
  DECLARE shard_id INT DEFAULT 0;
  DECLARE next_id BIGINT;

  REPLACE INTO account_id_seq (stub) values ('a');
  SELECT LAST_INSERT_ID() % 1024 INTO seq_id;
  SELECT FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000) INTO now;
  SET next_id = (now - epoch) << 23;
  SET next_id = next_id | (shard_id << 10);
  SET next_id = next_id | seq_id;

  RETURN next_id;
END$$
DELIMITER ;