mysql使用varchar类型为主键自增

转载

mob64ca14163a4f 2024-07-08 11:57:20

使用自增主键的目的： 可以让数据插入数据页时尽量保持按递增顺序插入，使得数据页数据更加紧凑，避免页分裂。

我们会讨论一下问题：自增主键如何保存？

我们先来建个表：

create table t3(
 	id int(11) not null auto_increment PRIMARY key,
 	a int(11) not null,
	b int(11) not null,
	UNIQUE key a(a)
)ENGINE = INNODB

38.1 自增主键如何保存

表的结构定义语句保存在.fm的文件中，并不会保存自增主键的值。

不同的引擎保存自增主键的方式也不一样：

MyISAM：自增主键和表数据文件保存在一起
InnoDB：分为MySQL 8.0之前和MySQL 8.0

MySQL 8.0 之前：自增主键的值保存在内存中，如果我们将表的最大一行删除并重启MySQL，会发现自增主键改变了，使用show create table tableName来查看自增主键的值。
MySQL 8.0 ：自增主键的值写入redo log，重启可以依靠redo log来进行恢复。

查看方式如下：

mysql使用varchar类型为主键自增_MySQL

38.2 自增值修改机制

如果一个字段被设为auto_increment ，那么在插入数据的时候会出现如下情况：

如果字段的值为0，null或者空，则将表的自增值替换该值。
如果字段不为上述情况，则使用。

下面就是如何去计算一个自增值了，这里涉及到两个参数auto_increment_offset和auto_automent_increment，分别表示自增值的初始值和自增值的步长，默认为1。

我们将插入的值设为X，自增值设为Y：

如果X<Y，那么自增值不变
如果X>=Y，那么会产生新的自增值，新的自增值等于X+步长。

38.3 自增值的修改时机

先假设我们去插入一条数据：

insert into t values(null, 2, 2);

语句的执行流程如下：

执行器调用InnoDB引擎接口写入一行数据(0,1,1)
InnoDB发现没有指定自增id的值，将表t的自增值2写入并更新自增值为3，修改后的数据为(2,2,2)
继续执行插入数据操作

38.4 自增值不连续的情况

1.唯一键冲突：

接着上面的情况，我们的表t中的a字段为唯一索引，当出现唯一索引出现的时候，再插入下个数据，就会发现自增值已经为3了，这就是第一种情况。

2.事务回滚：

在多事务并发情况下，有一个事务在最后关头发生错误，进行了回滚，也会导致自增主键出现断层。

3.自增主键的批量申请

当MySQL遇到insert…select、replace…select和load data语句时，它就会使用批量申请。

第一次申请1个，第二次申请2个，第三次申请4个，后面会一直翻倍。

如下情况：

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table t2 like t;
# 第一次：1，第二次：2~3，第三次：4~7
insert into t2(c,d) select c,d from t;
insert into t2 values(null, 5,5);

第三次申请的自增值为4_{7，但是使用了一个5}7是浪费的，也是空缺的。