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

1.前置知识——事务

2. 前置知识——触发器

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

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

1.前置知识——事务

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

事务隔离级别：

读未提交 read uncommitted
读已提交 read committed
可重复读 repeatable read
串行化 serializable

查看全局系统隔离级别
select @@global.transaction_isolation;

查看当前系统隔离级别
select @@transaction_isolation;

2. 前置知识——触发器

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

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

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

触发器类型：

INSERT 型触发器【NEW 表示将要或者已经新增的数据】
UPDATE 型触发器【OLD表示修改之前的数据，NEW 表示将要或已经新增的数据】
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语句中，”="号被看作是比较操作符。

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

声明游标一定要在声明变量之后
触发器内，一定不要使用UPDATE、INSERT、DELETE
关于使用变量值修改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值按升序连续排序