MySQL使用Sequence创建唯一主键的实现示例,MySQL中如何为查询的数据添加自增序号、顺序呢

MySQL使用Sequence创建唯一主键的实现示例

 更新时间：2024年05月26日 11:03:41   作者：Holy_Java  

 

Sequence提供了更多的灵活性,本文主要介绍了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）创建表

1 2 3 4 5 6 7

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函数

1 2 3 4 5 6 7 8 9

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函数

1 2 3 4 5 6 7 8 9

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函数

1 2 3 4 5 6 7 8

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

1	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）实体类

1 2 3 4 5 6 7 8 9

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

3.2）repository接口

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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层测试

1 2 3 4 5 6 7 8 9 10 11 12 13

@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);     } }

MySQL中如何为查询的数据添加自增序号、顺序呢

背景介绍
很多时候我们在使用mysql查询数据的时候都会遇到一个问题，就是查询出来了一堆数据，但是查询的数据的表并没有序号，然而部分数据库显示工具是有外带序号显示，但是这种序号不是由sql产生的，而是工具的list容器生成的，那么这个时候应该怎么解决呢？

解决方案
写法一、

set @rownum = 0;
SELECT @rownum := @rownum +1 AS rownum ,s.account  FROM s_user s ORDER BY  s.reg_time DESC ;

方法1的写法就是声明了一个变量在外部，然后每行数据都为它自增。

写法二

SELECT
		account,
		(@i:=@i+1) AS rows
	FROM
		s_user,
		(SELECT @i := 0) AS it
	ORDER BY
		account DESC

写法二的和写法一实现的效果是一样的，只是sql有一点区别，其次在调用过程需要注意一个事项，就是在from 后面需要为变量归零重置（(SELECT @i := 0) AS it），否则可能会出现累计效果。

转载于:https://www.cnblogs.com/david97/p/9413995.html

• 创建序列表

CREATE TABLE `sequence` (
  `name` varchar(50) NOT NULL,
  `current_value` bigint(30) NOT NULL,
  `increment` int(11) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='序列表';

• 创建函数 查询当前序列名的序列值

CREATE DEFINER=`root`@`%` FUNCTION `shop-product`.`currval`(seq_name VARCHAR(50)) RETURNS bigint(30)
    DETERMINISTIC
BEGIN
 DECLARE vCurrent BIGINT(30);  
 SET vCurrent = 0;  
 SELECT current_value INTO vCurrent FROM sequence WHERE NAME = seq_name;   
 RETURN vCurrent; 
END

• 创建函数 查询当前序列名下一个序列值

CREATE DEFINER=`root`@`%` FUNCTION `shop-product`.`nextval`(seq_name VARCHAR(50)) RETURNS bigint(30)
    DETERMINISTIC
BEGIN  
UPDATE sequence SET current_value = current_value + increment WHERE NAME = seq_name;  
RETURN currval(seq_name);  
END

• 创建函数 更新当前序列名的序列值

CREATE DEFINER=`root`@`%` FUNCTION `shop-product`.`setval`(seq_name VARCHAR(50),value BIGINT) RETURNS bigint(30)
    DETERMINISTIC
BEGIN
	UPDATE sequence  SET current_value = value WHERE name = seq_name; 
	RETURN currval(seq_name); 
END

• 序列表插入数据

INSERT INTO `sequence`
(name, current_value, `increment`)
VALUES('test', 1, 1);

使用方法

SELECT SETVAL(‘test’, 10); --设置指定sequence的初始值
SELECT CURRVAL(‘test’); --查询指定sequence的当前值
SELECT NEXTVAL(‘test’); --查询指定sequence的下一个值

 

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';

 

SELECT c.relname AS table_name,
    s.relname AS sequence_name
   FROM ((pg_depend d
     JOIN pg_class c ON ((d.refobjid = c.oid)))
     JOIN pg_class s ON ((d.objid = s.oid)))
  WHERE ((d.refclassid = ('pg_class'::regclass)::oid) AND (d.classid = ('pg_class'::regclass)::oid) AND (c.relkind = 'r'::"char") AND (s.relkind = 'S'::"char"));