功能需求:每天的序列号从1开始,保留四位数,不足4位往前补0
1,新建一张表
CREATE TABLE `sequence` ( `day_id` date DEFAULT NULL COMMENT '账期', `name` varchar(50) COLLATE utf8mb4_bin NOT NULL COMMENT '序列的名字', `current_value` int(11) NOT NULL COMMENT '序列的当前值', `increment` int(11) NOT NULL COMMENT '序列的自增值', PRIMARY KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='序列表';
2,mybatis 写法
<!-- 获取序列号并更新序列号 --> <update id="nextval" parameterType="com.xxx.xxx.xxx.entity.Sequence"> <selectKey resultType="java.lang.Long" keyColumn="current_value" keyProperty="current_value" order="AFTER"> select current_value from sequence where name=#{name} </selectKey> UPDATE sequence SET current_value = CASE when day_id = CURDATE() then current_value + increment else 1 end, day_id = CASE when day_id = CURDATE() then day_id else CURDATE() end WHERE name = #{name}; </update>
3,调用
//编号规则为:07XXYYYYMMDDnnnn,即 4位区号+8位日期+4位序号,如长沙分公司3月8日提交的工单为:XXXX202303080001 String workOrderNumber = ""; //查询序列号 Sequence sequence = new Sequence(); sequence.setName(name); myPointMapper.nextval(sequence); //获取当前时间 Date date=new Date(); SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd"); //位数不足往前补0 String serial_number = StringUtils.leftPad(String.valueOf(sequence.getCurrent_value()), 4, "0"); workOrderNumber = "XXXX" + df.format(date) + serial_number;
记录一下平常遇到的问题及新的知识,方便以后查看