的中AUTO_INCREMENT类型的属性用于为一个表中记录自动生成ID功能,可在一定程度上代替,PostgreSQL等中的sequence。
在数据库应用,我们经常要用到唯一编号,以标识记录。在MySQL中可通过数据列的AUTO_INCREMENT属性来自动生成。
当插入记录时,如果为AUTO_INCREMENT数据列明确指定了一个数值,则会出现两种情况,
情况一,如果插入的值与已有的编号重复,则会出现出错信息,因为AUTO_INCREMENT数据列的值必须是唯一的;
情况二,如果插入的值大于已编号的值,则会把该插入到数据列中,并使在下一个编号将从这个新值开始递增。也就是说,可以跳过一些编号。
如果自增序列的最大值被删除了,则在插入新记录时,该值被重用。
如果用UPDATE命令更新自增列,如果列值与已有的值重复,则会出错。如果大于已有值,则下一个编号从该值开始递增。
AUTO_INCREMENT是数据列的一种属性,只适用于整数类型数据列。设置AUTO_INCREMENT属性的数据列应该是一个正数序列,所以应该把该数据列声明为UNSIGNED,这样序列的编号个可增加一倍。AUTO_INCREMENT数据列必须有唯一索引,以避免序号重复(即是主键或者主键的一部分)。AUTO_INCREMENT数据列必须具备NOT NULL属性。AUTO_INCREMENT数据列序号的最大值受该列的数据类型约束,如TINYINT数据列的最大编号是127,如加上UNSIGNED,则最大为255。一旦达到上限,AUTO_INCREMENT就会失效。当进行全表删除时,MySQL AUTO_INCREMENT会从1重新开始编号。全表删除的意思是发出以下两条语句时:
这是因为进行全表操作时,MySQL(和PHP搭配之最佳组合)实际是做了这样的优化操作:先把数据表里的所有数据和索引删除,然后重建数据表。
如果想删除所有的数据行又想保留序列编号信息,可这样用一个带where的delete命令以抑制MySQL(和PHP搭配之最佳组合)的优化:
因此在使用INSERT…SELECT、INSERT…values(…),values(…)时,LOAD DATA等耗费时间较长的操作时,会将整个表锁住,而阻塞其他的“INSERT-like”、Update等语句,推荐使用程序将这些语句分成多条语句,一一插入,减少单一时间的锁表时间。mysql5.1.22之后mysql进行了改进,引入了参数 mode,连续模式)。这种方式是新版本中的默认方式,推荐使用,并发性相对较高,特点是“consecutive”,即保证同一条insert语句中新插入的auto_increment id都是连续的。这种模式下:“Simple inserts”:直接通过分析语句,获得要插入的数量,然后一次性分配足够的auto_increment id,只会将整个分配的过程锁住。“Bulk inserts”:因为不能确定插入的数量,因此使用和以前的模式相同的表级锁定。“Mixed-mode inserts”:直接分析语句,获得最坏情况下需要插入的数量,然后一次性分配足够的auto_increment id,只会将整个分配的过程锁住。需要注意的是,这种方式下,会分配过多的id,而导致“浪费”。比如INSERT INTO t1 (c1,c2) VALUES (1,’a’), delete(update多条语句,delete多条语句)时,日志会比SBR大很多;假如实际语句中这样语句不是很多的时候(现实中存在很多这样的情况),推荐使用RBR配合innodb_autoinc_lock_mode,不过话说回来,现实生产中“Bulk inserts”本来就很少,因此innodb_autoinc_lock_mode = 1应该是够用了。