澳门新浦京娱乐场网站-www.146.net-新浦京娱乐场官网
做最好的网站

澳门新浦京娱乐场网站:相关知识点总结,自增

自增主键没有持久化是个比较早的bug,这点从其在官方bug网站的id号也可看出()。由Peter Zaitsev(现Percona CEO)于2003年提出。历史悠久且臭名昭著。 

前言

 

澳门新浦京娱乐场网站 1

悲剧啊!Mysql的上古BUG!!!

导读这是MySQL8.0修复的上古bug之一,在2003年由Percona的CEO(当时应该还没Percona吧)提出的bug#199,光看这bug号就扑面而来一股上古时代的沧桑气息。

澳门新浦京娱乐场网站 2
问题的本质在于InnoDB初始化AUTO_INCREMENT的方式,在每次重启时,总是算出表上最大的自增值作为最大值,下一次分配从该值开始。这意味着如果在btree右侧叶节点大量删除记录,重启后,自增值可能被重用。这在很多场景下可能导致问题,包括但不限于:主备切换、历史数据迁移等场景。在bug#199下面一大堆的回复里,可以看到大量的同行抱怨。

官方的修复就比较优雅了,不改变任何现有的存储,而是通过redo log来进行恢复。该补丁基于WL#7816的框架实现的,要想搞懂这个补丁,得先看看WL#7816做了哪些改动,为了解决这个问题,InnoDB使用一个引擎私有的系统表 特殊redo log的方式,在引擎内部自己解决corruption标记持久化的问题。其大概思路为:

  1. 当发现索引损坏时,写入一条redo log,但不更新数据词典
  2. 引入一个innodb引擎私有的系统表,称为DD Buffer Table,每次checkpoint之前会将索引corruption bit存入其中。
  3. 在崩溃恢复时,同时从redo log和DD Buffer Table中读取索引 corruption bit, 合并结果,并标记内存中的表和索引对象。

初始化Persister

目前Persister的类型仅有两种,一个用于corruption bit的持久化,一个用于自增列的持久化,对应的类为:

Persister:    |-- CorruptedIndexPersister    |-- AutoIncPersister

Persister对应全局对象dict_persist_t::persisters,可以通过类型persistent_type_t来找到对应的Persister,目前仅有PM_INDEX_CORRUPTED及PM_TABLE_AUTO_INC,但从注释来看,未来肯定会做更多的扩展,Persister在启动时调用函数dict_persist_init进行初始化。

新的系统表

新的系统表名为SYS_TABLE_INFO_BUFFER,对应管理类为DDTableBuffer,指针存储在dict_persist->table_buffer中。

系统表包含两个列:TABLE_ID及BLOB类型的METADATA(ref DDTableBuffer::init),METADATA列包含了所有需要持久化的元数据。

回写DDTableBuffer

有几种情况会将内存修改回写到DDTableBuffer中:

  1. 在做checkpoint(log_checkpoint)之前,所有在dirty_dict_tables链表上的表对象,对应persist metadata都需要回写到DDTableBuffer中(dict_persist_to_dd_table_buffer)
  2. 从内存中驱逐一个表对象时(dict_table_remove_from_cache_low),如果需要的话也会去尝试回写。
  3. 在对包含自增列的表做DDL后,需要持久化counter,在如下函数中,会调用dict_table_set_and_persist_autoinc:
ha_innobase::commit_inplace_alter_tablecreate_table_info_t::initialize_autoinc()// for example: alter table..auto_increment = ??row_rename_table_for_mysql;// rename from temporary table to normal table

回写的过程也比较简单(dict_table_persist_to_dd_table_buffer_low):

  1. 通过表对象初始化需要回写的Metadata数据: corrupt index及autoinc值(dict_init_dynamic_metadata)
  2. 构建记录值,插入DDTableBuffer系统表(DDTableBuffer::replace(), 如果记录存在的话,则进行悲观更新操作
  3. 表对象的diry_status修改成 METADATA_BUFFERED,表示有buffer的元数据

Recovery and Startup

在崩溃恢复时,当解析到日志MLOG_TABLE_DYNAMIC_META时(MetadataRecover::parseMetadataLog),会进行解析并将解析得到的数据存储到集合中(MetadataRecover::m_tables),如果存在相同table-id的项,就进行替换,确保总是最新的。

在完成recovery后,搜集到的meta信息暂时存储到srv_dict_metadata中, 随后进行apply(srv_dict_recover_on_restart), apply的过程也比较简单,载入表对象,然后对表对象进行更新(MetadataRecover::apply),例如对于autoinc列,就总是选择更大的那个值。

最后

这个bug已经挂了相当长的时间,不排除把这个bug当作InnoDB的“特性”的同学,一定要注意到这个改动...


原文来自:

本文地址:

导读 这是MySQL8.0修复的上古bug之一,在2003年由Percona的CEO(当时应该还没Percona吧)提出的bug#199,光看这bug号就...

首先,直观的重现下。

自增主键没有持久化是个比较早的bug,这点从其在官方bug网站的id号也可看出()。由Peter Zaitsev(现Percona CEO)于2003年提出。历史悠久且臭名昭著。

MySQL的自增列(AUTO_INCREMENT)和其它数据库的自增列对比,有很多特性和不同点(甚至不同存储引擎、不同版本也有一些不同的特性),让人感觉有点稍微复杂。下面我们从一些测试开始,来认识、了解一下这方面的特殊知识点:

    在一次宕机之后重启Mysql服务器并恢复数据的过程中发现了自增主键列的自增数值会回退,导致有数据上有冲突。经过一番的排查之后发现原来是主键自增值回退了,导致自增主键有重复导致外键关联失效引起的。

mysql> create table t1(id int auto_increment primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(null),(null),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
 ---- 
| id |
 ---- 
|  1 |
|  2 |
|  3 |
 ---- 
3 rows in set (0.00 sec)

mysql> delete from t1 where id=3;
Query OK, 1 row affected (0.36 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.35 sec)

mysql> select * from t1;
 ---- 
| id |
 ---- 
|  1 |
|  2 |
|  4 |
 ---- 
3 rows in set (0.01 sec)

首先,直观的重现下。

 

我们来举个例子说明一下具体的现象,例如,创建一个个InNoDB引擎表:

 

mysql> create table t1(id int auto_increment primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(null),(null),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from t1;
 ---- 
| id |
 ---- 
| 1 |
| 2 |
| 3 |
 ---- 
rows in set (0.00 sec)

mysql> delete from t1 where id=3;
Query OK, 1 row affected (0.36 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.35 sec)

mysql> select * from t1;
 ---- 
| id |
 ---- 
| 1 |
| 2 |
| 4 |
 ---- 
rows in set (0.01 sec)

 

CREATE TABLE `bsession`  (

虽然id为3的记录删除了,但再次插入null值时,并没有重用被删除的3,而是分配了4。

虽然id为3的记录删除了,但再次插入null值时,并没有重用被删除的3,而是分配了4。

自增列持久化问题

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

 

删除id为4的记录,重启数据库,重新插入一个null值。

 

`aname` varchar(80) NOT NULL DEFAULT ‘’,

删除id为4的记录,重启数据库,重新插入一个null值。

mysql> delete from t1 where id=4;
# service mysqld restart
mysql> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
 ---- 
| id |
 ---- 
| 1 |
| 2 |
| 3 |
 ---- 
rows in set (0.00 sec)

如果一个表拥有自增列,当前最大自增列值为9, 删除了自增列6、7、8、9的记录,重启MySQL服务后,再往表里面插入数据,自增列的值为6还是10呢?  如果表的存储引擎为MyISAM呢,又会是什么情况? 下面实验环境为MySQL 5.7.21

PRIMARY KEY (`id`)

 

可以看到,新插入的null值分配的是3,按照重启前的操作逻辑,此处应该分配5啊。

 

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

mysql> delete from t1 where id=4;
# service mysqld restart
mysql> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
 ---- 
| id |
 ---- 
|  1 |
|  2 |
|  3 |
 ---- 
3 rows in set (0.00 sec)

这就是自增主键没有持久化的bug。究其原因,在于自增主键的分配,是由InnoDB数据字典内部一个计数器来决定的,而该计数器只在内存中维护,并不会持久化到磁盘中。当数据库重启时,该计数器会通过下面这种方式初始化。

 

澳门新浦京娱乐场网站 3

 

SELECT MAX(ai_col) FROM table_name FOR UPDATE; 
mysql> drop table if exists test;

Query OK, 0 rows affected (0.08 sec)

 

mysql> create table test(id int auto_increment primary key, name varchar(32)) ENGINE=InnoDB;

Query OK, 0 rows affected (0.02 sec)

 

 

mysql> insert into test(name)

    -> select 'kkk1' from dual union all

    -> select 'kkk2' from dual union all

    -> select 'kkk3' from dual union all

    -> select 'kkk4' from dual union all

    -> select 'kkk5' from dual union all

    -> select 'kkk6' from dual union all

    -> select 'kkk7' from dual union all

    -> select 'kkk8' from dual union all

    -> select 'kkk9' from dual;

Query OK, 9 rows affected (0.01 sec)

Records: 9  Duplicates: 0  Warnings: 0

 

 

mysql> select * from test;

 ---- ------ 

| id | name |

 ---- ------ 

|  1 | kkk1 |

|  2 | kkk2 |

|  3 | kkk3 |

|  4 | kkk4 |

|  5 | kkk5 |

|  6 | kkk6 |

|  7 | kkk7 |

|  8 | kkk8 |

|  9 | kkk9 |

 ---- ------ 

9 rows in set (0.00 sec)

 

mysql> delete from test where id>=6;

Query OK, 4 rows affected (0.00 sec)

创建表测试表

可以看到,新插入的null值分配的是3,按照重启前的操作逻辑,此处应该分配5啊。

MySQL 8.0的解决思路

 

现在插入10条数据,再删除最后的几条,

 

将自增主键的计数器持久化到redo log中。每次计数器发生改变,都会将其写入到redo log中。如果数据库发生重启,InnoDB会根据redo log中的计数器信息来初始化其内存值。为了尽量减小对系统性能的影响,计数器写入到redo log中,并不会马上刷新。具体可参考:

重启MySQL服务后,然后我们插入一条记录,字段ID会从什么值开始呢? 如下所示,如果表的存储引擎为InnoDB,那么插入的数据的自增字段值为6.

INSERT INTO `bsession` (`aname`澳门新浦京娱乐场网站,) values ('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a’);

这就是自增主键没有持久化的bug。究其原因,在于自增主键的分配,是由InnoDB数据字典内部一个计数器来决定的,而该计数器只在内存中维护,并不会持久化到磁盘中。当数据库重启时,该计数器会通过下面这种方式初始化。

因自增主键没有持久化而出现问题的常见场景:

 

DELETE FROM `bsession` where `id` in (8,9,10);

SELECT MAX(ai_col) FROM table_name FOR UPDATE;
  1. 业务将自增主键作为业务主键,同时,业务上又要求主键不能重复。

  2. 数据会被归档。在归档的过程中有可能会产生主键冲突。

 

澳门新浦京娱乐场网站 4

 

所以,强烈建议不要使用自增主键作为业务主键。刨除这两个场景,其实,自增主键没有持久化的问题并不是很大,远没有想象中的”臭名昭著“。

澳门新浦京娱乐场网站 5

模拟数据操作

MySQL 8.0的解决思路

最后,给出一个归档场景下的解决方案,

 

删除操作之后,表中的数据仅有7条,最大的’id’ = 7。现在进行数据库重启,并重新插入10条数据。此时自增列是从8开始计数,还是从11开始计数呢?我想很多人都认为会从11开始计数,生成新纪录,但实际情况会超出我们的预期,下面我们实际验证一下:

 

创建一个存储过程,根据table2(归档表)自增主键的最大值来初始化table1(在线表)。这个存储过程可放到init_file参数指定的文件中,该文件中的SQL会在数据库启动时执行。

 

澳门新浦京娱乐场网站 6

将自增主键的计数器持久化到redo log中。每次计数器发生改变,都会将其写入到redo log中。如果数据库发生重启,InnoDB会根据redo log中的计数器信息来初始化其内存值。为了尽量减小对系统性能的影响,计数器写入到redo log中,并不会马上刷新。具体可参考:

DELIMITER ;;
CREATE PROCEDURE `auto_increment_fromtable2`(IN table1 VARCHAR(255), IN table2 VARCHAR(255))
BEGIN
set @qry = concat('SELECT @max1 := (`id`   1) FROM `',table1,'` ORDER BY `id` DESC LIMIT 1;');
 prepare stmt from @qry;
execute stmt;
deallocate prepare stmt;
 set @qry = concat('SELECT @max2 := (`id`   1) FROM `',table2,'` ORDER BY `id` DESC LIMIT 1;');
 prepare stmt from @qry;
execute stmt;
deallocate prepare stmt;
IF @max1 < @max2 THEN
 set @qry = concat('alter table `',table1,'` auto_increment=',@max2);prepare stmt from @qry;execute stmt;deallocate prepare stmt;
SELECT 'updated' as `status`;
else
SELECT 'no update needed' as `status`;
END IF;
END ;;
DELIMITER ;

接下来,我们创建一个MyISAM类型的测试表。如下所示:

见证奇迹

 

总结

 

你肯定会问了,这是为什么呢?

 

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对脚本之家的支持。

mysql> drop table if exists test;

Query OK, 0 rows affected (0.01 sec)

 

mysql> create table test(id int auto_increment  primary key, name varchar(32)) engine=MyISAM;

Query OK, 0 rows affected (0.02 sec)

 

mysql> 

 

insert into test(name)

select 'kkk1' from dual union all

select 'kkk2' from dual union all

select 'kkk3' from dual union all

select 'kkk4' from dual union all

select 'kkk5' from dual union all

select 'kkk6' from dual union all

select 'kkk7' from dual union all

select 'kkk8' from dual union all

select 'kkk9' from dual;

 

 

mysql> delete from test where id>=6;

Query OK, 4 rows affected (0.00 sec)

同样我也感觉很奇怪,于是就了解MySql的InnoDB引擎是如何处理自增列的:

因自增主键没有持久化而出现问题的常见场景:

您可能感兴趣的文章:

  • MySQL 8.0的关系数据库新特性详解
  • Mysql 8 新特性 window functions 的作用
  • MySQL 8 新特性之Invisible Indexes
  • MySQL8新特性:持久化全局变量的修改方法
  • MySQL8新特性:降序索引详解

 

原因是InnoDB引擎对AUTO_INCREMENT计数器是存放到主内存中的,并非硬盘。所以当重启后内存数据就丢失了!

  1. 业务将自增主键作为业务主键,同时,业务上又要求主键不能重复。

  2. 数据会被归档。在归档的过程中有可能会产生主键冲突。

删除了id>=6的记录后,重启MySQL服务,如下所示,测试结果为id =10, 那么为什么出现不同的两个结果呢?这个是因为InnoDB存储引擎中,自增主键没有持久化,而是放在内存中,关于自增主键的分配,是由InnoDB数据字典内部一个计数器来决定的,而该计数器只在内存中维护,并不会持久化到磁盘中。当数据库重启时,该计数器会通过SELECT MAX(ID) FROM TEST FOR UPDATE这样的SQL语句来初始化(不同表对应不同的SQL语句), 其实这是一个bug来着, 对应的链接地址为: 8.0 ,才将自增主键的计数器持久化到redo log中。每次计数器发生改变,都会将其写入到redo log中。如果数据库发生重启,InnoDB会根据redo log中的计数器信息来初始化其内存值。 而对应与MySIAM存储引擎,自增主键的最大值存放在数据文件当中,每次重启MySQL服务都不会影响其值变化。

我们来看官方文档:https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html澳门新浦京娱乐场网站:相关知识点总结,自增主键的持久化详解。,中的【InnoDB AUTO_INCREMENT Counter Initialization】一节的详细说明:

 

 

澳门新浦京娱乐场网站 7

所以,强烈建议不要使用自增主键作为业务主键。刨除这两个场景,其实,自增主键没有持久化的问题并不是很大,远没有想象中的”臭名昭著“。

 

MySQL官网说明

 

澳门新浦京娱乐场网站 8

不过这个特性将在Mysql的下一个版本8.0中更改,自增计数器每次更改时,当前的最大自增计数器值将会被写入redo log中,并保存到每个检查点的 InnoDB引擎的私有系统表中,实现自增计数器的持久化,重启后会保持一致。

最后,给出一个归档场景下的解决方案,

 

当服器在Crash中的恢复重启过程中,InnoDB使用存储在系统字典表里的当前最大自增值初始化到内存,并且从最后一个检查点开始扫描Redo Log中写入的计数器值。如果Redo Log中的值大于内存中的计数器值,Redo Log中的值将会被采用。

 

 

关于后续版本中对于 自增列的处理机制 请查看官方文档的详细说明,这里不在赘述。https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

创建一个存储过程,根据table2(归档表)自增主键的最大值来初始化table1(在线表)。这个存储过程可放到init_file参数指定的文件中,该文件中的SQL会在数据库启动时执行。

 

DELIMITER ;;
CREATE PROCEDURE `auto_increment_fromtable2`(IN table1 VARCHAR(255), IN table2 VARCHAR(255))
BEGIN
set @qry = concat('SELECT @max1 := (`id`   1) FROM `',table1,'` ORDER BY `id` DESC LIMIT 1;');
    prepare stmt from @qry;
execute stmt;
deallocate prepare stmt;
    set @qry = concat('SELECT @max2 := (`id`   1) FROM `',table2,'` ORDER BY `id` DESC LIMIT 1;');
    prepare stmt from @qry;
execute stmt;
deallocate prepare stmt;
IF @max1 < @max2 THEN
    set @qry = concat('alter table `',table1,'` auto_increment=',@max2);prepare stmt from @qry;execute stmt;deallocate prepare stmt;
SELECT 'updated' as `status`;
else
SELECT 'no update needed' as `status`;
END IF;
END ;;
DELIMITER ;

自增列细节特性

 

 

1:SQL模式的NO_AUTO_VALUE_ON_ZERO值影响AUTO_INCREMENT列的行为。

 

mysql> drop table if exists test;

Query OK, 0 rows affected (0.01 sec)

 

mysql> create table test(id int auto_increment primary key, name varchar(32));

Query OK, 0 rows affected (0.02 sec)

 

mysql> select @@sql_mode;

 ------------------------------------------------------------------------------------------------------------------------------------------- 

| @@sql_mode                                                                                                                                |

 ------------------------------------------------------------------------------------------------------------------------------------------- 

| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

 ------------------------------------------------------------------------------------------------------------------------------------------- 

1 row in set (0.00 sec)

 

mysql> insert into test(id, name) value(0, 'kerry');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  1 | kerry |

 ---- ------- 

1 row in set (0.00 sec)

 

mysql> 

 

如上所示,如果在SQL模式里面没有设置NO_AUTO_VALUE_ON_ZERO的话,那么在默认设置下,自增列默认一般从1开始自增,插入0或者null代表生成下一个自增长值。如果用户希望插入的值为0,而该列又是自增长的,那么这个选项就必须设置

 

mysql> SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into test(id, name) value(0, 'kerry');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  0 | kerry |

|  1 | kerry |

 ---- ------- 

2 rows in set (0.00 sec)

 

mysql> 

 

 

 

2:如果把一个NULL值插入到一个AUTO_INCREMENT数据列里去,MySQL将自动生成下一个序列编号。如下所示,这个语法对于熟悉SQL Server中自增字段的人来来看,简直就是不可思议的事情。

 

mysql> drop table if exists test;

Query OK, 0 rows affected (0.03 sec)

 

mysql> create table test(id int auto_increment primary key, name varchar(32));

Query OK, 0 rows affected (0.05 sec)

 

mysql> insert into test(id , name) value(null, 'kerry');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  1 | kerry |

 ---- ------- 

1 row in set (0.00 sec)

 

 

 

3:获取当前自增列的值

 

    获取当前自增列的值,可以使用 LAST_INSERT_ID函数,注意,这个是一个系统函数,可获得自增列自动生成的最后一个值。但该函数只与服务器的本次会话过程中生成的值有关。如果在与服务器的本次会话中尚未生成AUTO_INCREMENT值,则该函数返回0

 

mysql> select last_insert_id();

 ------------------ 

| last_insert_id() |

 ------------------ 

|                1 |

 ------------------ 

1 row in set (0.00 sec)

 

mysql> insert into test(name) value('jimmy');

Query OK, 1 row affected (0.00 sec)

 

mysql> select last_insert_id();

 ------------------ 

| last_insert_id() |

 ------------------ 

|                2 |

 ------------------ 

1 row in set (0.00 sec)

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  1 | kerry |

|  2 | jimmy |

 ---- ------- 

2 rows in set (0.00 sec)

 

如果要获取自增列的下一个值,那么可以使用show create table tablename查看。如下截图所示

 

澳门新浦京娱乐场网站 9

 

 

4:自增列跳号

 

MySQL中,自增字段可以跳号:可以插入一条指定自增列值的记录(即使插入的值大于自增列的最大值),如下所示,当前自增列最大值为1,我插入一个200的值,然后就会以200为基础继续自增,而且我还可以继续插入ID=100的记录,无需任何额外设置。

 

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  1 | kerry |

 ---- ------- 

1 row in set (0.00 sec)

 

mysql> insert into test value(200, 'test');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test;

 ----- ------- 

| id  | name  |

 ----- ------- 

|   1 | kerry |

| 200 | test  |

 ----- ------- 

2 rows in set (0.00 sec)

 

mysql> insert into test(name) value('test2');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test;

 ----- ------- 

| id  | name  |

 ----- ------- 

|   1 | kerry |

| 200 | test  |

| 201 | test2 |

 ----- ------- 

3 rows in set (0.00 sec)

 

mysql> 

mysql> insert into test(id, name) value(100, 'ken');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test;

 ----- ------- 

| id  | name  |

 ----- ------- 

|   1 | kerry |

| 100 | ken   |

| 200 | test  |

| 201 | test2 |

 ----- ------- 

4 rows in set (0.00 sec)

 

 

另外一个是关于自增列逻辑跳号问题,在一个事务里面,使用遇到事务回滚,自增列就会跳号,如下所示,id从201 跳到 203了。

 

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into test(name) value('kkk');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

 ----- ------- 

| id  | name  |

 ----- ------- 

|   1 | kerry |

| 100 | ken   |

| 200 | test  |

| 201 | test2 |

| 202 | kkk   |

 ----- ------- 

5 rows in set (0.00 sec)

 

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into test(name) value('kkk');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

 ----- ------- 

| id  | name  |

 ----- ------- 

|   1 | kerry |

| 100 | ken   |

| 200 | test  |

| 201 | test2 |

| 203 | kkk   |

 ----- ------- 

5 rows in set (0.00 sec)

 

当然,无论MySQL还是其他关系型数据库,都会遇到这种逻辑跳号的情况,例如ORACLE的序列也会存在这种逻辑跳号问题。为提高自增列的生成效率,都将生成自增值的操作设计为非事务性操作,表现为当事务回滚时,事务中生成的自增值不会被回滚。

 

5:truncate table操作会引起自增列从头开始计数

 

mysql> truncate table test;

Query OK, 0 rows affected (0.01 sec)

 

mysql> insert into test(name) value('kerry');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  1 | kerry |

 ---- ------- 

1 row in set (0.00 sec)

 

mysql> 

 

6:修改AUTO_INCREMENT的值来修改自增起始值。

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  1 | kerry |

 ---- ------- 

1 row in set (0.00 sec)

 

mysql> alter table test auto_increment=100;

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> insert into test(name) value('k3');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

 ----- ------- 

| id  | name  |

 ----- ------- 

|   1 | kerry |

| 100 | k3    |

 ----- ------- 

2 rows in set (0.00 sec)

 

当然MySQL还有一些相关知识点,这里没有做总结,主要是没有遇到过相关场景。以后遇到了再做总结,另外一方面,写技术文章,很难面面俱到,这样太耗时也太累人了!

 

 

 

参考资料:

 

本文由澳门新浦京娱乐场网站发布于数据库,转载请注明出处:澳门新浦京娱乐场网站:相关知识点总结,自增