MySQL使用Sequence创建唯一主键

第一章、快速了解Sequence

1.1）是什么？为什么使用

数据库中的Sequence（序列）是一种用于生成唯一数字值的对象。在关系型数据库中，Sequence通常用于生成主键值或其他需要唯一标识的字段值。

Sequence通常具有以下特点：

①生成连续的数字值，每次调用会递增或递减。
②可以定义起始值、增量值、最小值、最大值等属性。
③可以被多个会话共享，确保生成的值是全局唯一的。
④通常用于生成主键值，确保数据库表中的每条记录都有唯一标识。
不同的数据库系统（如Oracle、MySQL、PostgreSQL等）有不同的实现方式和语法规则。

1.2）Sequence和自增主键的区别

生成方式：
Sequence：Sequence是数据库中的一个对象，通过定义序列可以生成一系列唯一的数字值，可以手动控制序列的起始值、增量值、最小值、最大值等属性。
自增主键：自增主键是一种特殊的主键约束，通常与整数类型的字段一起使用。当插入新记录时，数据库会自动为该字段赋予一个唯一的递增值，而无需手动管理。

跨会话性：
Sequence：Sequence对象可以被多个会话共享，确保生成的值在整个数据库中是唯一的，适用于需要全局唯一标识的场景。
自增主键：自增主键是针对单个表的，每个表都有自己的自增主键，生成的值只在该表中唯一，不跨表。

灵活性：
Sequence：Sequence提供了更多的灵活性，可以定义不同的序列属性，如循环、缓存等，适用于需要定制化生成规则的情况。
自增主键：自增主键是一种简单的自动生成方式，适用于简单的唯一标识生成需求。

第二章、在MySQL中使用Sequence

2.1）创建mysql_sequence表

2.1.1）创建表

CREATE TABLE `mysql_sequence` (
  `seq_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '序列名称',
  `seq_desc` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '序列描述',
  `seq_no` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '序列号',
  `max_val` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`seq_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

2.1.2）插入数据

数据如下：

2.2）创建函数

2.2.1）创建nextval函数

DELIMITER $$
CREATE  FUNCTION `nextval`(v_seq_name VARCHAR(50)) RETURNS INT
DETERMINISTIC
BEGIN
    UPDATE mysql_sequence SET seq_no = seq_no + 1 WHERE seq_name = v_seq_name AND seq_no <= max_val;
    UPDATE mysql_sequence SET seq_no = 1 WHERE seq_name = v_seq_name AND seq_no > max_val;
    RETURN currval(v_seq_name);
END$$
DELIMITER ;

2.2.2）创建currval函数

DELIMITER $$
CREATE  FUNCTION currval(v_seq_name VARCHAR(50)) RETURNS INT
BEGIN
    DECLARE seq_val INT;
    SET seq_val = 0;
    SELECT seq_no INTO seq_val FROM mysql_sequence WHERE seq_name = v_seq_name;
    RETURN seq_val;
END$$
DELIMITER ;

2.2.3）创建timeseq函数

DELIMITER $$
CREATE  FUNCTION `timeseq`(v_seq_name VARCHAR(50), v_lpad INT) RETURNS VARCHAR(50) CHARSET utf8mb4 COLLATE utf8mb4_bin
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 ;

2.3）查询语句与结果

输入项为表格的seq_name字段值stud_seq和位数4

SELECT CONCAT(DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y%m%d'), LPAD(nextval('stud_seq'), 4, '0'))  FROM dual;

返回结果为时间戳20240428 拼上 4位数的 0002 这里的0002是数据表seq——no字段1自增后的结果

第三章、SpringDataJpa中调用

3.1）实体类

@Data
@Entity
@Table(name = "mysql_sequence")
public class MysqlSequence {
    @Id
    private String SeqName;
    private String SeqDesc;
    private String SeqNo;
}

3.2）repository接口

import com.icbc.sh.dto.MysqlSimpleSequence;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.data.jpa.repository.Modifying;

public interface SequenceRepo extends JpaRepository<MysqlSimpleSequence, String> {

    // 获取下一个序列值
    @Query(value = "select nextval(:seqName) from dual", nativeQuery = true)
    int nextval(@Param("seqName") String seqName);

    // 获取带填充的时间序列下一个值
    @Query(value = "select timeSeq(:seqName, :lpad) from dual", nativeQuery = true)
    String timeSeqNextVal(@Param("seqName") String seqName, @Param("lpad") int lpad);

    // 重置序列
    @Transactional
    @Modifying
    @Query(value = "update sequence set val = 1 where reset_flag = ?1", nativeQuery = true)
    int resetSeq(String resetFlag);
}

3.3）controller层测试

@RestController
@RequestMapping("/test")
public class TestController {

    @Autowired
    private SequenceRepo sequenceRepo;

    @GetMapping("/testSequence")
    public void testSequence(){
        int sequence = sequenceRepo.nextval("stud_seq");
        log.info("sequence :" + sequence);
    }
}