当前位置：首页 > news >正文

MySql每天从0开始生成特定规则自增编号

news 来源：原创 2024/9/29 4:19:47

一、前言

1、按一定规则生单号，要求不重复
2、例如：前缀 + 日期 + 不重复流水号，whgz-20240528-00001
在这里插入图片描述

二、数据库操作

1、MySQL新建一张表sys_sequence
seq_name 序列名称
current_val 当前编号
increment_val 步长

CREATE TABLE `sys_sequence` (`seq_name` varchar(50) NOT NULL COMMENT '序列名称',`current_val` int(11) NOT NULL COMMENT '当前值',`increment_val` int(11) NOT NULL DEFAULT '1' COMMENT '步长',PRIMARY KEY (`seq_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='系统自增序列';

2、mysql新建函数

2.1、执行下面的SQL，testdatabase改为自己使用的库

use testdatabase;
set global log_bin_trust_function_creators=TRUE;

2.2、执行脚本创建函数

CREATE DEFINER = `root`@`%` FUNCTION `currval`(v_seq_name VARCHAR(50))RETURNS int(11)
BEGIN
declare value integer;
set value = 0;
select current_val into value from sys_sequence where seq_name = v_seq_name;
return value;
END;CREATE DEFINER = `root`@`%` FUNCTION `nextval`(v_seq_name VARCHAR(50))RETURNS int(11)
BEGIN
update sys_sequence set current_val = current_val + increment_val where seq_name = v_seq_name;
return currval(v_seq_name);
END;

2.3、创建效果

在这里插入图片描述

在这里插入图片描述
2.4、报错处理
报错1：
报错内容:

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de

解决方法:

在服务器用root账号登录数据库,登录语句mysql -u root -p;执行如下sql,就可以创建了use testdatabase;
set global log_bin_trust_function_creators=TRUE;

报错2：

The user specified as a definer (‘root‘@‘%‘) does not exist

解决方法：查询中执行如下sql,给root 权限

grant all privileges on . to ‘root’@‘%’ identified by “.”;
flush privileges;

报错3:

Access denied; you need (at least one of) the SYSTEM_USER privilege(s)
for this operation

MySQL8.0.16版本中新增了一个system_user帐户类型，我们需要把权限给添加进去，此处的root可以是你当前使用的账户

解决:

grant system_user on . to ‘root’;

三、代码

1、controller

package com.jeesite.modules.onlinesbgl.web;import com.jeesite.modules.onlinesbgl.service.SequenceService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;@Controller
@RequestMapping(value = "f/onlinesbgl")
public class SequenceController{@Autowiredprivate SequenceService sequenceService;/*** 生成序列号*/@RequestMapping(value = "index")@ResponseBodypublic String listData() {return sequenceService.index2("whgz");}}

2、service

package com.jeesite.modules.onlinesbgl.service;import com.jeesite.modules.onlinesbgl.dao.SequenceMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.text.SimpleDateFormat;
import java.util.Date;@Service
public class SequenceService {@Autowiredprivate SequenceMapper sequenceMapper;public synchronized String index(String seq) {return seq.concat("-").concat(timestamp()).concat("-").concat(next(seq.toUpperCase()));}public synchronized String index2(String seq) {return seq.concat("-").concat(timestamp()).concat("-").concat(next(timestamp()));}/***获取序列*/public String next(String seq) {// 如果该序列不存在，那么新增int exists = sequenceMapper.selectSequenceExists(seq);if (exists == 0) {sequenceMapper.insertSequence(seq, 0, 1);}// 如果超出9999, 那么重置Integer val = sequenceMapper.currVal(seq);if (val >= 9999) {sequenceMapper.resetSequence(seq, 0);}String result = "0000" + sequenceMapper.nextVal(seq);return result.substring(result.length() - 5);}/*** 获取时间*/private static String timestamp() {return new SimpleDateFormat("yyyyMMdd").format(new Date());}
}

3、mapper

package com.jeesite.modules.onlinesbgl.dao;import com.jeesite.common.mybatis.annotation.MyBatisDao;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;@MyBatisDao
public interface SequenceMapper {/*** 新建序列* @param name  序列名* @param start 起始值* @param incr  递增值*/@Insert("INSERT INTO sys_sequence VALUES(#{name},${start},${incr})")public void insertSequence(@Param("name") String name, @Param("start") int start, @Param("incr") int incr);/*** 重置序列到某个值* @param name 序列名* @param val  起始值*/@Update("UPDATE sys_sequence SET current_val = ${val} WHERE seq_name = #{name}")public void resetSequence(@Param("name") String name, @Param("val") int val);/*** 获取指定序列是否存在* @param name 序列名* @return 0 不存在 1 存在*/@Select("SELECT COUNT(1) FROM sys_sequence WHERE seq_name = #{name}")public int selectSequenceExists(String name);/*** 获取指定序列下一个值* @param name 序列名* @return 值*/@Select("SELECT nextval(#{name})")public Integer nextVal(String name);/*** 获取指定序列当前值* @param name 序列名* @return 值*/@Select("SELECT currval(#{name})")public Integer currVal(String name);
}