首先,创建一个最简单的表,只包含一个自增id,并插入一条数据。
create table test0(id int unsigned auto_increment primary key) ;
insert into test values(null);
通过show命令 show create table test0; 查看表情况
CREATE TABLE `test0` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
可以发现 AUTO_INCREMENT 已经自动变成2,这离用完还有很远,我们可以算下最大当前声明的自增ID最大是多少,由于这里定义的是 intunsigned,所以最大可以达到2的32幂次方 - 1 = 4294967295
这里有个小技巧,可以在创建表的时候,直接声明AUTO_INCREMENT的初始值
create table test1(id int unsigned auto_increment primary key) auto_increment = 4294967295;insert into t1 values(null);
同样,通过show命令,查看t1的表结构
CREATE TABLE `test1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8
可以发现,AUTO_INCREMENT已经变成4294967295了,当想再尝试插入一条数据时,得到了下面的异常结果
17:28:03 insert into t1 values(null) Error Code: 1062. Duplicate entry '4294967295' for key 'PRIMARY' 0.00054 sec
说明,当再次插入时,使用的自增ID还是 4294967295,报主键冲突的错误。
对于大多数场景来说,4294967295这个数字已经足够应付需求。然而,如果您的服务经常性地处理大量数据并进行频繁的插入和删除操作,那么确实存在使用完该数字范围的风险。在这种情况下,采用bigint unsigned数据类型会更合适,因为它提供了更大的数字范围,可以满足更高的需求。
(此处已添加书籍卡片,请到今日头条客户端查看)
InnoDB在处理未定义主键的表时会自动创建一个不可见的6字节row_id,并维护一个全局的dictsys.row_id。每次插入数据时,都将全局row_id作为主键id,并将全局row_id递增1。
然而,这种设计可能存在一个潜在的问题。如果全局row_id一直增加,直到达到2的48次方减1时,再次递增1会导致row_id的低48位变为0。这样,在插入新行数据时,可能会遇到与已有行数据的主键冲突的情况。
为了避免这种潜在问题,确实建议为每个表定义一个主键。通过明确定义主键,您可以确保每个插入的数据行都有唯一的标识,避免主键冲突的可能性。