在MySQL中创建实现自增的序列(Sequence)

本文介绍如何在MySQL中通过创建特殊表及函数模拟Oracle的sequence功能,实现自动递增ID的生成。详细步骤包括创建Sequence管理表、取当前值与下一个值的函数等。

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

由于mysql和oracle不太一样,不支持直接的sequence,所以需要创建一张table来模拟sequence的功能,理由sql语句如下:
第一步:创建--Sequence 管理表

DROP TABLE IF EXISTS sequence;
CREATE TABLE sequence (
     name VARCHAR(50) NOT NULL,
     current_value INT NOT NULL,
     increment INT NOT NULL DEFAULT 1,
     PRIMARY KEY (name)
) ENGINE=InnoDB;


?

 
第二步:创建--取当前值的函数

DROP FUNCTION IF EXISTS currval;
DELIMITER $
CREATE FUNCTION currval (seq_name VARCHAR(50))
     RETURNS INTEGER
     LANGUAGE SQL
     DETERMINISTIC
     CONTAINS SQL
     SQL SECURITY DEFINER
     COMMENT ''
BEGIN
     DECLARE value INTEGER;
     SET value = 0;
     SELECT current_value INTO value
          FROM sequence
          WHERE name = seq_name;
     RETURN value;
END
$
DELIMITER ;


 
第三步:创建--取下一个值的函数

DROP FUNCTION IF EXISTS nextval;
DELIMITER $
CREATE FUNCTION nextval (seq_name VARCHAR(50))
     RETURNS INTEGER
     LANGUAGE SQL
     DETERMINISTIC
     CONTAINS SQL
     SQL SECURITY DEFINER
     COMMENT ''
BEGIN
     UPDATE sequence
          SET current_value = current_value + increment
          WHERE name = seq_name;
     RETURN currval(seq_name);
END
$
DELIMITER ;


第四步:创建--更新当前值的函数

DROP FUNCTION IF EXISTS setval;
DELIMITER $
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER)
     RETURNS INTEGER
     LANGUAGE SQL
     DETERMINISTIC
     CONTAINS SQL
     SQL SECURITY DEFINER
     COMMENT ''
BEGIN
     UPDATE sequence
          SET current_value = value
          WHERE name = seq_name;
     RETURN currval(seq_name);
END
$
DELIMITER ;


 
第五步:测试函数功能
当上述四步完成后,可以用以下数据设置需要创建的sequence名称以及设置初始值和获取当前值和下一个值。

  • INSERT INTO sequence VALUES ('TestSeq', 0, 1);----添加一个sequence名称和初始值,以及自增幅度
  • SELECT SETVAL('TestSeq', 10);---设置指定sequence的初始值
  • SELECT CURRVAL('TestSeq');--查询指定sequence的当前值
  • SELECT NEXTVAL('TestSeq');--查询指定sequence的下一个值


 
在java代码中,可直接创建sql语句查询下一个值,这样就解决了流水号唯一的问题。
贴出部分代码(已测试通过)

public void testGetSequence() {
  Connection conn = JDBCUtils.getConnection(url, userName, password);
  String sql = "SELECT CURRVAL('TestSeq');";
  PreparedStatement ptmt = null;
  ResultSet rs = null;
  try {
    ptmt = conn.prepareStatement(sql);
    rs = ptmt.executeQuery();
    int count = 0;
    while (rs.next()) {
      count = rs.getInt(1);
    }
    System.out.println(count);
  } catch (SQLException e) {
    e.printStackTrace();
  } finally {
    JDBCUtils.close(rs, ptmt, conn);
  }
}


ps:在应用中,还有一种用java代码去实现模拟自增sequence的方式,具体思路是创建一张存放sequence的table,然后通过java调用sql语句去查询和修改这个table中指定sequence名称的值,这种方式请加上synchronized。具体代码这里就不上传了,因为实现了,未去测试过。


在 oracle 中, sequence 提供多表多字段可共用一个不重复值。 Mysql 中存在自增列,基本可以满足 PK 的要求。但自增列存在限制:

a. 只能用于表中的一个字段,一张不能同时存在两个以上的自增列 ;

b. 自增列必须被定义为 key ( PK 或 FK ) ;

c. 自增列不能被多个表共用 ;

d. 当 insert 语句不包括自增字段或将其值设置为 NULL 时,该值会自动填上。

在不要求字段顺序递增的情况下,可以在 Mysql 中实现序列,再来看下面一个例子:

DROP TABLE IF EXISTS sequence;
  
-- 建sequence表,指定seq列为无符号大整型,可支持无符号值:0(default)到18446744073709551615(0到2^64–1)。
CREATE TABLE sequence (
   name       VARCHAR(50) NOT NULL,
     current_value   BIGINT UNSIGNED NOT NULL DEFAULT 0,
     increment     INT NOT NULL DEFAULT 1,
     PRIMARY KEY (name)  -- 不允许重复seq的存在。
) ENGINE=InnoDB;
  
  
DELIMITER /
  
DROP FUNCTION IF EXISTS currval /
  
CREATE FUNCTION currval(seq_name VARCHAR(50))
RETURNS BIGINT
BEGIN
     DECLARE value BIGINT;
     SELECT current_value INTO value
     FROM sequence
     WHERE upper(name) = upper(seq_name); -- 大小写不区分.
     RETURN value;
END;
/
  
DELIMITER ;
  
  
DELIMITER /
  
DROP FUNCTION IF EXISTS nextval /
  
CREATE FUNCTION nextval (seq_name VARCHAR(50))
RETURNS BIGINT
BEGIN
     DECLARE value BIGINT;
     UPDATE sequence
     SET current_value = current_value + increment
     WHERE upper(name) = upper(seq_name);
     RETURN currval(seq_name);
END;
/
  
DELIMITER ;
  
DELIMITER /
  
DROP FUNCTION IF EXISTS setval /
  
CREATE FUNCTION setval (seq_name VARCHAR(50), value BIGINT)
RETURNS BIGINT
BEGIN
     UPDATE sequence
     SET current_value = value
     WHERE upper(name) = upper(seq_name);
     RETURN currval(seq_name);
END;
/
  
DELIMITER ;


 在 SQL 中使用序列:
创建序列,往sequence表插入值即可:
mysql> insert into sequence set name='myseq';


查看当前已建序列:
mysql> select * from sequence;

+-------+---------------+-----------+
| name | current_value | increment |
+-------+---------------+-----------+
| myseq |       0 |     1 |
+-------+---------------+-----------+
1 row in set (0.00 sec)

获得序列的下一个值,第一次使用,因此值为1:

mysql> select nextval('myseq');

+------------------+
| nextval('myseq') |
+------------------+
|        1 |
+------------------+
1 row in set (0.00 sec)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28282660/viewspace-2061242/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/28282660/viewspace-2061242/

### MySQL存储引擎中的自序列实现与用法 #### 1. 自序列的基础概念 在MySQL中,`AUTO_INCREMENT` 是一种用于生成唯一标识符的功能。它通常被用来作为主键的一部分,确保每条记录都有唯一的编号[^1]。 当定义一个表时,可以通过设置某一列的属性为 `AUTO_INCREMENT` 来启用该功能。例如: ```sql CREATE TABLE example ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) ); ``` 上述代码会创建一张名为 `example` 的表,其中 `id` 列具有自动长特性。 #### 2. 自序列的工作机制 MySQL 中的自 ID 并不是逐个分配给新插入的数据行,而是在内部进行了优化处理。具体来说,在同一个 SQL 执行过程中多次请求新的自值时,后续请求的数量将是之前数量的两倍[^4]。这种设计可以减少锁竞争并提高性能。 然而需要注意的是,如果数据库重启或者遇到某些特殊情况(比如删除未提交事务),可能会导致间隙出现或重复值丢失等问题[^2]。 #### 3. 使用存储过程管理更复杂的自逻辑 对于一些特殊需求场景下可能需要更加灵活可控的方式来自动生成唯一ID,则可以通过编写用户定义函数(User Defined Function) 或者存储过程来完成这一目标[^3] 。下面展示了一个简单的例子: ```sql DELIMITER $$ CREATE PROCEDURE get_increment_id( IN idname_in VARCHAR(20), IN small_in BIGINT, OUT id_out BIGINT ) BEGIN DECLARE current_value BIGINT; SELECT IFNULL(MAX(id_column), 0)+1 INTO @current_value FROM sequence_table WHERE seq_name=idname_in FOR UPDATE; INSERT INTO sequence_table (seq_name,id_column) VALUES (idname_in,@current_value); SET id_out=@current_value ; END$$ DELIMITER ; ``` 此脚本允许我们根据不同名称的空间获取独立递数值,并且通过显式的锁定(`FOR UPDATE`)保证了多线程环境下的安全性。 #### 4. 跨分片/分布式系统的解决方案考虑 随着业务规模扩大至多个物理节点甚至云服务提供商之间分布部署的情况下,传统的单一服务器上的auto-increment已经无法满足全局一致性要求。此时可采用UUID或者其他基于时间戳加随机因子组合而成的大整型来做为主键替代方案之一;另外还有Snowflake算法也是常用的选择之一。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值