MySQL中获取一个或多个序列

最新推荐文章于 2024-07-03 15:21:14 发布

原创最新推荐文章于 2024-07-03 15:21:14 发布 · 2.4k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #sql

工作杂记专栏收录该内容

9 篇文章

订阅专栏

本文介绍了如何在MySQL中通过创建表和函数模拟Oracle序列功能，以解决多线程环境下流水号可能重复的问题。详细阐述了创建序列表、获取当前值、获取下一个值和更新序列值的SQL脚本，并展示了对应的Java接口及其实现，用于在应用程序中调用这些序列。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、前言

项目应用中，曾有以下一个场景：
接口中要求发送一个int类型的流水号，由于多线程模式，如果用时间戳，可能会有重复的情况(当然概率很小)。
所以想到了利用一个独立的自增的sequence来解决该问题。
当前数据库为：mysql
由于mysql和oracle不太一样，不支持直接的sequence，所以需要创建一张table来模拟sequence的功能，

二、SQL脚本

-- 创建 t_sequence 序列表
DROP TABLE IF EXISTS t_sequence; 
CREATE TABLE t_sequence ( 
     name VARCHAR(50) NOT NULL COMMENT '序列名',
     current_value INT NOT NULL COMMENT '当前值', 
     increment INT NOT NULL DEFAULT 1 COMMENT '自增幅度', 
     PRIMARY KEY (name) 
) ENGINE=InnoDB COMMENT '序列表'; 

-- 创建函数:获取当前值
DROP FUNCTION IF EXISTS seq_currval; 
DELIMITER $ 
CREATE FUNCTION seq_currval (seq_name VARCHAR(50)) 
     RETURNS INTEGER
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT ''
BEGIN
     DECLARE value INTEGER; 
     SET value = 0; 
     SELECT current_value INTO value 
          FROM t_sequence
          WHERE name = seq_name; 
     RETURN value; 
END
$ 
DELIMITER ; 

-- 创建函数:获取下一个值
DROP FUNCTION IF EXISTS seq_nextval; 
DELIMITER $ 
CREATE FUNCTION seq_nextval (seq_name VARCHAR(50)) 
     RETURNS INTEGER
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT ''
BEGIN
     UPDATE t_sequence
          SET current_value = current_value + increment 
          WHERE name = seq_name; 
     RETURN seq_currval(seq_name); 
END
$ 
DELIMITER ; 

-- 创建函数:获取下多个值
DROP FUNCTION IF EXISTS seq_multival; 
DELIMITER $ 
CREATE FUNCTION seq_multival (seq_name VARCHAR(50),step int) 
     RETURNS INTEGER
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT ''
BEGIN
     UPDATE t_sequence
          SET current_value = current_value + step 
          WHERE name = seq_name; 
     RETURN seq_currval(seq_name); 
END
$ 
DELIMITER ; 

-- 创建函数:更新当前值
DROP FUNCTION IF EXISTS seq_setval; 
DELIMITER $ 
CREATE FUNCTION seq_setval (seq_name VARCHAR(50), value INTEGER) 
     RETURNS INTEGER
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT ''
BEGIN
     UPDATE t_sequence
          SET current_value = value 
          WHERE name = seq_name; 
     RETURN seq_currval(seq_name); 
END
$ 
DELIMITER ;

三、通过java代码调用

接口类：

import com.deewin.aftermarket.admin.pojo.Sequence;
import com.baomidou.mybatisplus.extension.service.IService;

/**
 * <p>
 * 序列表 服务类
 * </p>
 *
 * @author bitaotao
 * @since 2022-09-02
 */
public interface ISequenceService extends IService<Sequence> {
    /**
     * 获取下一个序列值
     * @param seqName 序列名
     * @return
     */
    Integer getNextSeqVal(String seqName);

    /**
     * 获取下多个序列值
     * @param seqName 序列名
     * @param step 步长
     * @return
     */
    Integer getMultiSeqVal(String seqName,Integer step);

    /**
     * 设置序列值
     * @param seqName 序列名
     * @param initVal 初始值
     * @return
     */
    Integer setSeqVal(String seqName,Integer initVal);
}

接口实现类：

import com.deewin.aftermarket.admin.pojo.Sequence;
import com.deewin.aftermarket.admin.mapper.SequenceMapper;
import com.deewin.aftermarket.admin.service.ISequenceService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;

/**
 * <p>
 * 序列表 服务实现类
 * </p>
 *
 * @author bitaotao
 * @since 2022-09-02
 */
@Service
public class SequenceServiceImpl extends ServiceImpl<SequenceMapper, Sequence> implements ISequenceService {

    /**
     * 获取下一个序列值
     * @param seqName 序列名
     * @return
     */
    @Override
    public Integer getNextSeqVal(String seqName) {
        Integer val = this.baseMapper.getNextSeqVal(seqName);
        return val;
    }

    /**
     * 获取下多个序列值
     * @param seqName 序列名
     * @param step 步长
     * @return
     */
    @Override
    public Integer getMultiSeqVal(String seqName, Integer step) {
        Integer val = this.baseMapper.getMultiSeqVal(seqName,step);
        return val;
    }

    /**
     * 设置序列值
     * @param seqName 序列名
     * @return
     */
    @Override
    public Integer setSeqVal(String seqName,Integer initVal) {
        return this.baseMapper.setSeqVal(seqName,initVal);
    }
}

数据访问接口类

import com.deewin.aftermarket.admin.pojo.Sequence;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;

/**
 * <p>
 * 序列表 Mapper 接口
 * </p>
 *
 * @author bitaotao
 * @since 2022-09-02
 */
public interface SequenceMapper extends BaseMapper<Sequence> {

    Integer getNextSeqVal(String seqName);

    Integer getMultiSeqVal(String seqName, Integer step);

    Integer setSeqVal(String seqName, Integer initVal);
}

mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.deewin.aftermarket.admin.mapper.SequenceMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.deewin.aftermarket.admin.pojo.Sequence">
        <id column="name" property="name" />
        <result column="current_value" property="currentValue" />
        <result column="increment" property="increment" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        `name`, current_value, `increment`
    </sql>

    <select id="getNextSeqVal" resultType="java.lang.Integer">
        SELECT seq_nextval('${seqName}')
    </select>

    <select id="getMultiSeqVal" resultType="java.lang.Integer">
        SELECT seq_multival('${seqName}',#{step})
    </select>

    <select id="setSeqVal" resultType="java.lang.Integer">
        SELECT seq_setval('${seqName}',#{initVal})
    </select>
</mapper>