MySQL的自增列情况比较特殊,之前分析了两篇。...MySQL自增列主从不一致的测试(r12笔记第37天) MySQL自增列的重复值问题(r12笔记第25天) 而且在OOW的时候也着重提了一下自增列的历史遗留问题。...十年前的老问题,MySQL 8.0有了答案 当然基于MySQL自增列的实现,确实是不够优雅,在新的版本还在持续引入新的特性。...我们修改一下自增列的值。...对于自增列的问题,大家在程序侧需要格外注意。
---- 1背景描述 OceanBase 数据库中分为 MySQL 租户与 Oracle 租户,本文针对 OceanBase 中 Oracle 租户怎样创建自增列,以及如何更简单方便的处理自增列的问题展开介绍...发现问题场景 业务需要将数据库转换为 OceanBase 数据库,但源端涉及到 Oracle 及 MySQL 两种不同数据库,需要合并为 OceanBase 中单一的 Oracle 模式,其中源端 MySQL...在数据迁移中发现,MySQL 中的自增列(AUTO_INCREMENT)在 OBOracle 中是不支持的,在 OBOracle 对应 MySQL 自增列的功能是通过序列实现的。...如果序列已经递增到最大值或最小值,则会根据你的设置进行循环或停止自增长。...3、验证该方法是否达到自增列的效果。
前言 在 MySQL 的常见规范里面,每个表都要设置主键,一般来说都会推荐自增列作为主键,这和 MySQL 属于聚簇索引表有关,顺序增长的主键比较合适。而自增列中比较常遇见的问题就是自增列的空洞。...原生的 MySQL 自增列也存在一个 BUG,可能会影响到数据一致性,本文也会详细介绍,在自建 MySQL 的时候尽量不要踩到这个坑。...> 通过比较 max_value,auto_increment,auto_increment_ratio 这三个值很容就能监控到即将出现问题的自增列。...现象是 MySQL 在 5.7 和 5.7 之前,自增列的值是保存在内存中的,这就导致了 MySQL 重启之后会丢失这个自增列的值,所以每次重启之后,MySQL 会把表的自增列的值重置为自增列的 MAX...> 可以看到原生的 MySQL 在重启之后自增列的值被重置了。
row in set (0.00 sec) 非空约束为null 并在自增列属性前 即使自增列的非空约束定义可以为 null,但实际自增列为not null create table test_auto_incre...id | id2 | +----+------+ | 1 | 12 | | 2 | 2312 | +----+------+ 2 rows in set (0.00 sec) 非空约束为null 并在自增列属性后...自增列定义可以为null,实际自增列也可以为null;自增列失去作用!...(id int auto_increment null,id2 int null,key idx_id(id)); Query OK, 0 rows affected (0.02 sec) 非空约束在自增列属性后...----+------+ | NULL | 12 | | NULL | 2312 | +------+------+ 2 rows in set (0.00 sec) 非空约束为not null 并在自增列属性后
这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15.../16),则开辟一个新的页(节点) 4、自增主键 如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页 5、非自增主键 如果使用非自增主键...总结 如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话,这时候存取效率是最高的,也就是下面这几种情况的存取效率最高: 1、使用自增列(INT/BIGINT类型)做主键,这时候写入顺序是自增的...,和B+数叶子节点分裂顺序一致; 2、该表不指定自增列做主键,同时也没有可以被选为主键的唯一索引(上面的条件),这时候InnoDB会选择内置的ROWID作为主键,写入顺序和ROWID增长顺序一致; 除此以外...《高性能MySQL》中的原话 ? ?
MySQL的自增列问题其实很有意思,在重启数据库之后,会按照max(id)+1的方式来计算,这样一个看起来有些别扭的实现方式在早期版本就饱受诟病,在MySQL 5.7都没有解决掉,终于在8.0松口了...而重启会带来自增列一类的潜在问题,而如果不重启其实也有可能会有自增列的不一致问题。和两个参数table_definition_cache和table_open_cache还是密切相关的。...,如果表太多而不能全部放在缓存中的话,老的表就会被置换出来,这种被置换出来的表下次再使用的时候,就要重新打开一遍,对自增列来说,这个过程就和实例重启类似,需要 select max(id) + 1 算一下自增值...生成500个表,然后插入一条数据,修改自增列值,然后查询表里的数据,使得数据能够刷出,稍作等待,查看show create table的结果。...SHOW CREATE TABLE t$i; EOF done 测试完成之后,来查看自增列的值情况.
如果需要把一台MySQL中的数据定期归档到另外一台MySQL历史库中,那么很可能会发现会有重复值的问题,导致数据导入会失败,而这个问题其实是和自增列的重复值有关,我们来简单看看。...这个时候使用show create table查看,定义信息中自增列的值为4,即再插入一条记录,id值为4....+----+------+ | 1 | 2 | | 2 | 2 | +----+------+ 2 rows in set (0.00 sec) 这个时候如果查看表定义信息,就会发现自增列目前是...AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) 这是什么原因呢,如果你试试MyISAM,就不会出现这类问题,而对于InnoDB来说,它的自增列的实现在重启之后内存中肯定是没有了...这个情况不光是在MySQL 5.5存在,在MySQL 5.7也依旧存在。 而这类问题是否在数据迁移中会出现呢,我们也需要注意一下。 比如我们使用mysqldump导出数据,然后导入到另外一个环境。
1、mysql中int(11)中的11代表显示宽度 整数列的显示宽度,与mysql需要用多少个字符来显示该列数值,与该整数需要的存储空间的大小都没有关系。...比如,不管设定了显示宽度是多少个字符,bigint都要占用8个字节。 a、int是整型,(11)是指显示字符的宽度,最大为255。...e、如果没有给它指定显示宽度,MySQL会为它指定一个默认值。显示宽度只用于显示,并不能限制取值范围和占用空间。...f、INT(3)会占用4个字节的存储空间,并且允许的最大值也不会是999,而是INT整型所允许的最大值。...2、mysql有五种整型数据列类型,即TINYINT,SMALLINT,MEDIUMINT,INT和BIGINT。 a、区别是取值范围不同,存储空间不相同。
第五步:指定id=0插入; 第六步:指定id=1插入; 第七步:不指定id,依赖自增机制,插入1行; 请问,此时表中的三行记录,id分别是多少?...知识点二:含自增列的表,插入时可以手动指定自增列的值,但不能与已有值冲突,也可以使用系统默认自增列的值。...因此,第五、六、七步都是允许的: insert (0, '000') insert (1, '111') insert ('222') 知识点三:如果手动指定自增列的值是0或者NULL,MySQL会视为无效...也就是说,第五步 insert (0, '000') 又或者 insert (NULL, '000') 都会被MySQL视为: insert ('000') 即,实际插入到表中的记录是 (5,...'000') 第六步 insert (1, '111')没有问题 实际插入的也是 (1, '111') 知识点四:如果使用系统默认自增列的值,会从当前最大值开始往后增加。
昨天的一篇文章MySQL自增列主从不一致的测试(r12笔记第37天),今天有不少网友向我确认一些细节,我想最近正好在看GTID的东西,可以揉在一起来说说。...这个情况也很容易理解,那就是自增列不会变化。...mysql> insert into t1 values(null,2);这个时候查看自增列的情况如下,会逐步递增。...我们看看这个时候的自增列值情况。...纠结的问题就是自增列之为3,而Slave 1节点和Slave 2节点的自增列值为5. mysql> show create table t1\G ***************************
2、设置自增列 MYSQL的自增列一定要是有索引的列,设置种子值要在表的后面设置 --设置自增列 --sqlserver CREATE TABLE emp ( id INT IDENTITY...问:如果有一张表,里面有个字段为id的自增主键,当已经向表里面插入了10条数据之后,删除了id为8,9,10的数据,再把mysql重启, 之后再插入一条数据,那么这条数据的id值应该是多少,是8,还是11...这是因为两种类型的存储引擎所存储的最大ID记录的方式不同,MyISAM表将最大的ID记录到了数据文件里,重启mysql自增主键的最大ID值也不会丢失; 而InnoDB则是把最大的ID值记录到了内存中,...mysql复制表结构/数据的时候,并不会复制主键,索引,自增列等任何属性,仅仅是简单拷数据,而sqlserver会拷贝自增列 ?...这是因为两种类型的存储引擎所存储的最大ID记录的方式不同,MyISAM表将最大的ID记录到了数据文件里,重启mysql自增主键的最大ID值也不会丢失; 而InnoDB则是把最大的ID值记录到了内存中,
关于MySQL里的change和modify,总是看到两种不同的语法,在Oracle中语法有modify,如果修改表名有rename。...unsigned not null auto_increment,name varchar(30),address varchar(50), primary key(id) );然后插入一行数据,让自增列生效...| address | +----+-------+----------+ | 1 | name1 | address1 | +----+-------+----------+ 这个没什么多说的,自增列从...name,address) values('name1','address1'); Query OK, 1 row affected, 1 warning (0.00 sec) 所以查到的数据是下标为0,和自增列没有关系了...50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 我们继续插入一条数据,还是会抛出一个错误,说明自增列设置没起作用
151呢,mysql默认的最大连接数不是100么?...为了确认mysql5.5.3默认的最大连接数为151,去mysql官网查看了一下:mysql默认的最大连接数为151,上限为1000。...2.修改mysql默认的最大连接数为1000 在/etc/my.cnf文件中( 需要重启mysql服务) [mysqld]部分增加 max_connections=1000 mysql其他版本默认的最大连接数...Mysql5.5、mysql5.6、mysql5.7:默认的最大连接数都是151,上限为:100000 ?...Mysql 5.1 根据其小版本的不同,默认的最大连接数和可修改的连接数上限也有所不同。 ?
默认约束 --mysql CREATE TABLE emp ( id INT DEFAULT 12 ) 设置自增列 MYSQL的自增列一定要是有索引的列,设置种子值要在表的后面设置 --mysql...从100开始) 设置自增列的步长,可以分为全局级别和会话级别,如果是会话级别,那么当用户新建一个会话的时候,那么步长又回到了全局级别mysql不能设置为表级别的步长!!...10条数据之后,删除了id为8,9,10的数据,再把mysql重启,之后再插入一条数据,那么这条数据的id值应该是多少,是8,还是11?...这是因为两种类型的存储引擎所存储的最大ID记录的方式不同,MyISAM表将最大的ID记录到了数据文件里,重启mysql自增主键的最大ID值也不会丢失; 而InnoDB则是把最大的ID值记录到了内存中,所以重启...(3) SELECT @@identity 针对任何表 @@identity 是表示的是最近一次向具有identity属性(即自增列)的表插入数据时对应的自增列的值,是系统定义的全局变量。
MySQL里面有一个问题尤其值得注意,那就是自增列的重复值问题,之前也简单分析过一篇MySQL自增列的重复值问题(r12笔记第25天),但是在后续我想了下,还有很多地方需要解释,一个就是从库的自增列是如何维护的...,是否重启从库,自增列会受到影响。...mysql> insert into t1 values (null,2); Query OK, 1 row affected (0.01 sec) 自增列的值为4,而从库的自增列的值依旧没有任何变化。...,这个时候自增列会持续发生变化吗?...我们来验证一下,这个时候从库的自增列又开始生效了。
但是自增列有一个坑,并且这个坑存在了很久,一直到 MySQL 8.0 版本,才修复了这个坑,这个坑就是表的自增列变量 auto_increment 在 MySQL 重启后,有可能丢失。...「innodb 引擎(低版本):Innodb 表中把自增列作为主键 ID 时,自增列是通过 auto-increment 计数器实现的,计数器的最大值是记录到内存中的,重启数据库后,会导致 auto-increment...「MyISam 引擎:MyISAM 表会把自增列(auto-increment 计数器)最大值是记录到数据文件里,重启 MySQL 自增列(计数器)最大值不会丢失,从而使用自增列作为主键 ID 时也不会丢失...Innodb 表中把自增列作为主键 ID 时,在 mysql 重启后就会存在 ID 重置问题。...总结 1)如果 mysql 重启了,那么 innodb 表在启动后,AUTO_INCREMENT 值会自动检测出、并重置为当前表中自增列的最大值 +1。
该锁的持有时间到语句结束(而不是到事务结束,一个事务中可能包含多条语句),它能够确保为有自增属性列在 INSERT 一行或者多行数据时分配连续递增的值。...| auto_increment | +----------------+ | 5 | +----------------+ 1 row in set (0.00 sec) 自增列值大于列类型最大的整型值...无论 AUTO_INCREMENT 处于哪种锁模式下,自增列分配的值如果大于自增列所属字段类型的最大值,则自增列分配值机制就不会生效。...测试: 在MySQL当���,INT类型的最大值为 -2147483648~2147483647 (root@localhost) [test] > select * from t1; +----+--...TRX1语句还在执行,InnoDB并不知道需要分配多少个自增列值,也不清楚TRX用了多少个自增列值,所以在并行执行 INSERT 时对于未指定行的自增列值分配就会产生间隙(1000100~1048661
如果非要在SQL Server 平台修改自增列值的,那就手动需要自增列属性,然后修改该列值,修改成功后再手动添加自增列属性。...该方法最大的缺点就是要通过手工辅助取消和添加自增属性的。 还有一个方法,先将要修改的数据整理为T-SQL的插入脚本,再删除这批要修改的数据,在通过显示插入数据来实现。...MySQL 平台修改自增列值 mysql平台修改自增列值,有些麻烦的。...mysql中存在自增列,如果其引擎是myisam,则该列可以为独立主键列,也可以为复合主键列,即该列必须为主键的关联列;如果其引擎是innodb,则该列必须是独立主键列。...我采用的方法是将两个自增列值(比如1、2)分为以下三个步骤来实现: 1、先将自增列值为1的修改为0; 2、再将自增列值为2的修改为1; 3、再将自增列值为0的修改为2; 以下两种数据引擎的测试环境均是mysql
结论在 pt-archiver 中有这样一个参数--nosafe-auto-increment官方文档中作用如下:图片指定不使用自增列(AUTO_INCREMENT)最大值对应的行进行归档默认开启,该选项在进行归档清除时会额外添加一条...简单总结以下里面包含的信息:pt-archiver工具对自增列(AUTO_INCREMENT)最大值默认行为是: 会在进行归档清除时额外添加一条WHERE子句防止对,自增列(AUTO_INCREMENT...确定自己要对本表的全量数据进行归档时,应开启此参数--nosafe-auto-increment归档部分数据时包含自增列(AUTO_INCREMENT)字段的最大值时,应开启此参数--nosafe-auto-increment...重启后自增列的初始化过程:MySQL通过一个计数器,实现自增值的维护和分配。...但因为但该计数器仅存储在内存里,而没有刷新到磁盘,这就意味着,一旦MySQL重启,自增列会从初始值开始自增,而不是表中当前的最大值。所以MySQL重启后,需要重新初始化计数器为自增列最大值。
PS:本文以下内容基于 MySQL InnoDB 数据库引擎。...当我们试图将自增值设置为比自增列中的最大值还要小的值的时候,自增值会自动变为自增列的最大值 +1 的值,如下图所示: 3.一个表可以有多个自增列吗?...一个表中只能有一个自增列,这和一个表只能有一个主键的规则类似,当我们尝试给一个表添加一个自增列时,可以正常添加成功,如下图所示: 当我们尝试给一个表添加多个自增列时,会提示只能有一个自增列的报错信息...,如下图所示: 4.其他注意事项 除了一个表只能添加一个自增列之外,自增列还需要注意以下两个问题。...一个表中只能有一个自增列,就像一个表中只能有一个主键一样,如果设置多个自增列,那么 SQL 执行就会报错。
领取专属 10元无门槛券
手把手带您无忧上云