关于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 设为 10
mysql> alter table t1 AUTO_INCREMENT=10;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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=10 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 尝试将 AUTO_INCREMENT 设为 4
mysql> alter table t1 AUTO_INCREMENT=4;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 因为自增列最大记录值是 5，那么 AUTO_INCREMENT 不能小于 5，因而该值为 6
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)

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: 0

mysql> 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 engine
mysql>
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。

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