mysql 模仿oracle的sequence功能
第一步:创建表
DROP TABLE IF EXISTS `seq`;
CREATE TABLE `seq` (
`id` BIGINT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL COMMENT 'sequence名称',
`max` BIGINT(11) NOT NULL DEFAULT '1' COMMENT '最大id',
`length`INT(2) NOT NULL DEFAULT '1' COMMENT '生成序列后的长度,以0补全',
`next` INT(2) NOT NULL DEFAULT '1' COMMENT '增长的长度',
`rules` VARCHAR(255) DEFAULT NULL COMMENT '规则以###max_id###做为替换',
PRIMARY KEY (`id`),
UNIQUE KEY `fk_name` (`name`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
第二步:创建function bj
引用
DROP FUNCTION IF EXISTS bj;
DELIMITER $
CREATE FUNCTION bj (seq_name VARCHAR(50))
RETURNS VARCHAR(20)
CONTAINS SQL
BEGIN
DECLARE seq_rules VARCHAR(255);
DECLARE seq_length INT(2);
DECLARE seq_max BIGINT(20);
DECLARE max_id_length INT(20);
DECLARE max_id VARCHAR(20);
SELECT rules,seq.`length`,`max` INTO seq_rules, seq_length,seq_max FROM seq WHERE NAME = seq_name ;
SET max_id = seq_max;
SET max_id_length = CHARACTER_LENGTH(max_id);
#长度不够补0
WHILE seq_length > max_id_length DO
SET max_id = CONCAT('0',max_id);
SET max_id_length = CHARACTER_LENGTH(max_id);
END WHILE;
#替换规则
IF seq_rules IS NOT NULL AND INSTR(seq_rules,'###max_id###') > 0 THEN
SET max_id = REPLACE(seq_rules,'###max_id###',max_id);
END IF;
RETURN max_id;
END$
DELIMITER ;
第三步:创建function nextval
DROP FUNCTION IF EXISTS nextval;
DELIMITER $
CREATE FUNCTION nextval (seq_name VARCHAR(50))
RETURNS VARCHAR(20)
CONTAINS SQL
BEGIN
UPDATE seq SET `max` = `max` + NEXT WHERE NAME = seq_name;
RETURN bj(seq_name);
END$
DELIMITER ;
第四步: 插入两个seq
INSERT INTO `seq` (`name`,`max`,`length`,`next`,`rules`) VALUES ('complaint_seq_b', '0', '7', '1', 'B###max_id###' );
INSERT INTO `seq` (`name`,`max`,`length`,`next`,`rules`) VALUES ('complaint_seq_a', '0', '7', '1','A###max_id###');
第五步:测试
SELECT nextval('complaint_seq_b');
SELECT nextval('complaint_seq_a');