关于mysql:特性介绍-MySQL-自增列详解1自增列概念及使用

本文首发于 2019-12-09 19:37:10

1. 概念

自增列，即 AUTO_INCREMENT，可用于为新的记录生成惟一标识。

要求：

1. AUTO_INCREMENT 是数据列的一种属性，只实用于整数类型数据列。
2. AUTO_INCREMENT 数据列必须具备 NOT NULL 属性。

2. 应用办法

2.1. 创立含自增列的表

-- 不指定 AUTO_INCREMENT 的值，则从1开始mysql> create table t1(a int auto_increment primary key,b int);Query OK, 0 rows affected (0.01 sec)-- 手动指定 AUTO_INCREMENT 的值mysql> create table t2(a int auto_increment primary key,b int) AUTO_INCREMENT=100;Query OK, 0 rows affected (0.02 sec)

2.2. 插入数据

-- 不指定自增列mysql> insert into t1(b) values(1),(2);Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+---+------+| a | b    |+---+------+| 1 |    1 || 2 |    2 |+---+------+3 rows in set (0.00 sec)-- 指定自增列mysql> insert into t1(a,b) values(3,3);Query OK, 1 row affected (0.00 sec)

2.3. 如何查看表的 AUTO_INCREMENT 涨到了多少？

mysql> show create table t1;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                     |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t1    | CREATE TABLE `t1` (  `a` int(11) NOT NULL AUTO_INCREMENT,  `b` int(11) DEFAULT NULL,  PRIMARY KEY (`a`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

2.4. 插入数据时是否有空洞？

能够的，但要留神 AUTO_INCREMENT 的值肯定比自增列以后最大的记录值大。

-- 发明空洞mysql> insert into t1(a,b) values(5,5);Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+---+------+| a | b    |+---+------+| 1 |    1 || 2 |    2 || 3 |    3 || 5 |    5 |+---+------+5 rows in set (0.00 sec)mysql> show create table t1;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                     |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t1    | CREATE TABLE `t1` (  `a` int(11) NOT NULL AUTO_INCREMENT,  `b` int(11) DEFAULT NULL,  PRIMARY KEY (`a`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

2.5. 是否插入重复记录

既然自增列是惟一记录，那么必定不能插入重复记录。

-- 尝试插入重复记录mysql> insert into t1(a,b) values(5,5);ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

2.6. 怎么批改 AUTO_INCREMENT 的值？

留神：AUTO_INCREMENT 不能小于以后自增列记录的最大值。

-- 尝试将 AUTO_INCREMENT 设为10mysql> alter table t1 AUTO_INCREMENT=10;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table t1;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                      |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t1    | CREATE TABLE `t1` (  `a` int(11) NOT NULL AUTO_INCREMENT,  `b` int(11) DEFAULT NULL,  PRIMARY KEY (`a`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)-- 尝试将 AUTO_INCREMENT 设为4mysql> alter table t1 AUTO_INCREMENT=4;Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0-- 因为自增列最大记录值是5，那么 AUTO_INCREMENT 不能小于5，因而该值为6mysql> show create table t1;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                     |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t1    | CREATE TABLE `t1` (  `a` int(11) NOT NULL AUTO_INCREMENT,  `b` int(11) DEFAULT NULL,  PRIMARY KEY (`a`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

3. 问题

3.1. 自增列是否有下限？

由上文可见，自增列会始终减少，那是否有下限呢？

上文中表 t1 的自增列是 int 类型，由下表（MySQL 5.7）可见取值范畴是 -2147483648 到 2147483647（ -2³¹ ~ 2³¹ - 1 ）。

验证如下：

mysql> show create table t1;+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                              |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t1    | CREATE TABLE `t1` (  `a` int(11) NOT NULL AUTO_INCREMENT,  `b` int(11) DEFAULT NULL,  PRIMARY KEY (`a`)) ENGINE=InnoDB AUTO_INCREMENT=2147483644 DEFAULT CHARSET=utf8 |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)mysql> insert into t1(b) values(0),(0),(0);Query OK, 1 row affected (0.00 sec)mysql> insert into t1(b) values(0);ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'mysql> show create table t1;+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                              |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t1    | CREATE TABLE `t1` (  `a` int(11) NOT NULL AUTO_INCREMENT,  `b` int(11) DEFAULT NULL,  PRIMARY KEY (`a`)) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8 |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

这里须要补充阐明下 int(11) 中的数字的含意：

MySQL中整数数据类型前面的(N)指定显示宽度。
显示宽度不影响查问进去的后果。
显示宽度限度了小数点的地位(只有理论数字不超过显示宽度，这种状况下，数字显示为原样)。
显示宽度也是一个有用的工具，能够让开发人员晓得应该将值填充到哪个长度。

3.2. 如何防止自增列超过最大值？

能够采纳无符号的 BIGINT 类型（也可依据业务产生自增列的速度采纳适合的类型），能极大晋升自增列的范畴。

mysql> create table t2(a bigint unsigned primary key auto_increment,b int);Query OK, 0 rows affected (0.00 sec)mysql> alter table t2 auto_increment=18446744073709551613;Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table t2;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                    |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t2    | CREATE TABLE `t2` (  `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,  `b` int(11) DEFAULT NULL,  PRIMARY KEY (`a`)) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551613 DEFAULT CHARSET=utf8 |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)mysql> insert into t2(b) values(0);Query OK, 1 row affected (0.00 sec)mysql> insert into t2(b) values(0);ERROR 1467 (HY000): Failed to read auto-increment value from storage enginemysql>mysql> select * from t2;+----------------------+------+| a                    | b    |+----------------------+------+| 18446744073709551613 |    0 |+----------------------+------+1 row in set (0.00 sec)

UNSIGNED BIGINT 类型的范畴到底有多大呢？

如果每秒自增100万次，想要耗费完须要 18446744073709551613/1000000/3600/24/365=584942年。

有的敌人会问如果自增列不是采纳BIGINT类型，那么达到最大值后该表就无奈写入，此时该怎么办呢？

个别达到最大值后再次插入数据会报错ERROR 1467 (HY000): Failed to read auto-increment value from storage engine，能够通过alter table 将自增列的类型设为数值范畴更大的类型（比方BIGINT）。

4. 总结

1. AUTO_INCREMENT 列必然惟一，且仅用于整型类型。
2. AUTO_INCREMENT 列会持续增长，不会因 delete 自增列最大的记录而变小。
3. 当 AUTO_INCREMENT 列达到以后类型的最大值后将无奈插入数据，会报错ERROR 1467 (HY000): Failed to read auto-increment value from storage engine，此时将自增列改为 BIGINT 类型可解决问题。
4. 为了防止自增列达到最大值，可将其设为BIGINT类型。
5. 应用 alter table 批改 AUTO_INCREMENT 列时，其值会取自增列以后最大记录值+1与将要设置的值的最大值。
6. 在MySQL 5.7 中，将列设置成 AUTO_INCREMENT 之后，必须将其设置成主键/或者是主键的一部分，否则会报错ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key。

欢送关注我的微信公众号【数据库内核】：分享支流开源数据库和存储引擎相干技术。