MySQL生成分布式id

一、分布式id算法

首先需要在id_sequence表中配置一个唯一的key_name，分布式id的起始值和步长。
当服务需要分布式id时，会首先从数据库中获取一个start_id，然后将start_id更新成start_id+step。那么从start_id~start_id+step段内对的所有id，都属于当前这个服务了。如果start_id用完了，就会按照相同的流程重新申请一个start_id。

二、算法的MySQL实现

CREATE TABLE `id_sequence` (
  `key_name` varchar(255) NOT NULL,
  `start_id` int(11) NOT NULL,
  `step` int(11) NOT NULL,
  PRIMARY KEY (`key_name`),
  UNIQUE KEY `idx_keyname` (`key_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

delimiter $$
CREATE FUNCTION `id_nextval`(seq_name VARCHAR(64)) RETURNS bigint(20)
    DETERMINISTIC
BEGIN
    DECLARE nextval BIGINT;
    DECLARE seq_lock INT;
    DECLARE step INT DEFAULT 200;
    SET seq_lock = -1;
    SELECT GET_LOCK(seq_name, 15) into seq_lock;
    IF seq_lock = 1 then
      SELECT start_id INTO nextval FROM id_sequence WHERE key_name = seq_name for update;
			IF seq_lock != -1 THEN
				UPDATE id_sequence SET start_id = nextval+step WHERE key_name = seq_name;
      END IF;
      SELECT RELEASE_LOCK(seq_name) into seq_lock;
    END IF;
    RETURN nextval;
END
END $$
delimiter ;