mysql-流水号生成

使用MySql生产订单号

创建序列号表

1
2
3
4
5
6
7
8
create table seq_table
(
SEQ_NAME varchar(50) not null
primary key,
CURRENT_VALUE bigint default '1000000002' not null,
INCREMENT smallint(6) default '1' not null,
REMARK varchar(100) default '' not null
)engine = InnoDB;

获取下一个序号函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE DEFINER=`root`@`%` FUNCTION `FUN_SEQ`(SEQ VARCHAR(50)) RETURNS bigint(20)
BEGIN
UPDATE SEQ_TABLE
SET CURRENT_VALUE = CURRENT_VALUE + INCREMENT
WHERE SEQ_NAME=SEQ;
RETURN FUN_SEQ_CURRENT_VALUE(SEQ);
END;

CREATE DEFINER=`root`@`%` FUNCTION `FUN_SEQ_CURRENT_VALUE`(SEQ VARCHAR(50)) RETURNS bigint(20)
BEGIN
DECLARE SEQ_VALUE LONG;
SET SEQ_VALUE = 0;
SELECT CURRENT_VALUE INTO SEQ_VALUE
FROM SEQ_TABLE
WHERE SEQ_NAME=SEQ;
RETURN SEQ_VALUE;
END;

设置值

1
2
3
4
5
6
7
CREATE DEFINER=`root`@`%` FUNCTION `FUN_SEQ_SET_VALUE`(SEQ VARCHAR(50), VALUE INTEGER) RETURNS bigint(20)
BEGIN
UPDATE SEQ_TABLE
SET CURRENT_VALUE=VALUE
WHERE SEQ_NAME=SEQ;
RETURN FUN_SEQ_CURRENT_VALUE(SEQ);
END;