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

一、前言

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、创建成功可以进行查询

INSERT INTO sys_sequence VALUES('20230604',0,1);
SELECT currval('20230604');
SELECT nextval('20230604');

2.5、报错处理
报错1：
报错内容:

1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

解决方法:

在服务器用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 权限

1、确认用户是否存在：检查MySQL的用户账户是否存在。可以使用以下命令：

SELECT user, host FROM mysql.user;

查看是否有'root'@'%'这样的用户。

2、如果用户不存在，创建用户或者重新授权：

2.1、MySQL 8.0 以上版本使用下面的命令，因为新版的的mysql版本已经将创建用户和赋予权限的操作分开了，需要分两步操作：
CREATE USER 'root'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

替换'password'为实际的密码。

2.2、MySQL 8.0 以下版本使用下面的命令就可以
grant all privileges on . to ‘root’@‘%’ identified by “.”;
flush privileges;

3、如果用户存在，但主机名不正确，你可能需要创建一个特定的主机用户：

CREATE USER 'root'@'hostname' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'hostname' WITH GRANT OPTION;
FLUSH PRIVILEGES;

替换'hostname'为实际的主机名。
如果是在复制环境中，确保复制用户和权限被正确地复制到从服务器上。
如果以上步骤不适用，可能需要查看相关的权限表，手动修正definer字段。这通常不建议，除非你非常清楚自己在做什么，因为直接操作系统表可能会破坏数据库的一致性和安全性。

报错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{

	@Autowired
	private SequenceService sequenceService;

	/**
	 * 生成序列号
	 */
	@RequestMapping(value = "index")
	@ResponseBody
	public 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 {

    @Autowired
    private 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);
}

四、调用效果