[MySql]函数

admin 管理员组

文章数量: 887007

[MySql]函数

一.前言

在项目中经常会有需求,要给订单或合同生成有特定规则的编号,比如自增.
这里用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@% FUNCTION currval(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@% FUNCTION nextval(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

解决方法:查询中执行如下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’;

在服务器登录数据库操作更好,登录语句

mysql -u root -p

五.java代码

1.SequenceMapper

package com.xxmon.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.xxmon.seq.service;public interface ISequenceService {public String next(String seq);}

3.SequenceServiceImpl

package com.xxmon.seq.service.impl;import com.agriculturemon.seq.mapper.SequenceMapper;
import com.agriculturemon.seq.service.ISequenceService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;@Service("sequenceService")
public class SequenceServiceImpl implements ISequenceService {@Autowiredprivate SequenceMapper sequenceMapper;@Overridepublic 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.agriculturemon.utils;import com.agriculturemon.seq.service.ISequenceService;
import com.agriculturemon.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

本文标签： MySQL函数