快上网专注成都网站设计 成都网站制作 成都网站建设
成都网站建设公司服务热线：028-86922220

网站建设知识

十年网站开发经验 + 多家企业客户 + 靠谱的建站团队

量身定制 + 运营维护+专业推广+无忧售后，网站问题一站解决

mysql的auto_increment详解

转载http://blog.csdn.net/fwkjdaghappy1/article/details/7663331
  • auto_increment的基本特性

创新互联公司为您提适合企业的网站设计 让您的网站在搜索引擎具有高度排名，让您的网站具备超强的网络竞争力！结合企业自身，进行网站设计及把握，最后结合企业文化和具体宗旨等，才能创作出一份性化解决方案。从网站策划到成都网站建设、成都做网站， 我们的网页设计师为您提供的解决方案。

MySQL的中AUTO_INCREMENT类型的属性用于为一个表中记录自动生成ID功能，可在一定程度上代替Oracle，PostgreSQL等数据库中的sequence。

在数据库应用，我们经常要用到唯一编号，以标识记录。在MySQL中可通过数据列的AUTO_INCREMENT属性来自动生成。

可在建表时可用“AUTO_INCREMENT=n”选项来指定一个自增的初始值。
可用alter table table_name AUTO_INCREMENT=n命令来重设自增的起始值。

当插入记录时，如果为AUTO_INCREMENT数据列明确指定了一个数值，则会出现两种情况，
情况一，如果插入的值与已有的编号重复，则会出现出错信息，因为AUTO_INCREMENT数据列的值必须是唯一的；
情况二，如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。
如果自增序列的最大值被删除了，则在插入新记录时，该值被重用。
如果用UPDATE命令更新自增列，如果列值与已有的值重复，则会出错。如果大于已有值，则下一个编号从该值开始递增。

在使用AUTO_INCREMENT时，应注意以下几点：
AUTO_INCREMENT是数据列的一种属性，只适用于整数类型数据列。
设置AUTO_INCREMENT属性的数据列应该是一个正数序列，所以应该把该数据列声明为UNSIGNED，这样序列的编号个可增加一倍。
AUTO_INCREMENT数据列必须有唯一索引，以避免序号重复(即是主键或者主键的一部分)。
AUTO_INCREMENT数据列必须具备NOT NULL属性。
AUTO_INCREMENT数据列序号的最大值受该列的数据类型约束，如TINYINT数据列的最大编号是127,如加上UNSIGNED，则最大为255。一旦达到上限，AUTO_INCREMENT就会失效。
当进行全表删除时，MySQL AUTO_INCREMENT会从1重新开始编号。全表删除的意思是发出以下两条语句时：

[php] view plain
  • delete from table_name;  
  • 或者  
  • truncate table table_name   
    • 这是因为进行全表操作时，MySQL(和PHP搭配之最佳组合)实际是做了这样的优化操作：先把数据表里的所有数据和索引删除，然后重建数据表。
    如果想删除所有的数据行又想保留序列编号信息，可这样用一个带where的delete命令以抑制MySQL(和PHP搭配之最佳组合)的优化：

    [php] view plain
  • delete from table_name where 1;   

    • 可用last_insert_id（）获取刚刚自增过的值。

    • 关于mysql auto_increment所带来的锁表操作

    在mysql5.1.22之前，mysql的“INSERT-like”语句（包INSERT, INSERT…SELECT, REPLACE,REPLACE…SELECT, and LOAD DATA)会在执行整个语句的过程中使用一个AUTO-INC锁将表锁住，直到整个语句结束（而不是事务结束）。
    因此在使用INSERT…SELECT、INSERT…values(…),values(…)时，LOAD DATA等耗费时间较长的操作时，会将整个表锁住，而阻塞其他的“INSERT-like”、Update等语句，推荐使用程序将这些语句分成多条语句，一一插入，减少单一时间的锁表时间。
    mysql5.1.22之后mysql进行了改进，引入了参数 innodb_autoinc_lock_mode，通过这个参数控制mysql的锁表逻辑。
    在介绍这个之前先引入几个术语，方便说明 innodb_autoinc_lock_mode。
    1.“INSERT-like”：
    INSERT, INSERT … SELECT, REPLACE, REPLACE … SELECT, and LOAD DATA, INSERT … VALUES(),VALUES()
    2.“Simple inserts”：
    就是通过分析insert语句可以确定插入数量的insert语句, INSERT, INSERT … VALUES(),VALUES()
    3.“Bulk inserts”：
    就是通过分析insert语句不能确定插入数量的insert语句, INSERT … SELECT, REPLACE … SELECT, LOAD DATA
    4.“Mixed-mode inserts”：
    不确定是否需要分配auto_increment id，一般是下面两种情况
    INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');
    INSERT … ON DUPLICATE KEY UPDATE

    一、innodb_autoinc_lock_mode = 0 (“traditional” lock mod，传统模式)。
    这种方式就和mysql5.1.22以前一样，为了向后兼容而保留了这种模式，如同前面介绍的一样，这种方式的特点就是“表级锁定”，并发性较差。
    二、innodb_autoinc_lock_mode = 1 (“consecutive” lock 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'), (NULL,’b'), (5,’c'), (NULL,’d');会一次性的分配5个id，而不管用户是否指定了部分id；
    INSERT … ON DUPLICATE KEY UPDATE一次性分配，而不管将来插入过程中是否会因为duplicate key而仅仅执行update操作。
    注意：当master mysql版本<5.1.22，slave mysql版本>=5.1.22时，slave需要将innodb_autoinc_lock_mode设置为0，因为默认的innodb_autoinc_lock_mode为1，对于INSERT … ON DUPLICATE KEY UPDATE和INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');的执行结果不同，现实环境一般会使用INSERT … ON DUPLICATE KEY UPDATE。
    三、innodb_autoinc_lock_mode = 2 (“interleaved” lock mode，交叉模式)。
    这种模式是来一个分配一个，而不会锁表，只会锁住分配id的过程，和innodb_autoinc_lock_mode = 1的区别在于，不会预分配多个，这种方式并发性最高。
    但是在replication中当binlog_format为statement-based时（简称SBR statement-based replication）存在问题，因为是来一个分配一个，这样当并发执行时，“Bulk inserts”在分配的时会同时向其他的INSERT分配，会出现主从不一致（从库执行结果和主库执行结果不一样），因为binlog只会记录开始的insert id。
    测试SBR，执行begin;insert values(),();insert values(),();commit;会在binlog中每条insert values(),();前增加SET INSERT_ID=18/*!*/;。
    但是row-based replication RBR时不会存在问题。
    另外RBR的主要缺点是日志数量在包括语句中包含大量的update delete（update多条语句，delete多条语句）时，日志会比SBR大很多；假如实际语句中这样语句不是很多的时候（现实中存在很多这样的情况），推荐使用RBR配合innodb_autoinc_lock_mode，不过话说回来，现实生产中“Bulk inserts”本来就很少，因此innodb_autoinc_lock_mode = 1应该是够用了。


    最后说一句今天遇到这个问题，在LOCK_MODE为AUTO_INC，而且事物回滚非常的慢，不得已停止数据库重新启动
    mysql> select * from innodb_locks;
    +-------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
    | lock_id     | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index | lock_space | lock_page | lock_rec | lock_data |
    +-------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
    | 2954466:518 | 2954466     | AUTO_INC  | TABLE     | `test`.`kkkm` | NULL       |       NULL |      NULL |     NULL | NULL      |
    | 2954465:518 | 2954465     | AUTO_INC  | TABLE     | `test`.`kkkm` | NULL       |       NULL |      NULL |     NULL | NULL      |
    +-------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+

    当前标题：mysql的auto_increment详解
    当前网址：http://6mz.cn/article/jhcijg.html

    其他资讯