你需要为什么创建缺口?要保留ID?我宁愿不惜一切代价“修复”设计并更新其他模块,而不是摸一个序列。
我暗示着通过为每个ID插入一个默认行(标记为无效)来分配并返回该ID,而不是明确地增加序列。这种方法是一致且可移植的。以后,你可以通过匹配的默认值来更新这些默认行,而不必使用显式的序列值来强制插入。这需要更多的内存,但没有锁。在过期的行上进行垃圾收集可以在这里提供帮助。“插入或更新”语句可以重新创建垃圾收集的行,但是我不会这样做。
3.6.9。使用AUTO_INCREMENT
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
Which returns:
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
没有为AUTO_INCREMENT列指定任何值,因此MysqL自动分配了序列号。你还可以为该列显式分配NULL或0以生成序列号。
你可以使用LAST_INSERT_ID()sql函数或MysqL_insert_id()C API函数检索最新的AUTO_INCREMENT值。这些函数是特定于连接的,因此它们的返回值不受也执行插入操作的另一个连接的影响。
对AUTO_INCREMENT列使用最小的整数数据类型,该数据类型应足够大以容纳所需的最大序列值。当列达到数据类型的上限时,下一次生成序列号的尝试将失败。如果可能,请使用UNSIGNED属性,以允许更大的范围。例如,如果使用tinyint,则最大允许序列号为127。对于tinyint UNSIGNED,最大数量为255。请参见第11.2.1节“整数类型(精确值)-INTEGER,INT,SMALLINT,tinyint,MEDIUMINT,BIGINT ”表示所有整数类型的范围。
注意对于多行插入,LAST_INSERT_ID()和MysqL_insert_id()实际上从插入的第一行返回AUTO_INCREMENT键。这样可以在复制设置中的其他服务器上正确地复制多行插入。
如果AUTO_INCREMENT列是多个索引的一部分,则MysqL使用从AUTO_INCREMENT列开始的索引(如果有的话)生成序列值。例如,如果动物表包含索引PRIMARY KEY(grp,id)和INDEX(id),则MysqL将忽略PRIMARY KEY来生成序列值。结果,该表将包含单个序列,而不是每个grp值的序列。
要以非1的AUTO_INCREMENT值开头,请使用CREATE TABLE或ALTER TABLE设置该值,如下所示:
MysqL> ALTER TABLE tbl AUTO_INCREMENT = 100; InnoDB注意事项
对于InnoDB表,如果要修改在INSERT语句序列中间包含自动增量值的列,请小心。例如,如果使用UPDATE语句在自动增量列中放置一个较大的新值,则后续的INSERT可能会遇到“重复项”错误。如果执行DELETE后再执行更多INSERT语句,或者当你提交事务时(而不是在UPDATE语句之后),则测试是否已经存在自动增量值。
MyISAM笔记
对于MyISAM表,可以在多列索引的第二列上指定AUTO_INCREMENT。在这种情况下,将为AUTO_INCREMENT列生成的值计算为MAX(auto_increment_column)+ 1 WHERE prefix = given-prefix。当你要将数据放入有序组中时,这很有用。
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
) ENGINE=MyISAM;
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
Which returns:
+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+
在这种情况下(当AUTO_INCREMENT列是多列索引的一部分时),如果删除任何组中具有最大AUTO_INCREMENT值的行,则将重用AUTO_INCREMENT值。即使对于MyISAM表,通常不会重复使用AUTO_INCREMENT值,也会发生这种情况。
进一步阅读
有关AUTO_INCREMENT的更多信息,请参见:
如何将AUTO_INCREMENT属性分配给列:第13.1.17节“ CREATE TABLE语法”和第13.1.7节“ ALTER TABLE语法”。
AUTO_INCREMENT的行为取决于NO_AUTO_VALUE_ON_ZERO sql模式:第5.1.7节“服务器sql模式”。
如何使用LAST_INSERT_ID()函数查找包含最新AUTO_INCREMENT值的行:第12.14节“信息函数”。
设置要使用的AUTO_INCREMENT值:第5.1.4节“服务器系统变量”。
AUTO_INCREMENT和复制:第16.4.1.1节“复制和AUTO_INCREMENT”。
可以用于复制的与AUTO_INCREMENT相关的服务器系统变量(auto_increment_increment和auto_increment_offset):第5.1.4节“服务器系统变量”。