MySQL自增列(AUTO_INCREMENT)相关知识点总结
分类:计算机教程

 

美洲杯赔率 1

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

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

 

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

 

CREATE TABLE `bsession`  (

自增列持久化问题

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

 

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

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

PRIMARY KEY (`id`)

 

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

 

美洲杯赔率 2

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)

创建表测试表

 

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

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

INSERT INTO `bsession` (`aname`) values ('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a’);

美洲杯赔率, 

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

 

美洲杯赔率 3

美洲杯赔率 4

模拟数据操作

 

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

 

美洲杯赔率 5

接下来,我们创建一个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引擎是如何处理自增列的:

 

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

删除了id>=6的记录后,重启MySQL服务,如下所示,测试结果为id =10, 那么为什么出现不同的两个结果呢?这个是因为InnoDB存储引擎中,自增主键没有持久化,而是放在内存中,关于自增主键的分配,是由InnoDB数据字典内部一个计数器来决定的,而该计数器只在内存中维护,并不会持久化到磁盘中。当数据库重启时,该计数器会通过SELECT MAX(ID) FROM TEST FOR UPDATE这样的SQL语句来初始化(不同表对应不同的SQL语句), 其实这是一个bug来着, 对应的链接地址为:https://bugs.mysql.com/bug.php?id=199,直到MySQL 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】一节的详细说明:

 

美洲杯赔率 6

 

MySQL官网说明

美洲杯赔率 7

不过这个特性将在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

 

自增列细节特性

 

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)

 

 

本文由美洲杯赔率发布于计算机教程,转载请注明出处:MySQL自增列(AUTO_INCREMENT)相关知识点总结

上一篇:SQL 美洲杯赔率中Count()的问题 下一篇:没有了
猜你喜欢
热门排行
精彩图文
  • MySQL自增列(AUTO_INCREMENT)相关知识点总结
    MySQL自增列(AUTO_INCREMENT)相关知识点总结
      MySQL的自增列(AUTO_INCREMENT)和其它数据库的自增列对比,有很多特性和不同点(甚至不同存储引擎、不同版本也有一些不同的特性),让人感觉有点稍微
  • 应用canvas绘制动态时钟--每秒自动动态更新时间,
    应用canvas绘制动态时钟--每秒自动动态更新时间,
    使用canvas绘制时钟 下文是部分代码,完整代码参照: https://github.com/lemoncool/canvas-clock ,可直接下载。 首先看一下效果图:每隔一秒会动态更新时间 一、前
  • Expression经验之前言,Expression经验美洲杯赔率
    Expression经验之前言,Expression经验美洲杯赔率
    对于C#中的Expression特性想必从事C#开发的同学都不会陌生,网上和园子里都有很多的好的文章介绍。我想也没有必要再去写一些文章去介绍,科普或是从入
  • 10分钟学会前端调试利器——FireBug
    10分钟学会前端调试利器——FireBug
    10分钟学会前端调试利器——FireBug 2015/09/17 · HTML5 , JavaScript · 1评论 · 调试 原文出处: 惟吾德馨(@Allen_Bryant)     作者:惟吾德馨‘ 原文地址: http:
  • Data URI(转)
    Data URI(转)
    细说 Data URI 2015/08/27 · HTML5 · URI 原文出处: 李靖(@Barret李靖)     Data URL 早在 1995 年就被提出,那个时候有很多个版本的 Data URL Schema定义陆续出现在