mysql实现序列,用于表数据唯一固定主键

本文介绍了在MySQL中如何实现类似Oracle的序列功能,以保证在插入数据时生成唯一的、固定的主键,特别是针对同一产品或指标的情况。文中通过代码示例和效果展示详细阐述了这一解决方案。

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

一、业务背景

1、oracle有自增序列,mysql有auto_increment,但是后者是在插入数据时实现自增,数据每增加一行,就加1
2、需求:同一个产品\指标,同一个id,且同一个产品\指标的id一旦生成,就固定不变

二、代码

-- 1、创建表sequence:
DROP TABLE
    IF EXISTS sequence;
CREATE TABLE
    sequence
    (
        name VARCHAR(50) NOT NULL,
        current_v
### MySQL实现时间序列作为主键MySQL中,可以利用`TIMESTAMP`或`DATETIME`类型的字段来创建基于时间戳的时间序列主键。为了确保该字段能够唯一标识每条记录并遵循良好的设计实践,通常会结合其他机制一起使用。 #### 方法一:组合时间戳与其他唯一标识符 一种常见的方式是将当前时间戳与另一个能保证唯一的部分相结合形成复合主键。例如: ```sql CREATE TABLE example_table ( ts DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), unique_part VARCHAR(36) NOT NULL, PRIMARY KEY (ts, unique_part) ); ``` 这里采用了精度达到微秒级别的 `DATETIME(6)` 类型[^1],并且定义了一个辅助列用于增强唯一性。对于 `unique_part` 可以考虑采用UUID或其他业务层面可区分的信息填充。 #### 方法二:自动生成带有前缀的时间戳字符串 另一种思路是在应用层生成包含特定格式化日期/时间信息在内的字符串形式的主键值,在插入之前先计算好完整的ID再传入数据库。比如下面这个例子展示了如何构建形如 "YYYYMMDDHHMISS-随机数" 的主键值: ```sql SET @prefix = DATE_FORMAT(NOW(),'%Y%m%d%H%i%s'); INSERT INTO another_example(id,value) VALUES(CONCAT(@prefix,FLOOR(RAND() * 90000 + 10000)),'some value'); ``` 这种方法虽然简单易懂,但在高并发环境下可能会遇到重复的风险,因此建议配合锁或者其他同步措施加以保护[^2]。 #### 方法三:借助触发器和存储过程动态分配带有序号的时间戳 更复杂但也更为可靠的做法是通过编写触发器或者调用存储过程来自动生成具有连续序号特征的时间序列主键。这涉及到额外维护一张专门用来跟踪各时间段内已发放的最大编号的小,并据此派生新的主键值。具体操作如下所示: 首先建立一个追踪最大序数值的小: ```sql CREATE TABLE seq_tracker( datepart DATE NOT NULL PRIMARY KEY, max_seq INT UNSIGNED NOT NULL DEFAULT 0 ); ``` 接着为实际的目标设定结构: ```sql CREATE TABLE target_with_timestamp_id( id CHAR(20), -- 假设长度足够容纳最长可能的结果串 content TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY(id) ); ``` 最后提供一段负责生产新id的函数以及相应的before insert trigger: ```sql DELIMITER $$ CREATE FUNCTION generate_time_based_id() RETURNS CHAR(20) BEGIN DECLARE newId CHAR(20); DECLARE curDatePart DATE; DECLARE nextSeqNo INT; SELECT CURDATE() INTO curDatePart ; LOCK TABLES seq_tracker WRITE ; IF EXISTS(SELECT * FROM seq_tracker WHERE datepart=curDatePart ) THEN UPDATE seq_tracker SET max_seq=max_seq+1 WHERE datepart=curDatePart ; ELSE INSERT INTO seq_tracker(datepart,max_seq)VALUES(curDatePart ,1); END IF; SELECT CONCAT(DATE_FORMAT(CURDATE(),"%Y%m%d"),LPAD(max_seq,8,"0")) AS generated_id INTO newId FROM seq_tracker WHERE datepart=curDatePart LIMIT 1; UNLOCK TABLES; RETURN newId; END$$ DELIMITER ; DROP TRIGGER IF EXISTS before_target_insert; CREATE TRIGGER before_target_insert BEFORE INSERT ON target_with_timestamp_id FOR EACH ROW BEGIN SET NEW.id = generate_time_based_id(); END; ``` 上述代码片段实现了每天重置计数的基础之上按顺序递增的功能,从而使得最终形成的主键既包含了当天的具体时刻又具备全局范围内的唯一性质[^3]。
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值