mysql 自增长id mysql自动增长id

目录

1.前置知识——事务

2. 前置知识——触发器 

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

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

---

1.前置知识——事务

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

 事务隔离级别：

1. 读未提交
2. 读已提交
3. 可重复读
4. 串行化

查看全局系统隔离级别

select @@global.transaction_isolation;

 查看当前系统隔离级别

select @@transaction_isolation;

2. 前置知识——触发器 

触发器类型：

1. INSERT
2. UPDATE
3. DELETE

 假设有这样一张数据表

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

执行语句;

• 多个执行语句的触发器

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

        begin
执行语句列表

        end;

查看触发器

        SHOW TRIGGERS; 

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

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

• @变量名 : 定义一个用户变量.
• =

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

其区别在于:

• 使用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. 主键 且 自动递增 
2. 升序，不能让后面的数据id小于前面数据的id
3. CALL reorder_id();

演示：

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

保持升序）

CALL reorder_id();

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

 文章完