MySQL中AUTO_INCREMENT介绍
添加auto_increment:alter table tb_name change 字段 新字段 类型 auto_increment;
删除auto_increment:alter table tb_name change 字段 新字段 类型;
修改auto_increment:alter table tb_name auto_increment=xxx;
我们再来看一个自增长问题,如下测试:
# 第一种
mysql> create table t1(id int auto_increment, a int, primary key (id)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values (1,2),(2,2),(3,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> insert into t1 values (4,2),(null,2),(null,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
# 第二种
mysql> create table t1(id int auto_increment primary key,name varchar(255));
Query OK, 0 rows affected (0.12 sec)
mysql> create table t2(name varchar(255))engine=innodb;
Query OK, 0 rows affected (0.10 sec)
mysql> insert into t2 values('aa'),('bb');
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t1(name) select * from t2;
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
+----+------+
2 rows in set (0.00 sec)
mysql> show create table t1;
| Table | Create Table
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)
我们可以看到第一种带NULL值的写法,自增长值会多增加一个值;第二种insert .. select,自增长也会多增加一个值。这个会带来什么问题呢?你会发现从库自增长值却是正常的(当复制格式为ROW时),这个时候其实也就是主从数据不一致了,但影响不大,除非出现记录ID大于自增长ID,那样插入数据重复会报错。
插入操作会依据这个自增长的计数器加1赋予自增长列。这个实现方式称作AUTO-INC Locking(自增锁)。 这种自增锁是采用一种特殊的表锁机制,为了提高插入的性能,锁不是在一个事务完成后才释放,而是在完成对自增长值插入的sql 语句后立即释放。
AUTO-INC Locking 从一定程度上提高了并发插入的效率,但还是存在一些性能上的问题。
从MySQL 5.1.22版本开始,InnoDB存储引擎中提供了一种轻量级互斥量的自增长实现机制,这种机制大大提高了自增长值插入的性能。并且从该版本开始,InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式,该参数的默认值为1。在继续讨论新的自增长实现方式之前,需要对自增长的插入进行分类。如下说明:
innodb_autoinc_lock_mode 在不同设置下对自增长的影响(默认为1):
MySQL 8.0修复了一个MySQL十年之久的自增列Bug,在2003年由Percona的CEO提出的bug#199。先重现一下这个BUG。
### MySQL 5.7
mysql> select * from t1;
+----+------+
| id | a |
+----+------+
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
+----+------+
3 rows in set (0.00 sec)
# 删除一些记录
mysql> delete from t1 where id=2 or id=3;
mysql> select * from t1;
+----+------+
| id | a |
+----+------+
| 1 | 2 |
+----+------+
1 row in set (0.00 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
### 重启MySQL后查看AUTO_INCREMENT
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
### MySQL 8.0
mysql> select * from t;
+----+------+---------------------+
| id | name | time |
+----+------+---------------------+
| 1 | l | 2019-08-07 19:06:32 |
| 2 | y | 2019-08-07 19:06:49 |
| 4 | c | 2019-08-07 19:08:08 |
| 6 | h | 2019-08-07 19:11:25 |
+----+------+---------------------+
4 rows in set (0.00 sec)
mysql> delete from t where id=4;
Query OK, 1 row affected (0.00 sec)
mysql> delete from t where id=6;
Query OK, 1 row affected (0.01 sec)
mysql> show create table t;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
### 重启MySQL后查看AUTO_INCREMENT
mysql> show create table t;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
如果为InnoDB表指定AUTO_INCREMENT列,则内存中表对象包含一个称为自动增量计数器的特殊计数器,该计数器在为列分配新值时使用。
在MySQL 5.7及更早版本中,自动增量计数器仅存储在内存中,而不存储在磁盘上。要在服务器重新启动后初始化自动增量计数器,InnoDB将在第一次插入到包含AUTO_INCREMENT列的表中执行以下语句:
SELECT MAX(ai_col)FROM table_name FOR UPDATE;
上面的测试反映了mysqld重启后,InnoDB存储引擎的表自增id可能出现重复利用的情况。这在很多场景下可能导致问题,包括但不限于:主备切换、历史数据迁移等场景。在bug#199下面一大堆的回复里,可以看到大量的同行抱怨。
在MySQL 8.0中,此问题已修改。每次更改时,当前最大自动增量计数器值将写入redo log,并保存到每个检查点上的引擎专用系统表中。这些更改使当前最大自动增量计数器值在服务器重新启动时保持不变。
InnoDB在MySQL 8.0及更高版本中使用等效的SELECT MAX(ai_col)FROM table_name FOR UPDATE语句来初始化自动增量计数器的唯一情况是导入没有.cfg元数据文件的表空间。否则,从.cfg元数据文件中读取当前最大自动增量计数器值。
另外:在MySQL 5.7和8.0开启一个事务后,有类INSERT操作,自增值就会增加;但是当事务回滚后,自增值并不会减小。也就是说自增值会有空洞。但是MySQL 5.7重启会自增值会减小,MySQL 8.0自增值不会减小。
网友评论