一.前言
在项目中经常会有需求,要给订单或合同生成有特定规则的编号,比如自增.
这里用mysql函数来实现.
规则:固定大写字母DT +当前时间戳+五位编号[00001-99999]
生成编号:DT-20220421010101-00001
下一次获取编号:DT-20220421010101-00002
二.MySQL新建一张表sys_sequence
seq_name 序列名称
current_val 当前编号
increment_val 步长
结构sql如下:
DROP TABLE IF EXISTS sys_sequence; CREATE TABLE sys_sequence (
seq_name VARCHAR ( 50 ) CHARACTER SET utf8 COLLATE
utf8_general_ci NOT NULL COMMENT ‘序列名称’, current_val INT ( 0 ) NOT
NULL COMMENT ‘当前值’, increment_val INT ( 0 ) NOT NULL DEFAULT 1
COMMENT ‘步长’, PRIMARY KEY ( seq_name ) USING BTREE ) ENGINE =
INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT =
‘系统自增序列’ ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
表中数据如图
步长为1的意思是下次编号会比当前编号递增1
三.在MySQL新建函数
这里需要新建两个函数,一个为currval,一个为nextval
1. 在navicat中,函数部分,右键选新建函数
2. 填函数名,勾选函数,点击完成
3. 新建函数之后需要补全函数定义
currval函数完整定义为:
CREATE DEFINER=
root
@%
FUNCTIONcurrval
(v_seq_name VARCHAR(50)) RETURNS int
BEGIN
declare value integer;
set value = 0;
select current_val into value from sys_sequence where seq_name = v_seq_name;
return value;
END
nextval函数完整定义为:
CREATE DEFINER=
root
@%
FUNCTIONnextval
(v_seq_name VARCHAR(50)) RETURNS int
BEGIN
update sys_sequence set current_val = current_val + increment_val where seq_name = v_seq_name;
return currval(v_seq_name);
END
四. 创建函数时的常见错误
4.1 报错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;
4.2.报错2:
- The user specified as a definer (‘root‘@‘%‘) does not exist
解决方法:查询中执行如下sql,给root 权限
grant all privileges on . to ‘root’@‘%’ identified by “.”;
flush privileges;
4.3报错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’;
五.java代码
1.SequenceMapper
package com.xx.common.seq.mapper;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;
@Repository
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);
}
2.ISequenceService
package com.xx.common.seq.service;
public interface ISequenceService {
public String next(String seq);
}
3.SequenceServiceImpl
package com.xx.common.seq.service.impl;
import com.agriculture.common.seq.mapper.SequenceMapper;
import com.agriculture.common.seq.service.ISequenceService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service("sequenceService")
public class SequenceServiceImpl implements ISequenceService {
@Autowired
private SequenceMapper sequenceMapper;
@Override
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);
}
}
六.添加工具类
package com.agriculture.common.utils;
import com.agriculture.common.seq.service.ISequenceService;
import com.agriculture.common.utils.spring.SpringUtils;
import java.text.SimpleDateFormat;
import java.util.Date;
public abstract class GenerateSerialUtilscOPR {
private GenerateSerialUtilscOPR() {
}
private static volatile ISequenceService sequenceService;
//订单编号
public synchronized static String index(String seq) {
return seq.concat("-").concat(timestamp()) .concat("-"). concat(getService().next(seq.toUpperCase()));
}
/**
* 获取Service
*
* @return service
*/
private static ISequenceService getService() {
if (sequenceService == null){ sequenceService = SpringUtils.getBean(ISequenceService.class);}
return sequenceService;
}
/**
* 获取时间
*
* @return timestamp
*/
private static String timestamp() {
return new SimpleDateFormat("yyyyMMdd").format(new Date());
}
}
七.完成啦测试方法
public static void main(String[] args) {
System.out.println(index("DT"));
}
测试结果为:DT-20220421020101-00002