您的位置:首页 - 教程 - MYSQL - 正文
MYSQL数据库中的auto_increment
在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



MySQL 的语法
下列 SQL 语句把 "Persons" 表中的 "P_Id" 列定义为 auto-increment 主键:

CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。

默认地,AUTO_INCREMENT 的开始值是 1,每条新记录递增 1。

要让 AUTO_INCREMENT 序列以其他的值起始,请使用下列 SQL 语法:

ALTER TABLE Persons AUTO_INCREMENT=100
要在 "Persons" 表中插入新记录,我们不必为 "P_Id" 列规定值(会自动添加一个唯一的值):

INSERT INTO Persons (FirstName,LastName)
VALUES ('Bill','Gates')
上面的 SQL 语句会在 "Persons" 表中插入一条新记录。"P_Id" 会被赋予一个唯一的值。"FirstName" 会被设置为 "Bill","LastName" 列会被设置为 "Gates"。

评论: