日期+流水号生成序列

CREATE TABLE `sequence` (
  `name` varchar(255) NOT NULL DEFAULT '',
  `current_value` int(11) DEFAULT NULL,
  `increment` int(11) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

BEGIN
    DECLARE rValue VARCHAR(50);/*返回值201208080001*/
    DECLARE sValue INT;/*系统时间20120808*/
    DECLARE cValue INT;/*表时间20120606*/
    DECLARE cNum   INT;
    SET rValue=0;
    SELECT DATE_FORMAT(SYSDATE(), '%Y%m%d') INTO sValue;
    SELECT current_value INTO cValue FROM sequence; 
    IF sValue<>cValue THEN
        UPDATE sequence SET current_value=sValue,increment=1 WHERE `name`='RENTID';
    END IF;
    SELECT current_value INTO rValue FROM sequence WHERE `name`='RENTID';
    SELECT increment INTO cNum FROM sequence WHERE `name`='RENTID';
    SET rValue=CONCAT(rValue,LPAD(cNum,4,0));
    UPDATE sequence SET increment=(cNum+1) WHERE `name`='RENTID';
    RETURN rValue;
END

转载于:https://www.cnblogs.com/duhl/archive/2012/11/08/2760512.html