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;

欢迎大家阅读《mysql 仿照oracle的sequence功能》,跪求各位点评，by 搞代码

第二步：创建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');

微信 赏一包辣条吧~

支付宝 赏一听可乐吧~