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