没有increase类型的数据库中创建sequence供采番使用

浏览数：28 / 时间：2015年06月12日

对于一些没有自增长类型的数据库，要使用自增长的采番方式，一般采用自建Sequence的方法。

以MySql为例：

首先，创建一张存放数据的Sequence表

 1 DROP TABLE IF EXISTS `sequence`;
 2 CREATE TABLE `sequence` (
 3 `name` VARCHAR(10) NOT NULL COMMENT ‘番号种类‘,
 4 `current_value` INT NOT NULL COMMENT ‘当前值‘,
 5 `increment` INT NOT NULL COMMENT ‘递增量‘
 6 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT=‘采番表‘;
 7 
 8 ALTER TABLE `sequence` add PRIMARY KEY `PK_sequence` (
 9 `name`
10 );

其次，创建操作函数currval、nextval、setval

 1 -- 取当前值的函数
 2 DROP FUNCTION IF EXISTS currval;
 3 DELIMITER $$
 4 CREATE
 5     FUNCTION currval(seq_name VARCHAR(10))
 6         RETURNS INTEGER
 7         COMMENT ‘取当前SEQUENCE值‘
 8     BEGIN
 9         DECLARE curvalue INTEGER;
10         SET curvalue = 0;
11         SELECT current_value INTO curvalue FROM sequence WHERE name = seq_name;
12         RETURN curvalue;
13     END$$
14 DELIMITER ;
15 
16 -- 取下一个值的函数
17 DROP FUNCTION IF EXISTS nextval;
18 DELIMITER $$
19 CREATE
20     FUNCTION nextval(seq_name VARCHAR(10))
21         RETURNS INTEGER
22         COMMENT ‘取下一个SEQUENCE值‘
23     BEGIN
24         DECLARE seq_name_exits INTEGER;
25         SELECT COUNT(*) INTO seq_name_exits FROM sequence WHERE name = seq_name;
26         IF (seq_name_exits = 0) THEN
27             INSERT INTO sequence (name,current_value,increment) VALUES (seq_name,100000,1);
28         END IF;
29         UPDATE sequence SET current_value = current_value + increment WHERE name = seq_name;
30         RETURN currval(seq_name);
31     END$$
32 DELIMITER ;
33 
34 -- 更新当前值的函数
35 DROP FUNCTION IF EXISTS setval;
36 DELIMITER $$
37 CREATE 
38     FUNCTION setval(seq_name VARCHAR(10), value INTEGER)
39         RETURNS INTEGER
40         COMMENT ‘更新当前SEQUENCE值‘
41     BEGIN
42         UPDATE sequence
43         SET current_value = value
44         WHERE name = seq_name;
45         RETURN currval(seq_name);
46     END$$
47 DELIMITER ;

最后，创建类似increase方法，供外部调用，此处假设为订单号，采番规则为订单类型+年月日+每日6位连号

 1 -- 订单No采番函数
 2 DROP FUNCTION IF EXISTS getNewOrderNo;
 3 DELIMITER $$
 4 CREATE 
 5     FUNCTION getNewOrderNo(order_type varchar(10))
 6     -- order_type: CX|CP
 7         RETURNS VARCHAR(20)
 8         COMMENT ‘订单No采番函数‘
 9     BEGIN
10         DECLARE curday VARCHAR(8);
11         DECLARE seq_value VARCHAR(6);
12         SELECT date_format(now(),‘%Y%m%d‘) INTO curday;
13         SELECT LPAD(nextval(concat(order_type,curday)),6,‘0‘) INTO seq_value;
14         RETURN concat(order_type, curday, seq_value);
15     END$$
16 DELIMITER ;

初始化Sequence表数据

1 -- 订单No Sequence初期化
2 INSERT INTO sequence (name,current_value,increment) VALUES (concat(‘CX‘,date_format(now(),‘%Y%m%d‘)),100000,1);
3 INSERT INTO sequence (name,current_value,increment) VALUES (concat(‘CP‘,date_format(now(),‘%Y%m%d‘)),100000,1);