mysql自增主键和uuid_mysql8.2和8.0的区别

(2) 2024-06-16 15:12

Hi,大家好,我是编程小6,很荣幸遇见你,我把这些年在开发过程中遇到的问题或想法写出来,今天说一说
mysql自增主键和uuid_mysql8.2和8.0的区别,希望能够帮助你!!!。

自增主键

自增值的存储

MyISAM引擎将当前自增值存储在表数据文件中。

InnoDB引擎在5.7及之前将当前自增值存储在内存中,MySQL重启时从表中查询自增列最大值+步长作为当前自增值。

InnoDB引擎在8.0及之后版本中将自增值变动记录存储在redo log中,重启MySQL后根据redo log恢复之前的自增值。

自增值的变化

执行插入语句时,如果自增列的值不指定,或指定为0或null,则插入时将表当前自增值填入该列,并增长自增值(当前自增值+步长)。

执行插入语句时,如果自增列指定了某个值,则使用指定的值。然后从当前自增值开始循环+步长,选择第一个大于指定值的数值作为新的当前自增值。

即:如果设置自增从1开始,步长10,则自增值依次是1、11、21。如果插入了一条自增列为37的记录,则下次获取到的自增值是41,因为41是21之后第一个大于37的自增值。

-- 查看自增值与自增步长

SHOW VARIABLES LIKE 'auto_inc%';

-- 设置自增步长,重启MySQL后失效

SET @@auto_increment_increment = 10;

-- 查询上次获取到的自增ID

SELECT last_insert_id();

自增列不连续的情况发生事务回滚时。因为每次取自增值都会变化,事务回滚时并不会撤回已经被取走的自增值,因此当发生事务回滚时会发生自增列不连续的情况。

发生唯一键冲突时。发生唯一键冲突之前,首先会获取当前自增值填入自增列,然后由于发生唯一键冲突,导致插入失败,但此时自增值已经发生了变化,会导致不连续。

insert...select语句执行时。insert...select语句执行时如果插入多行,会批量申请自增值,第一次申请1个,第二次申请2个,第三次申请4个,第N次申请2^(N-1)个。所以如果第N次申请了多个值,但是没有用掉的话,也会造成自增列不连续。

比如insert...select插入了4行,自增列自增值从1开始。首先申请1个,然后申请2个,再申请3个。此时共申请了7个自增值,但是只插入了5行数据,表中自增列最大值为5,但当前自增值是8,下次插入的行自增列会是8。

是所有插入失败都会导致不连续吗?不是。如果是列的值超出范围,这个是在插入之前检查的,此时还没有申请自增值,所以不会改变当前自增值,所以不会造成不连续

自增列的锁

自增列的锁并非事务锁,申请完拿到自增值后立即释放,而不是等到事务提交。

MySQL5.0之前,自增锁是与语句相关的,如果在插入语句中用到自增锁,会等到语句执行完成后才释放。

MySQL5.1及之后,添加了一个参数innodb_autoinc_lock_mode配置,用于控制自增锁行为:值为0,表示采用MySQL5.0之前的策略,语句执行完成后释放;

值为1,普通insert语句在申请用完之后立即释放;批量插入语句如insert多个values或insert...select语句,还是要等到语句执行完成后释放;

值为2,所有申请自增主键的动作都是申请用完后释放。

默认值是1。

自增列导致的主从数据不一致问题

当binlog_format设置为STATMENT时,binlog中记录的是每次执行的修改数据SQL。此时如果插入数据未指定自增列值而是使用自动获取的话,可能发生主从数据不一致问题。

如:自增值从1开始,步长1。事务A插入两条数据,自增列值分别为1、2。

此时开启事务B,插入一条数据,然后回滚。

然后事务A再插入一条数据,自增列值为4。

提交事务A。

此时主库中数据为1、2、4。而同步到从库后执行插入语句,插入的数据为1、2、3,主从数据不一致。

解决思路:避免从库自动得到自增列ID。

解决办法:插入时指定ID。

将binlog_fomat设置为row。

自增列值用完了

会继续使用上一次生成的自增值。也就是自增值不再增长。

last_insert_id()问题

查询上一次递增ID的值:

select last_insert_id();当在不同事务中各执行一条插入语句时,返回的是各自事务中生成的ID;

当在同一个事务中多次执行插入语句时,会返回最后一次插入语句的ID;

当在一个SQL语句中批量插入多条数据时,返回的是批量插入的第一条数据的ID;

当在不同事务中各执行一条插入语句时,返回的是各自事务中生成的ID。

mysql> begin;

Query OK, 0 rows affected (0.05 sec)

mysql> insert into t(c, d) values(5, 6);

Query OK, 1 row affected (0.11 sec)

mysql> select last_insert_id();

+------------------+

| last_insert_id() |

+------------------+

| 8 |

+------------------+

1 row in set (0.06 sec)

mysql> begin;

Query OK, 0 rows affected (0.05 sec)

mysql> insert into t(c, d) values(6, 4);

Query OK, 1 row affected (0.05 sec)

mysql> select last_insert_id();

+------------------+

| last_insert_id() |

+------------------+

| 9 |

+------------------+

1 row in set (0.06 sec)

当在同一个事务中多次执行插入语句时,会返回最后一次插入语句的ID。

mysql> begin;

Query OK, 0 rows affected (0.05 sec)

mysql> insert into t(c, d) values(6, 4);

Query OK, 1 row affected (0.05 sec)

mysql> select last_insert_id();

+------------------+

| last_insert_id() |

+------------------+

| 9 |

+------------------+

1 row in set (0.06 sec)

mysql> insert into t(c, d) values(76, 1);

Query OK, 1 row affected (0.06 sec)

mysql> select last_insert_id();

+------------------+

| last_insert_id() |

+------------------+

| 14 |

+------------------+

1 row in set (0.05 sec)

当在一个SQL语句中批量插入多条数据时,返回的是批量插入的第一条数据的ID。

mysql> insert into t(c, d) values(76, 1);

Query OK, 1 row affected (0.06 sec)

mysql> select last_insert_id();

+------------------+

| last_insert_id() |

+------------------+

| 14 |

+------------------+

1 row in set (0.05 sec)

mysql> insert into t(c, d) values(22, 1), (23, 1), (24, 1);

Query OK, 3 rows affected (0.05 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select last_insert_id();

+------------------+

| last_insert_id() |

+------------------+

| 15 |

+------------------+

1 row in set (0.05 sec)

mysql> commit;

Query OK, 0 rows affected (0.06 sec)

今天的分享到此就结束了,感谢您的阅读,如果确实帮到您,您可以动动手指转发给其他人。

上一篇

已是最后文章

下一篇

已是最新文章

发表回复