MySQL 中数据表 id 值 连续 且 自动增长

目录

1.前置知识——事务

2. 前置知识——触发器 

3.前置知识——MySQL中符号@的作用 

4. 回归正题，如何实现将id值连续且自动增长

---

1.前置知识——事务

参考链接：具体事务知识，点击这里详细查看 

 事务隔离级别：

1. 读未提交 read uncommitted
2. 读已提交 read committed
3. 可重复读 repeatable read
4. 串行化 serializable

查看全局系统隔离级别

select @@global.transaction_isolation;

 查看当前系统隔离级别

select @@transaction_isolation;

2. 前置知识——触发器 

参考链接：MySQL 循环知识了解

参考链接：触发器知识，点击了解详情

参考链接：基本MySQL 语法，如创建表、修改表数据

触发器类型：

1. INSERT 型触发器【NEW 表示将要或者已经新增的数据】
2. UPDATE 型触发器【OLD表示修改之前的数据，NEW 表示将要或已经新增的数据】
3. DELETE 型触发器【OLD表示将要或者已经删除的数据】

 假设有这样一张数据表

CREATE TABLE `test_trans` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  `create_time` datetime DEFAULT NULL COMMENT 'Create Time',
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试事务'

创建触发器的格式

• 创建只有1个执行语句的触发器

        create trigger 触发器名 before|after 触发事件 on 表名 for each row

                执行语句;

•  创建有多个执行语句的触发器

        create trigger 触发器名 before|after 触发事件 on 表名 for each row

        begin
            执行语句列表

        end;

查看触发器

        SHOW TRIGGERS; 

3.前置知识——MySQL中符号@的作用 

参考链接：更多关于@的知识，点击此链接

参考链接：关于MySQL 游标的相关知识

参考链接：MySQL的预处理技术，prepare 的使用 

变量名，如果你不加@的话，会认为这是一个列名，但是这列不存在，就报错了；

• @变量名 : 定义一个用户变量.
• = 对该用户变量进行赋值.

用户变量赋值有两种方式: 一种是直接用"=“号，另一种是用”:=“号。

其区别在于:

• 使用set命令对用户变量进行赋值时，两种方式都可以使用；
• 用select语句时，只能用“:=”方式，因为select语句中，”="号被看作是比较操作符。

我曾遇到过一些问题（现已解决，可以参考这些链接）：

1. 声明游标一定要在声明变量之后
2. 触发器内，一定不要使用UPDATE、INSERT、DELETE
3. 关于使用变量值修改auto_increment的问题

---

4. 回归正题，如何实现将id值连续且自动增长

创建存储过程（reorder_id）

-- -------------------------------------------------------
-- test_trans 数据表【reorder_id】
-- 对 id 重新排序（使id连续）
-- 前提: 
--      1. id 是 主键
--      2. id 必须设置自增
-- -------------------------------------------------------
CREATE PROCEDURE reorder_id()
BEGIN
    -- 声明变量
    DECLARE i INT DEFAULT 1;
    DECLARE cursor_id INT DEFAULT 0;
 
    -- 1. 声明游标
    DECLARE temp_cursor CURSOR FOR SELECT id FROM test_trans;
 
    -- 获取数据的总条数
    SET @total = 0;
    SELECT COUNT(id) INTO @total FROM test_trans;
 
    -- 2. 打开游标
    OPEN temp_cursor;
 
    -- 3. 循环(使用游标)
    WHILE  i <= @total DO
        FETCH temp_cursor INTO cursor_id;
        UPDATE test_trans SET id = i WHERE id = cursor_id;
        SET i = i + 1;
    END WHILE;
    
    -- 4. 关闭游标
    CLOSE temp_cursor;
 
    SET @total = @total + 1;
 
    -- 执行预处理
    -- 预处理
    SET @total_sql = concat("ALTER TABLE test_trans AUTO_INCREMENT = ",@total);
    PREPARE sql_auto from @total_sql;
    EXECUTE sql_auto;
 
END 

创建存储过程后，进行调用（CALL reorder_id();）就能实现id值按升序连续排序

注意事项：

1.  id 值设置为主键 且 自动递增 
2.  id 值一定要升序，不能让后面的数据id小于前面数据的id
3.  每次要使id连续，需要调用CALL reorder_id();

演示：

假设有这样一张数据表，如下图所示

 将id打乱，删除其中几条数据（保持升序）

 调用存储过程CALL reorder_id();

 再次新增数据，发现id值又从4开始

 文章完