MYSQL 更新自增id mysql自增id

---

文章目录

• 背景
• 自增ID的数据类型

• 单位换算规则
• 自增ID取值范围

• 无符号位的计算方式
• 有符号位的计算方式

• int和int(11)有什么区别

• 表自增主键的自增值
• MySQL全局的自增值row_id
• Xid
• Innodb的事务ID
• 线程ID
• 总结

背景

MySQL中有各种各样的自增ID。例如我们最常见的表的自增ID，Xid，事务的ID，线程的ID，表的编号ID，binlog日志文件的ID等等。这些ID都是有它自己的增长规律的，并不是随机生成的。MySQL的整体功能设计，有很多地方都依赖于这些ID的增长规律。

接下来我们选择几个经常遇到的来分析一下。

自增ID的数据类型

我们在使用自增ID的时候，定义自增ID字段的类型为int，而int类型是一个大类，它有可以细分为tinyint、smallint、mediumit、int、bigint5中类型。

每一种int类型的取值范围是不同的，如下表格所示：

类型	占空间(byte)	占空间(Bit)	存储范围(有符号)	取值范围(有符号)	存储范围(无符号)	取值范围(无符号)
tinyint	1	8	$[-2^{(8-1)},2^{(8-1)}-1]$	[-128,127]	$[0,2^8-1]$	[0,255]
smallint	2	16	$[-2^{(16-1)},2^{(16-1)}-1]$	[-32768,32767]	$[0,2^{16}-1]$	[0,65535]
mediumint	3	24	$[-2^{(24-1)},2^{(24-1)}-1]$	[-8388608,8388607]	$[0,2^{24}-1]$	[0,16777215]
int	4	32	$[-2^{(32-1)},2^{(32-1)}-1]$	[-2147483648,2147483647]	$[0,2^{32}-1]$	[0,4294967295]
bigint	8	64	$[-2^{(64-1)},2^{(64-1)}-1]$	[-9223372036854775808,9223372036854775807]	$[0,2^{64}-1]$	[0,18446744073709551615]

单位换算规则

上面的表格中，我们提到的占用空间的大小问题，不同的整型数据类型所占用的磁盘存储空间是不同的。具体的换算用到的单位如下：

1. 1PB(拍字节)=1024TB(太字节)，简写为T
2. 1TB=1024GB(吉字节)，简写为G
3. 1GB=1024MB(兆字节)，简写为M
4. 1MB=1024KB(千字节)，简写为K
5. 1KB=1024Byte(字节)，简写为B
6. 1Byte=8Bit(位)，简写为b
7. 1Bit = 1个二进制数字，值为0或者1

自增ID取值范围

结合上面的计算转换关系。我们使用tinyint来举例说明它的取值范围是怎么计算来的。

tinyint占用1个byte，也就是8个bit，1byte=8bit，即为：一个字节等于8位。

无符号位的计算方式

一个8位的无符号二进制能存放的二进制数值范围是[00000000~11111111]，将其转换为十进制就是[0,255]。下面说一下转换的过程是怎么样的。二进制中的数据非0即为1，逢二进一，00000000为最小的二进制数，11111111为最大的二进制数。

二进制的00000000转换为十进制的时候就是从右侧第一个二进制数0开始向左侧依次：

$0*2^0 + 0*2^1 + 0*2^2 + 0*2^3 + 0*2^4 + 0*2^5 + 0*2^6 + 0*2^7 = 0$

二进制的11111111转换为十进制的时候就是从右侧第一个二进制数1开始向左侧依次：

$1*2^0 + 1*2^1 + 1*2^2 + 1*2^3 + 1*2^4 + 1*2^5 + 1*2^6 + 1*2^7 = 2^8 - 1 =255$

这就是无符号的时候，一个8位的二进制数所能存储数据范围转换为十进制数据存储范围的过程。所以一个8位的二进制数能存储的无符号的十进制数的范围是[0,255]

有符号位的计算方式

那有符号的时候，该怎么计算呢？

在二进制中，正号用0表示，负号用1表示，并且需要把正负号放在二进制的最高位，也就是最左边的位置，剩余右边的7个位置用来表示二进制的具体数值。那么一个有正负号的8位二进制取值范围就是[11111111,01111111]。去掉左侧第一位用来标记正负号的位置，还剩余7个位置，这7个位置都是1的时候是最大的二进制数。如果前面使用一个负号(此时用1表示)就是最小的二进制数，如果前面增加一个正号(此时用0表示)就是最大的二进制数。所以一个有正负号的8位的二进制数的取值范围为：[11111111,01111111]。

有符号的二进制的11111111转换为十进制的时候就是从右侧第一个二进制数0开 始向左侧依次：

$-(1*2^0 + 1*2^1 + 1*2^2 + 1*2^3 + 1*2^4 + 1*2^5 + 1*2^6) = -(2^7 - 1) = -127$

二进制的01111111转换为十进制的时候就是从右侧第一个二进制数1开始向左侧依次：

$1*2^0 + 1*2^1 + 1*2^2 + 1*2^3 + 1*2^4 + 1*2^5 + 1*2^6 = 2^7 - 1 = 127$

怎么有符号的最小值是-127，而不是-128呢？在计算机中，表示负值是用补码。
为什么有符号的tinyint的最小值是-128？虽然“-0”也是“0”，但根据正、反、补码体系，“-0”的补码和“+0”是不同的，这样就出现两个补码代表一个数值的情况。为了将补码与数字一一对应，所以人为规定“0”一律用“+0”代表。同时为了充分利用资源，就将原来本应该表示“-0”的补码规定为代表-128。

int和int(11)有什么区别

这里插一个题外话。因为我看很多同事在创建表的时候，习惯性的对int类型的字段指定一个长度单位。

例如：int(11)是他们经常使用的方式。

那么我们在定义表中自增主键或某个int类型的字段的时候，写成int和int(11)有什么区别？

int(11)中的11表示int类型所能存储的最小值的显示宽度。

注意，这里是显示宽度，表示可以显示多长的int类型的值。我们从上面表中可以知道，int类型的取值范围为：-2147483648~2147483647，其中的最小值-2147483648的长度为11，这里的int(11)表示可以显示出这个最小值的完整内容，而最大值2147483647的长度为10，因为最小值长度为11数值都可以显示出来，那么最大值的长度为10的数值肯定也可以显示出来。

当我们定义了一个int(11)类型字段后，如果后面不指定UNSIGNED ZEROFILL关键字，这个字段和int是一样的。只有指定的UNSIGNED ZEROFILL之后，这个int(11)中的11才起到作用。他起到的作用就是和UNSIGNED ZEROFILL配合使用，将我们插入的数据，在不满足长度的情况下，在前面补0。

比如我们定义了int(5) UNSIGNED ZEROFILL，那么当我们插入的数据值1234的时候，它会在1234前面补上0，显示为01234，仅此而已。

显示宽度只用于显示，并不能限制取值范围和占用空间。
如：int(3) 它也会占用 4 个字节(byte)的存储空间，并且它允许的最大值也不会是999，而是int 整型所允许的取值范围有符号的时候为[-2147483648,2147483647]或者无符号的时候为[0,4294967295]。

那么int(4)、int(8)、int(11) 分别占用几个字节 ？他们也都是占用4个字节byte，他们的取值范围也都是[-2147483648,2147483647]有符号或者[0,4294967295]无符号，和int不指定长度一样。

所以我们使用int类型的变量的时候，直接使用tinyint、smallint、mediumit、int、bigint中的某一种就可以，具体使用哪一种根据自己的业务量来定，而不需要为期指定长度。除非你的业务需求中需要在不足数据位数的时候，在前面补0，但是这个功能需要在定义字段的时候结合UNSIGNED ZEROFILL关键字一起使用才有效果。

表自增主键的自增值

如果一张表的自增ID用完之后，我们再次向这个表中插入数据会怎么样呢？我们使用tinyint类型的自增主键举例举例来实验一下。

1. 创建测试的表

mysql> CREATE TABLE `t` (
    ->   `id` tinyint unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=254 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.02 sec)

1. 插入测试数据

mysql> insert into t(id) values (null);
Query OK, 1 row affected (0.01 sec)

1. 再次插入数据

mysql> insert into t(id) values (null);
ERROR 1062 (23000): Duplicate entry '255' for key 'PRIMARY'
mysql>

1. 查看表中自增值是多少

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=255 DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)

1. 查看表中的数据，结果如下

mysql> select * from t;
+-----+
| id  |
+-----+
| 255 |
+-----+
1 row in set (0.02 sec)

整个过程截图如下，从中我可以可以看出，当自增主键的值，达到最大值之后，我们再次向表中插入数据的时候。自增键的自增值将不会再次增加，一直保持最大值不在变化，我们获取到的自增值也一直是最大值。所以当我们再次插入数据的时候回出现主键冲突的错误。

如果我们的业务流量比较大，担心自增值会被用完，我们可以把自增键的字段类型设置为8个byte的bigint，这个类型的值，在理论上是不会用完的，但是与此同时，你要付出的存储空间也会别int大一倍。这样就可以避免因为自增主键的自增值被用完后，再次插入数据的时候查询主键冲突错误信息。

MySQL全局的自增值row_id

我们在创建表的时候，如果不为表指定任何主键，那么MySQL会给这个表创建一个隐藏的自增ID主键，并且这个隐藏的自增ID的取值是从一个全局变量dict_sys.row_id中获取。这个变量是所有没有主键的表共享的。

这个变量占用6个byte，它的取值范围是$2^{48}-1$，因为这个值对所有没有主键的表共享，如果你的MySQL数据库中，有很多没有主键的表，并且有很多的数据在这些表中，那么这个值是有可能达到最大值的。

如果这个全局变量的值达到了最大值，它就会从0开始从新开始计算。这就导致了没有主键的表中的数据可能会被覆盖的可能性。试想一下，如果一个表没有主键只有一列varchar类型的字段col_a，我们想里面插入数据的时候。当插入到最大行的时候，它会从0开始计算，此时我们插入$an+1$的时候，就会回到第一行$a1$的这个行上，会把$a1$这个行的数据内容被覆盖为$an+1$，以此类推，$a2$会被$an+2$覆盖掉。

row_id隐藏列	col_a
1	$a1$
2	$a2$
…	…
$2^{48}-1$	$an$
1	$an+1$

所以建议所有的表都要设置一个主键，避免这个隐藏的全局自增值到达最大的$2^{48}-1$之后会覆盖掉之前插入的数据。有了自增主键，即便是超过了自增值，在插入数据的时候，会有主键冲突的错误，这比不通知我们直接把数据给覆盖掉要好很多。

Xid

在MySQL的innodb数据表进行更新操作的时候，会涉及到redolog的两阶段提交和binlog日志的配合。以此来达到数据在逻辑上的一致性，从而保证了在MySQL数据库崩溃异常重启后，innodb表可以恢复已经正常提交的事务，这也就是我们经常所说的innodb的crash-safe的能力。

Xid是有MySQL的Server层维护的。

Xid是binlog文件中常见的一个ID，因为binlog是server层维护的日志，所以Xid也是由MySQL的Server层维护的。它在binlog文件中标识一个唯一的事务。

但是在不同的binlog文件中，这个Xid是有可能相同的。因为这个ID是来自于MySQL执行各种SQL语句的时候的查询编号，MySQL在为所有的SQL语句会分配一个唯一的编号，这个编号来自于全局变量：global_query_id。而global_query_id，它是维护在内存当中。它是占8个字节的bigint类型，最大值为：$2^{64}-1$。这就意味着，如果MySQL重启了，那么这个变量的值将会丢失，重启后这个值将会重新从0开始累加。

所以SQL语句的编号将会重新从0开始累加，这个查询语句的编号会赋值给对应的事务编号，但是binlog文件再MySQL重启后，会重新使用新的binlog日志文件。所以在同一个日志文件中，Xid是不可能相同的。

说Xid在同一个binlog日志文件中不可能相同的说法也不算太严谨，因为如果这个global_query_id达到最大值$2^{64}-1$之后，从新从0累计也有可能导致同一个binlog文件中的Xid的值重复。但是这个可能性几乎为0，因为我们的binlog日志文件在达到一定的大小后也会重新开启一个新的binlog日志文件。这个是有参数max_binlog_size控制的。

Innodb的事务ID

InnoDB的事务ID是指：trx_id。

和Xid不同，trx_id是由InnoDB引擎自己维护的。它的最大值为$2^{48}-1$。如果到达它的最大值之后，会从0开始累加。这个值再MySQL重启之后不会清零，它做了持久化的操作，所以重启后的MySQL事务ID是可以累积上一次的值的。

这可能潜在的隐藏一个bug，如果trx_id到达最大之后，重新从0累加，这就导致了事务的id重复了，这样在MySQL的MVCC多版本数据控制和一致性事务读取的时候，就可能会发生脏读。但是可以忽略这个bug，因为这个值已经很大了，不会那么快就出现这个bug。

trx_id的值来自于innodb内部自己维护的max_trx_id全局变量。每次需要申请新的trx_id的时候，就获得当前max_trx_id的值，然后再把max_trx_id的值加1为下次准备。注意：只读事务不会占用max_trx_id的值。

对于正在执行的事务，可以在information_schema.innodb_trx表中看到对应的事务信息，已经当前事务trx_id的值。

在MySQL的MVCC多版本控制的一致性事务视图在实现的过程中，就依赖于这个trx_id的值，因为它代表了每一行被修改数据的版本号，在每一行数据被修改后，都会拿当前修改这一行数据的事务的trx_id作为当前数据的版本号。当一个事务读到一行数据的时候，判断这个数据是否可见的方法，就是通过事务的一致性视图与这行数据的 trx_id 做对比。

线程ID

线程ID是指：thread_id，我们平时执行show processlist;命令的时候就可以显示出这个线程ID。如下所示：

thread_id的取值来自于系统保存的一个全局变量thread_id_counter，每新建一个连接，就将 thread_id_counter 赋值给这个新连接的线程变量。

它的大小是4个字节，最大值为：$2^{32}-1$，到达最大值之后，他会重新从0累加。但是它也不会重复，因为他们使用了唯一数组的设计理念，如下：

do {
  new_id= thread_id_counter++;
} while (!thread_ids.insert_unique(new_id).second);

总结

• 表的自增 id 达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。
• row_id 达到上限后，则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据。
• Xid 只需要不在同一个 binlog 文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计。InnoDB 的 max_trx_id 递增值每次 MySQL 重启都会被保存起来，所以我们文章中提到的脏读的例子就是一个必现的 bug，好在留给我们的时间还很充裕。
• thread_id 是我们使用中最常见的，而且也是处理得最好的一个自增 id 逻辑了。

---