创建自增序列

1.

DROP TABLE IF EXISTS `sequence`;

CREATE TABLE `sequence` (
  `name` varchar(50) NOT NULL COMMENT '自增序列名称',
  `current_value` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '自增序列当前值',
  `increment` int(11) NOT NULL DEFAULT '1' COMMENT '自增序列增量',
  PRIMARY KEY (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `sequence` (`name`, `current_value`, `increment`) VALUES ('qcode', 1, 1);


-- ----------------------------
-- Function structure for `nextWeiXinQcode`
-- ----------------------------
DROP FUNCTION IF EXISTS `nextWeiXinQcode`;
DELIMITER ;;
CREATE  FUNCTION `nextWeiXinQcode`(seq_name VARCHAR(50)) RETURNS int(11)
BEGIN
   DECLARE
      currValue INT DEFAULT 0;


SELECT
   current_value INTO currValue
FROM
   sequence
WHERE
   upper(NAME) = upper(seq_name);


IF (currValue = 99999) THEN
   UPDATE sequence
SET current_value = 0
WHERE
   upper(NAME) = upper(seq_name);


END IF;


UPDATE sequence
SET current_value = current_value + increment
WHERE
   upper(NAME) = upper(seq_name);


SELECT
   current_value INTO currValue
FROM
   sequence
WHERE
   upper(NAME) = upper(seq_name);


RETURN currValue;


END
;;
DELIMITER ;