mysql的auto_increment详解

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 转载http://blog.csdn.net/fwkjdaghappy1/article/details/7663331 auto_increment的基本特性 MySQL的中AUTO_INCREMENT类型的属性用于为一个表中记录自动生成ID功能,可在一定程度上代替Oracle,PostgreSQL等数据库中的sequence。

转载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  copy
  1. delete from table_name;  
  2. 或者  
  3. truncate table table_name   
这是因为进行全表操作时,MySQL(和PHP搭配之最佳组合)实际是做了这样的优化操作:先把数据表里的所有数据和索引删除,然后重建数据表。
如果想删除所有的数据行又想保留序列编号信息,可这样用一个带where的delete命令以抑制MySQL(和PHP搭配之最佳组合)的优化:


[php]  view plain  copy
  1. 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      |
+-------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
24天前
|
关系型数据库 MySQL 索引
【MySQL进阶之路 | 基础篇】约束之主键约束与AUTO_INCREMENT
【MySQL进阶之路 | 基础篇】约束之主键约束与AUTO_INCREMENT
|
1月前
|
存储 SQL 关系型数据库
MySQL 5.7和 MySQL8.0 InnoDB auto_increment 初始化的区别
在MySQL 5.7及之前,自动递增计数器只存于内存,重启后需通过查询确定初始值。从MySQL 8.0开始,计数器变化时写入重做日志,检查点时保存至数据字典,确保重启后能基于持久化的最大值初始化,避免查询,增强连续性和一致性。[[MySQL参考手册, 3099页]](https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html)
|
9月前
|
关系型数据库 MySQL 数据库
MySQL中如何设置 auto_increment 重新计数 主键id从1开始
几句话解决 auto_increment 重新计数?
167 0
MySQL中如何设置 auto_increment 重新计数 主键id从1开始
|
11月前
|
SQL 关系型数据库 MySQL
MySQL AUTO_INCREMENT 原理解析
在关系型数据库MySQL中,AUTO_INCREMENT是数据库的一个属性,该属性使得在申明了AUTO_INCREMENT的列中可以自动生成唯一的递增值。本文详细介绍了在InnoDB及MyISAM引擎中AUTO_INCREMENT的使用原理及一些容易被忽略的问题。
|
6月前
|
关系型数据库 MySQL 开发者
MySQL 字段约束 null, not null, default, auto_increment
前言:转载,觉得有用就发了一遍 在 MySQL 中,每个字段定义都包含附加约束或者修饰符,这些可以用来增加对所输入数据的约束。 今天我们就来看一下 MySQL 的字段约束: NULL 和 NOT NULL 修饰符、DEFAULT 修饰符,AUTO_INCREMENT 修饰符。
132 0
|
关系型数据库 MySQL
MySQL 中 auto_increment 自动插入主键值
对于整数类型的主键,常搭配自增长 auto_increment 来使用。插入数据对应的字段不给值时,使用最大值+1!! 🌘下面进行演示~~🌒
MySQL 中 auto_increment 自动插入主键值
|
SQL 关系型数据库 MySQL
MySQL中ENGINE=InnoDB、AUTO_INCREMENT的意思
MySQL中ENGINE=InnoDB、AUTO_INCREMENT的意思
|
SQL 关系型数据库 MySQL
MySQL 使用 AUTO_INCREMENT 关键字
MySQL 使用 AUTO_INCREMENT 关键字
111 0
|
数据可视化 关系型数据库 MySQL
MySQL约束-自增长约束(auto_increment)、非空约束(not null)、唯一约束(unique)
MySQL约束-自增长约束(auto_increment)、非空约束(not null)、唯一约束(unique)
581 0
MySQL约束-自增长约束(auto_increment)、非空约束(not null)、唯一约束(unique)
|
SQL 关系型数据库 MySQL
记一次MySQL AUTO_INCREMENT的故障
MySQL如果在指定id 进行插入的时候,如果这个id大于表的自增值,那么MySQL会把表的自增值修改为这个id,并加1,但是如果我们把主键更新成更大的值,MySQL并不会把表的自增值修改为更新后的值,会埋下一颗定时炸弹,在某些情况下,如DDL,重启等之后,业务开始报错,会误认为DDL或者重启导致业务表的插入故障。
3287 0

推荐镜像

更多