遇到一个需求,需要生成一个流水号,要求每天从1开始。
解决方案如下:
使用数据库生成这个流水号,传入字符串,传入已传入的字符串,则返回上次生成的序号+1,传入新的字符串,则序号从1开始。
第一步:新建表 t_code_ident
CREATE TABLE `t_code_ident` (
`id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`currentvalue` int(10) NULL DEFAULT 1,
`increaseby` int(10) NULL DEFAULT 1,
`maxvalue` int(10) NULL DEFAULT 999999999,
`minvalue` int(10) NULL DEFAULT 1,
`cycle` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`dateStr` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '2020-05-09',
UNIQUE INDEX `id`(`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
第二步:创建函数
函数1
CREATE FUNCTION `getSeqNextValue`(arg varchar(30)) RETURNS int(11)
DETERMINISTIC
BEGIN
DECLARE str VARCHAR(50);
DECLARE intResult NUMERIC(20,0);
DECLARE intCount NUMERIC(20,0);
DECLARE intMax NUMERIC(20,0);
DECLARE intMin NUMERIC(20,0);
DECLARE strlsCycle CHAR(1);
DECLARE dateStrs VARCHAR(50);
set intResult=0;
set intCount=0;
set intMax=0;
set intMin=0;
select count(ID) into intCount from t_code_ident WHERE ID=arg;
IF intCount = 0 then
insert into t_code_ident(id,dateStr) VALUES(arg, DATE_FORMAT(now(),'%Y-%m-%d'));
set intResult = 1;
ELSE
SELECT DATE_FORMAT(now(),'%Y-%m-%d') into str FROM DUAL;
select dateStr into dateStrs from t_code_ident WHERE ID=arg;
IF dateStrs != str THEN
update t_code_ident SET currentvalue = 1, dateStr = str WHERE ID=arg;
set intResult = 1;
ELSE
select s.currentvalue + s.increaseby, s.maxValue,s.minvalue,s.cycle into intResult, intMax,intMin,strlsCycle from t_code_ident s where id=arg;
IF intResult<intMax then
update t_code_ident SET currentvalue = intResult WHERE ID=arg;
set intResult = intResult;
ELSE
update t_code_ident SET currentvalue = intMin WHERE ID=arg;
set intResult = intMin;
END IF;
END IF;
END IF;
RETURN intResult;
END
函数2
CREATE FUNCTION `getSwqNo`(`in_strType` varchar(30)) RETURNS varchar(10) CHARSET utf8
BEGIN
DECLARE v_intValue NUMERIC(20,0);
DECLARE v_strSn VARCHAR(30);
DECLARE v_strTmp VARCHAR(10);
SET v_intValue = getSeqNextValue(in_strType);
select LPAD(v_intValue,5,"0") into v_strTmp from dual;
RETURN v_strTmp;
END
第三步:使用
可以在数据库直接查询
select getSwqNo('test') from dual;
在代码中使用
@Select("select getSwqNo(#{name}) from dual")
String getSeqNo(@Param("name") String name);