MySQL存储过程结合MyBatis实现高并发流水号生成

实现目标与架构逻辑

本方案的核心目标是：生成不重复流水号、支持自定义周期重置（如每日从1开始）、以及保证高并发下的线程安全。

技术选型如下：

• 数据存储：MySQL InnoDB 引擎，利用其事务隔离特性。
• 并发控制：使用 SELECT ... FOR UPDATE 对流水记录行进行锁定，阻塞其他事务读取直到当前事务提交。
• ORM框架：MyBatis，负责将Java Map对象映射为存储过程的入参/出参。

第一步：准备流水号数据表结构

首先需要创建一张专门的表来维护流水号状态。该表的主键由业务标识、机构ID和周期格式共同组成，以确保不同维度的流水互不干扰。

建表脚本：

DROP TABLE IF EXISTS `sys_serial_number`;
CREATE TABLE `sys_serial_number` (
  `table_key` varchar(100) NOT NULL COMMENT '主键（建议用表名）',
  `org_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '分公司ID',
  `number` bigint(20) NOT NULL DEFAULT '1' COMMENT '流水号（存储过程控制递增，获取完后+1）',
  `period_date_format` varchar(20) NOT NULL COMMENT '流水号生成周期日期格式',
  `datestr` varchar(20) DEFAULT NULL COMMENT '流水号日期值',
  `description` varchar(100) DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`table_key`,`org_id`,`period_date_format`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='流水号生成表';

第二步：编写原子化存储过程

存储过程是保证“查询-计算-更新”操作原子性的关键。我们需要处理三种情况：新业务首次申请、跨周期重置、正常递增。

存储过程代码：

DROP PROCEDURE IF EXISTS `generate_serial_number_by_date`;
CREATE PROCEDURE `generate_serial_number_by_date`(
    IN param_key varchar(100),
    IN param_org_id bigint, 
    IN param_period_date_format varchar(20), 
      OUT result bigint,
    OUT current_datestr varchar(20))
begin 
    declare old_datestr varchar(20);
    
    START TRANSACTION; 

    -- 1. 计算当前周期的时间字符串
    if param_period_date_format='infinite' then 
        set current_datestr = '00000000';
    else
        set current_datestr = DATE_FORMAT(NOW(), param_period_date_format);
    end if;
    
    -- 2. 关键步骤：加排他锁读取当前行
    select number, datestr 
    from sys_serial_number
    where table_key = param_key 
            and org_id = param_org_id 
            and period_date_format = param_period_date_format
            into result, old_datestr
            for update;

    -- 3. 判断是否为新数据，如果是则初始化为1
    IF result is null then
        set result = 1;
        insert into sys_serial_number(table_key, org_id, period_date_format, datestr, number, description) 
            values(param_key, param_org_id, param_period_date_format, current_datestr, 1, 'add by procedure');
    
    -- 4. 判断是否跨周期，如果跨周期则重置为1
    elseif old_datestr != current_datestr then
        set result = 1;
        update sys_serial_number 
                set number = 1, 
                        datestr = current_datestr 
        where table_key = param_key 
                and org_id = param_org_id 
                and period_date_format = param_period_date_format;
    end if;
    
    -- 5. 无论是否重置，最后一步都让序号+1
    update sys_serial_number set number = number + 1 
        where table_key = param_key 
            and org_id = param_org_id 
            and period_date_format = param_period_date_format;
    commit;
end

逻辑解析： for update 在此处起到了悲观锁的作用。当两个线程同时尝试获取同一个Key的流水号时，后到达的线程会因无法锁定该行而等待，直到前一个事务执行完 commit。这从根本上避免了并发冲突导致的序号重复或错乱。

第三步：配置MyBatis映射接口

在MyBatis的Mapper XML文件中，需要声明一个调用存储过程的SQL语句。注意指定 statementType="CALLABLE" 并正确设置参数的输入输出模式。

Mapper配置示例：

<select id="generateSerialNumber" parameterType="java.util.HashMap" statementType="CALLABLE"
<![CDATA[
           {
           call generate_serial_number_by_date (
            #{param_key,mode=IN,jdbcType=VARCHAR},
            #{param_org_id,mode=IN,jdbcType=BIGINT},
            #{result,mode=OUT,jdbcType=BIGINT}
            )
           }
       ]]>
  </select>

第四步：Java端调用与并发测试

在Service层接收一个Map作为参数，传入MyBatis。由于使用了Map，输出的 result 会自动回填到Map中。

Service调用代码：

@Override
public Map<String, Object> generateSerialNumber(Map<String, Object> param) {
    sysSerialNumberMapper.generateSerialNumber(param);
    return param;
}

为了验证其高并发安全性，我们可以模拟三个线程并行请求同一组参数：

final Map<String, Object> param = new HashMap<String, Object>();
param.put("param_key","contract");
param.put("param_orgId", 84);
// 启动线程1
new Thread(new Runnable() {
    @Override
    public void run() {
        for(int i =0; i<100; i++) {
            Map<String, Object> map = serialNumberProvider.generateSerialNumber(param);
            System.out.println("thread-1：" + map.get("result"));
        }
    }
}).start();
// 类似地启动 thread-2, thread-3...

运行结果中，每个线程拿到的100个号码必然是连续且无重复的（例如线程1拿到1-100，线程2拿到101-200），证明了存储过程层面的加锁有效性。

常见问题排查

在集成过程中，常会遇到如下报错：

java.sql.SQLException: Parameter number 4 is not an OUT parameter

出现此错误通常是因为MyBatis调用存储过程时参数数量与存储过程定义的参数个数不匹配，或者输出参数模式未正确声明。请检查以下两点：

1. 存储过程定义一致性：确认XML中的 call 语句参数数量与实际创建的存储过程签名完全一致。
2. 驱动与模式配置：确保调用方（Java）传递的Map中包含了所有必要的占位符，并且 #{param.name, mode=OUT} 语法使用无误。