三亩地 三亩地SAN MU DI · CODE DIARY
交流投稿
ARTICLE DETAIL

日记详情

真实记录编程学习的某一天，欢迎挑你感兴趣的翻一翻。

首页/学习日记/日记详情

MySQL生成‘年月日+流水号’订单ID？一个自定义函数timeSeq()全搞定（含防并发踩坑经验）

发布时间：2026/8/12 21:21:12 三亩地 编程学习日记
MySQL生成‘年月日+流水号’订单ID？一个自定义函数timeSeq()全搞定（含防并发踩坑经验）

MySQL实战：高并发场景下的日期流水号生成方案与避坑指南

在电商、金融等业务系统中，生成带有日期前缀的流水号（如订单号、交易单号）是常见需求。这类编号既要具备可读性（通过前缀快速识别日期），又要确保全局唯一性。本文将深入探讨MySQL中的高效实现方案，并重点解决高并发环境下的"跳号"和"重复"问题。

1. 核心需求分析与方案选型

业务流水号通常需要满足以下特性：

  • 日期前缀：如20230815表示2023年8月15日
  • 顺序递增：保证同一天内的连续性
  • 固定长度：如8位日期+4位序列组成12位编号
  • 高并发安全：多线程同时生成时不出现重复或跳号

传统方案对比：

方案优点缺点
自增主键+应用层拼接实现简单无法预知ID，分库分表时可能重复
UUID全局唯一无序且过长，不利于业务识别
Redis原子计数器高性能需要维护额外中间件
MySQL序列函数无外部依赖，事务安全需要处理并发控制

推荐方案：基于MySQL自定义函数实现，核心优势在于：

  • 完全依赖数据库事务保证原子性
  • 无需引入额外中间件
  • 支持灵活的格式定制

2. 基础实现：timeSeq函数详解

以下是完整的函数实现代码：

DELIMITER $$ CREATE FUNCTION `timeSeq`( v_seq_name VARCHAR(50), -- 序列名称 v_lpad INT -- 序列号位数 ) RETURNS VARCHAR(50) CHARSET utf8mb4 BEGIN DECLARE seq_val VARCHAR(50); -- 组合日期与序列号 SELECT CONCAT( DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y%m%d'), LPAD(nextval(v_seq_name), v_lpad, '0') ) INTO seq_val FROM dual; RETURN seq_val; END$$ DELIMITER ;

配套的序列管理表结构：

CREATE TABLE `sys_sequence` ( `seq_name` varchar(50) NOT NULL COMMENT '序列名称', `current_val` bigint NOT NULL COMMENT '当前值', `max_val` bigint DEFAULT 9999 COMMENT '最大值', `step` int DEFAULT 1 COMMENT '步长', PRIMARY KEY (`seq_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

基础使用示例：

-- 初始化序列 INSERT INTO sys_sequence(seq_name, current_val) VALUES('order_seq', 0); -- 生成订单号 SELECT timeSeq('order_seq', 4); -- 输出示例：202308150001

3. 高并发场景下的三大陷阱与解决方案

3.1 重复编号问题

现象：当多个事务同时调用nextval()时，可能读取到相同的序列值。

解决方案：使用SELECT...FOR UPDATE实现行锁

DELIMITER $$ CREATE FUNCTION `safe_nextval`(v_seq_name VARCHAR(50)) RETURNS INT BEGIN DECLARE seq_val INT; -- 加锁查询 SELECT current_val INTO seq_val FROM sys_sequence WHERE seq_name = v_seq_name FOR UPDATE; -- 更新序列 UPDATE sys_sequence SET current_val = CASE WHEN current_val + step > max_val THEN 1 ELSE current_val + step END WHERE seq_name = v_seq_name; RETURN seq_val + 1; END$$ DELIMITER ;

3.2 事务回滚导致的跳号

现象：事务获取序列号后回滚，导致序列号不连续。

应对策略：

  1. 业务上接受非连续编号（推荐）
  2. 使用独立事务管理序列：
DELIMITER $$ CREATE FUNCTION `non_transactional_nextval`(v_seq_name VARCHAR(50)) RETURNS INT NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINER BEGIN -- 函数体与之前相同 END$$ DELIMITER ;

3.3 性能瓶颈优化

当QPS超过1000时，序列表可能成为瓶颈。优化方案：

分片策略：按业务拆分不同序列

-- 电商系统示例 INSERT INTO sys_sequence VALUES ('order_seq', 0, 9999, 1), ('payment_seq', 0, 9999, 1), ('refund_seq', 0, 9999, 1);

批量获取：每次获取多个序列号

CREATE FUNCTION `batch_nextval`( v_seq_name VARCHAR(50), v_count INT ) RETURNS VARCHAR(255) BEGIN DECLARE start_val INT; SELECT current_val INTO start_val FROM sys_sequence WHERE seq_name = v_seq_name FOR UPDATE; UPDATE sys_sequence SET current_val = CASE WHEN current_val + v_count > max_val THEN v_count - (max_val - current_val) ELSE current_val + v_count END WHERE seq_name = v_seq_name; RETURN CONCAT(start_val + 1, ',', start_val + v_count); END$$

4. 生产环境最佳实践

4.1 监控与告警配置

关键监控指标：

  • 序列使用率：current_val/max_val
  • 获取耗时：函数执行时间
  • 并发冲突：死锁次数
-- 序列使用率查询 SELECT seq_name, current_val, max_val, CONCAT(ROUND(current_val/max_val*100,2),'%') AS usage_rate FROM sys_sequence;

4.2 灾备方案设计

跨机房部署：在序列表中增加数据中心标识

ALTER TABLE sys_sequence ADD COLUMN dc_id VARCHAR(10) DEFAULT 'dc1'; CREATE FUNCTION `distributed_timeSeq`( v_seq_name VARCHAR(50), v_lpad INT, v_dc_id VARCHAR(10) ) RETURNS VARCHAR(50) BEGIN DECLARE seq_val VARCHAR(50); SELECT CONCAT( DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y%m%d'), v_dc_id, LPAD(nextval(v_seq_name), v_lpad, '0') ) INTO seq_val FROM dual; RETURN seq_val; END$$

4.3 分库分表适配方案

在分库场景下，建议采用以下ID结构：

[4位库标识][8位日期][4位序列]

实现示例：

CREATE FUNCTION `sharding_timeSeq`( v_seq_name VARCHAR(50), v_lpad INT, v_shard_id VARCHAR(4) ) RETURNS VARCHAR(50) BEGIN DECLARE seq_val VARCHAR(50); SELECT CONCAT( v_shard_id, DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y%m%d'), LPAD(nextval(v_seq_name), v_lpad, '0') ) INTO seq_val FROM dual; RETURN seq_val; END$$

5. 扩展应用场景

5.1 多模式序列生成

支持不同日期格式和序列组合：

CREATE FUNCTION `flex_timeSeq`( v_seq_name VARCHAR(50), v_format VARCHAR(20), -- 如'%Y%m%d'、'%Y%m'等 v_lpad INT, v_prefix VARCHAR(10) DEFAULT '' ) RETURNS VARCHAR(50) BEGIN DECLARE seq_val VARCHAR(50); SELECT CONCAT( v_prefix, DATE_FORMAT(CURRENT_TIMESTAMP(), v_format), LPAD(nextval(v_seq_name), v_lpad, '0') ) INTO seq_val FROM dual; RETURN seq_val; END$$

5.2 与JPA集成示例

Spring Data JPA调用示例：

public interface SequenceRepository extends JpaRepository<SysSequence, String> { @Query(value = "SELECT timeSeq(:seqName, :lpad) FROM dual", nativeQuery = true) String generateTimeSeq(@Param("seqName") String seqName, @Param("lpad") int lpad); @Transactional @Modifying @Query(value = "UPDATE sys_sequence SET current_val = " + "CASE WHEN current_val + :step > max_val THEN 1 " + "ELSE current_val + :step END " + "WHERE seq_name = :seqName", nativeQuery = true) int updateSequence(@Param("seqName") String seqName, @Param("step") int step); }

5.3 历史数据迁移方案

当需要修改编号规则时，兼容旧数据的处理方式：

  1. 在序列表中增加规则版本字段
  2. 新函数根据版本选择不同格式
  3. 旧数据通过前缀区分
ALTER TABLE sys_sequence ADD COLUMN format_ver VARCHAR(10) DEFAULT 'v1'; CREATE FUNCTION `compatible_timeSeq`( v_seq_name VARCHAR(50) ) RETURNS VARCHAR(50) BEGIN DECLARE seq_val VARCHAR(50); DECLARE v_format VARCHAR(20); -- 根据版本获取格式 SELECT CASE format_ver WHEN 'v1' THEN '%Y%m%d' WHEN 'v2' THEN '%y%m%d' ELSE '%Y%m%d%H%i%s' END INTO v_format FROM sys_sequence WHERE seq_name = v_seq_name; -- 生成序列 SELECT CONCAT( DATE_FORMAT(CURRENT_TIMESTAMP(), v_format), LPAD(nextval(v_seq_name), 4, '0') ) INTO seq_val FROM dual; RETURN seq_val; END$$
编程学习 学习日记 实战经验
← 返回列表