MYSQL中的auto

it2022-05-07  41

 在MySQL中,经常使用 AUTO_INCREMENT对主键建立自增id的行为,MySQL会自行保证主键ID的自增与不重复,使程序不需对ID进行关注。

但是在使用时,MySQL对auto_increment对管理的一些特性可能会导致一些问题,所以需要手工对其进行修改。

修改AUTO_INCREMENT,MySQL只需一条非常简单的命令:

ALTER TABLE t1 AUTO_INCREMENT = 13;

需要注意,在InnoDB引擎下,如果修改的目标值小于当前数据库中ID的最大值,则数据库不会有任何变化。

在MyISAM引擎下,如果修改的目标值小于当前数据库中ID的最大值,则AUTO_INCREMENT会被设置为该最大值。

 

在前几天的工作中,为了保证ID的范围,对数据库的AUTO_INCREMENT进行了修改,发现了一些问题。

当在使用ALTER TABLE mytable AUTO_INCREMENT = N修改AUTO_INCREMENT值时,

即使没有对数据结构进行变化,MySQL服务器(操作的服务器为MySQL5.5,innodb引擎)也会自动建立一个新的表,然后将所有数据复制到新表中,

再将旧表删除,并重命名新表。

同时,在返回的结果中可以看到,所有的行都被affected。

例如:

mysql> alter table mytable auto_increment=1000000;Query OK, 512691 rows affected (1 min 4.55 sec)Records: 512691 Duplicates: 0 Warnings: 0

 

经过查询,发现这是在使用ALTER TABLE命令时常见的情况,同时AUTO_INCREMENT在MySQL5.5中不属于可以跳过重建操作的命令

这就带来一个问题,在进行修改AUTO_INCREMENT时,整个表会被加锁,不能修改,影响正常服务。如果表里的数据量非常大,则复制数据会相当的花时间。

同时也可能带来其他的不可预知的问题,例如硬盘空间被零时表大量占用的问题。

 

在查询了MySQL官方文档与相关资料后发现了一些可以快速解决的方案:

1、如果需要将AUTO_INCREMENT变大,则可以通过很方便的手段进行操作,首先插入一行傀儡数据,插入时将该行数据的ID设置为AUTO_INCREMENT的目标值-1。

进行此操作后,则AUTO_INCREMENT会自动变为你嗦需要设置的目标值。

但是这种方法仅适用于将AUTO_INCREMENT改大的情况,无法将AUTO_INCREMENT改小。

 

2、如果需要快速的将AUTO_INCREMENT改小,则需要利用innodb的特性。

在InnoDB中,使用的是内存自增计数器,也就是说,AUTO_INCREMENT不会写入到硬盘中。

每次重启数据库服务后,MySQL会自动运行命令

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

以获取最大的ID,并赋值到AUTO_INCREMENT中作为计数器的新值。

利用这一点,我们就可以快速的修改AUTO_INCREMENT的值:首先删除所有比目标值大的ID的行,然后重启数据库,再使用之前改大的方法设置AUTO_INCREMENT

转载于:https://www.cnblogs.com/ruizhang3/p/6583141.html


最新回复(0)