建材网站建设 南宁,室内装修设计自学入门,重庆网站备案最快几天,js跳转到别的网站MySQL数据库为列提供了一种自增属性#xff0c;当列被定义为自增时。Insert语句对该列即使不提供值#xff0c;MySQL也会自动为该列生成递增的唯一标识#xff0c;因此这个特性广泛用于主键的自动生成。
一、自增列的用法
自增列具有自动生成序列值#xff0c;整型#…MySQL数据库为列提供了一种自增属性当列被定义为自增时。Insert语句对该列即使不提供值MySQL也会自动为该列生成递增的唯一标识因此这个特性广泛用于主键的自动生成。
一、自增列的用法
自增列具有自动生成序列值整型单调递增这些特点非常适合作为索引组织表的主键新插入的数据会附加在已有的数据后面不会出现页分裂现象且整型的主键查找效率非常高。
1.1 基本用法
在创建表时只需在某个整型列tinyint,smallint, mediumint, int, bigint上指定auto_increment即可打开自增属性。
一张表中只能指定一个自增列且必须建立索引示例中 id列没有指定为索引列建表报错must be defined as a key
create table t2(
id int auto_increment,
name varchar(32));指定自增列为主键创建成功
create table t (
id int primary key auto_increment,
name varchar(32));插入数据时即使insert语句未包含自增列MySQL也会自动为该列生成值
insert into t(name) values(Vincent); -- 未指定id列
select * from t;如果指定了0或null同样也可以生成自增值
insert into t values (0, Victor), (null, Grace); -- 指定0和null
select * from t;注意由于0会触发自增如果ID列本来保存的数据就包含0那么在数据导出和导入过程中数字0可能会因此触发自增而被修改导致数据不一致。 这种情况可以打开sql_mode参数中的no_auto_value_on_zero选项可以在会话和全局修改打开该选项后只有null可以触发自增0不再触发。
set sql_modeconcat(session.sql_mode, ,no_auto_value_on_zero);
insert into t values(0, Tom);
select * from t;1.2 自增列特性
自增列除了让其自动生成值之外也可以显式赋值使用中注意以下几点
显式赋值可能导致大量值被浪费事务回滚不会回滚已使用的自增值truncate table清除数据的同时也会让自增值初始化alter table … auto_increment1 可以让自增值恢复到最大可用值消除间隙并不会设定成1
自增列可以显式赋值但如果指定的值超过目前auto_increment的最大值则MySQL会从你指定的值之后开始继续递增即使前面有可用的值也不再使用示例中显式指定id列为1000那么下一条数据会从1001开始自增。
insert into t values(1000, Jerry);
insert into t values(null, Spike);事务中如果使用了自增值即使回滚自增值也不会恢复示例中的事务消耗了2个自增值(1002, 1003)然后事务回滚了但是下一条insert语句自增值是从1004开始的
begin;
insert into t values(null, Spike);
insert into t values(null, Spike);
rollback; -- 事务回滚
insert into t values(null, Tyke); -- 自增值不会回滚
select * from t;示例删除了id为1000及以上的数据后使用alter table … auto_increment1使自增值恢复到当前数据的最大值
delete from t where id1000;
alter table t auto_increment1;
insert into t values(null, Jerry);
select * from t;1.3 通过last_insert_id()获取自增值
MySQL提供了函数last_insert_id()用于获取上一个成功执行的insert语句所生成的第一个自增值
truncate table t;
insert into t values(null,Vincent);
select last_insert_id();单一insert语句如果插入多行获取的是语句中第一个产生的自增值而不是最后一个下面insert语句插入了2条记录但last_insert_id()返回的是2而不是3虽然表中id增长到3
insert into t values(null,Victor),(null,Grace);
select last_insert_id();
select * from t;如果在事务中手动回滚last_insert_id()的值也是不会回滚的其代表的是曾经成功插入的自增值而不判断事务是否最终提交有一定误导性不能用作判断实际插入的值
begin;
insert into t values(null,Vincent);
insert into t values(null,Vincent); -- 成功插入last_insert_id()为5
rollback; -- 事务回滚
select last_insert_id(); -- last_insert_id()依然是5但数据实际未插入last_insert_id(expr)还有个可选的参数如果提供参数expr则会返回该值并将expr记录为下一个last_insert_id()的返回值
select last_insert_id(100);
select last_insert_id();二、自增计数器
在MySQL8.0之前对于auto_increment的值会在内存中维护一个计数器不保存在磁盘上在服务器启动时会对每张表执行类似select max(auto_column) from t for update;语句获取当前表中的最大自增值用于初始化这个计数器。
MySQL8.0以后这个计数器的值会在每次变更时写入重做日志和数据字典保存到磁盘上。服务器重启时直接读取数据字典即可不必再通过查询表初始化。
三、自增值生成模式
上面的都是单线程下自增值的生成示例但在并发时多个事务可能会同时向表中插入数据事务之间存在争用。MySQL为并发场景下自增值的生成提供了3种不同的模式。3种模式由innodb_autoinc_lock_mode控制只读变量修改需要重启对应的值分别为0, 1, 2
0, 传统模式Traditional Lock Mode1, 连续模式Consecutive Lock Mode2, 交错模式Interleaved Lock Mode
在解释3种模式的区别前先了解一下insert语句的分类insert语句可以分为以下3类
简单插入Simple Inserts如单记录insert或者多记录insert在解析SQL时就可以确定要加载的记录数即要生成自增值数量批量插入Bulk Inserts如insert … select, load data等在解析SQL时不确定需要加载的记录数混合插入Mixed-Mode Inserts在多记录简单插入中为自增列显式指定了部分值如 insert into … values (null,‘a’), (5, ‘b’), (null, ‘c’)
3.1 传统模式
在传统模式下innodb_autoinc_lock_mode0所有类型的insert都会使用表级X锁并且持有到insert语句结束这意味着同一时间只有1条insert语句可以执行但可以保证单条insert语句产生的自增值是连续的。
在基于语句的主从复制Statement-Based Replication模式下insert语句在主从可以生成相同的值。传统模式只是为了向前兼容现在已经不会使用了。
3.2 连续模式
连续模式innodb_autoinc_lock_mode1是对传统模式的优化对于批量插入这种不确定需要需要多少自增值的insert会和传统模式一样使用表级锁直至insert语句执行完成。
而对于可以事先确定插入记录数的简单插入MySQL会用mutex闩更轻量级的锁仅在预先分配自增值时锁定在insert语句执行完成前就已经释放了。连续模式也可以保证基于语句的复制主从可以生成相同的自增值但性能比传统模式更好。
对于混合插入类型多行简单插入中部分行显式指定自增值部分行未指定连续模式下会预先生成比要插入行更多的自增值然后以连续方式分配给需要自增的行多余的值就丢弃了。
MySQL8.0以前的版本默认为连续模式。
3.3 交错模式
交错模式下innodb_autoinc_lock_mode2不使用表锁任何并发insert都可以同时执行这意味着多条insert语句生成的自增值是可能是交错的单条insert语句无法保证生成连续的自增值但这种模式并发性能是最好的。
因为缺乏了表锁控制多条insert并发插入在主从执行时无法保证自增值完全相同此模式对基于语句的复制应该没人用了吧是不安全的建议配合基于行的复制Row-Based Replication使用MySQL8.0默认。
MySQL8.0默认为交错模式。
四、调整自增偏移
自增列的默认初始值为1步长为1。但在多主复制、组复制这类可以多点写入的环境可能会产生冲突。
为了保证不出现冲突可以设置auto_increment_offset和auto_increment_increment来修改自增的初始值和步长使各个写入点产生的自增值不重叠可以在会话和全局级别修改。
例如现在有一个双主环境可以在一台主机上配置初始值为1默认步长为2这样生成的自增值都是单数
set auto_increment_increment2;而在另一台主机上配置初始值为2步长为2生成自增值都是双数这样可以避免并发写入时发生冲突
set auto_increment_offset2;
set auto_increment_increment2;五、监控自增值的使用比例
由于数据类型最大值的限制自增并不是没有上限的。当到达上限时数据无法继续插入导致业务中断因此DBA需要监控自增值的使用情况在达到上限之前及时采取扩容措施。
示例这里又新建2张表设置自增列类型为tinyint8位有符号整数范围为-128~127然后将自增值分别设置为64和127
create table t2(id tinyint primary key auto_increment);
create table t3(id tinyint primary key auto_increment);alter table t2 auto_increment64;
alter table t3 auto_increment127;使用下面的SQL即可查询test数据库下所有表的自增列使用比例可根据情况调整去除t.table_schematest’可以查询所有库
select
t.table_schema,
t.table_name,
t.auto_increment,
c.column_type,
concat(round((t.auto_increment /
(case data_type
when tinyintthen if(column_type like %unsigned, 255, 127)
when smallint then if(column_type like %unsigned, 65535, 32767)
when mediumint then if(column_type like %unsigned, 16777215, 8388607)
when intthen if(column_type like %unsigned, 4294967295, 2147483647)
when bigint then if(column_type like %unsigned,18446744073709551615,9223372036854775807)
end))*100,2),%) used_percentage
from information_schema.tables t
join information_schema.columns c on t.table_schema c.table_schema and t.table_name c.table_name
where t.auto_increment is not null and c.extraauto_increment and t.table_schematest;从结果可以看到t3表的自增列已经使用100%再插入新的数据就会报错了。
insert into t3 values(null);
