MySQL自增列AUTO |
您所在的位置：网站首页 › mysql建表自动增长 › MySQL自增列AUTO |
MySQL自增列AUTO
|
目录
基本用法样例基本要求
使用要点初始值自增列计数器NULL的处理存储指定数值存0负数存储其他指定数值
其他贴士查看自增列当前的最大值MySQL重启后自增列的初始化`replace into`可能改变自增列自增列超过最大值
MySQL可以通过AUTO_INCREMENT关键字，实现列数据自增。一般用于单表的唯一标识列，常见的有主键。每张表最多只能有一个自增列。 基本用法 样例 CREATE TABLE `test` ( `k` BIGINT(19) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(64) NOT NULL, PRIMARY KEY (`k`), UNIQUE KEY `key_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 基本要求 设置AUTO_INCREMENT的列，需为整数或浮点数据类型自增列需设置为NOT NULL，否则会直接存储NULL 使用要点 初始值自增列默认是从1开始，即插入第一行数据默认是1。 可在建表时设置AUTO_INCREMENT=n，指定一个自增的初始值。 CREATE TABLEtest(...) AUTO_INCREMENT=10，表示自增列从10开始自增。 也可以通过ALTER TABLE test auto_increment = n来设置，但这样下次插入的值是n+1。 数据量大的时候性能比较差 注意：MySQL重启后，InnoDB引擎的表，自增列需要重新初始化。具体原因参见MySQL重启后自增列的初始化和自增列计数器。 自增列计数器InnoDB引擎中，含自增列的表，在数据字典中会包含一个自动增量计数器，用于为该列分配新值，使用show create table看到的建表语句中，显示的AUTO_INCREMENT=n就是这个计数器值。 该计数器仅存储在内存里，而没有刷新到磁盘，重启后可能会丢失。参见MySQL重启后自增列的初始化 初始化这个计数器有几种方法： 上面自增列计数器中介绍的CREATE TABLE或ALTER TABLE指定SELECT MAX(ai_col) FROM table_name FOR UPDATE;注意该方法有失效可能。如果SHOW TABLE STATUS语句在初始化之前检查了表，就不会递增这个值了。 这个方式是使用对表的常规互斥锁定读取，并且该锁定持续到事务结束。 新创建表的计数器也是以类似原理处理的初始化自动增量计数器后，如果插入数据时自增列没有指定值，则InnoDB会递增计数器并将新值分配给该列。如果插入时给自增列指定了值，且该值大于当前计数器值，则计数器将设置为指定的列值。只要服务器运行，InnoDB就会使用内存中的自动增量计数器。如前所述，当服务器停止并重新启动时，该表表的执行第一个INSERT时，InnoDB会重新初始化自增计数器。 注意：重启MySQL后，CREATE TABLE和ALTER TABLE时指定的AUTO_INCREMENT = N就失效了，需要的话要重新设置（MySQL重启后自增列的初始化）。 NULL的处理自增列插入NULL时，将设置为下一个序列值，一般就是当前最大值+1 插入数据时，没有明确给该列设值，等同于NULL 存储指定数值自增列需保证唯一，优先判断是否重复，重复则报错。 存0存0和存NULL是相同效果，但0容易和真实值混淆，所以不建议这样用。 如果在sql_mode中配置NO_AUTO_VALUE_ON_ZERO，0会直接存储、不会自动生成值如果不方便调sql_mode，还可以通过先插入、再更新成0的方式； 但注意，较大值更新为较小值、不会更新自增序列。即如果是当前最大值(max)这行更新成0，尽管更新后最大值已经变成max - 1，但下一次插入数据，自动生成的主键仍然是max + 1而不是max 负数不支持负数。 因此自增列一般要设置为UNSIGNED，这样可以将自增容量提升一倍。 存储其他指定数值存储一个指定数值时(后面代称为value1)，会直接存储该值。 如果该值大于当前最大值max，那么会重新排列，下次插入会以当前值(value1)为开始继续生成新值(value1 + 1)。效果上就是会跳过一些值。 如果该值小于当前值，只判断是否重复。 其他贴士 查看自增列当前的最大值一般会使用select max(col) from xxx的方式获取自增列最大值，但这个方式需要计算，而且会让MySQL更新计数器。 其实还有更简单的方式：show create table xxx，效果如下图 如自增列计数器所讲，MySQL通过维护这个计数器，实现自增值的维护和分配。但因为但该计数器仅存储在内存里，而没有刷新到磁盘，这就意味着，一旦MySQL重启，自增列会从初始值开始自增，而不是表中当前的最大值。 所以MySQL重启后，需要重新初始化计数器为自增列最大值。 在自增列计数器中介绍了初始化的几种方式： ALTER TABLE test auto_increment = nSELECT MAX(ai_col) FROM table_name FOR UPDATE; replace into可能改变自增列如果一个表同时存在自增主键和唯一键，replace into指定了新的主键值或不指定主键值，会导致主键被更新成新值。 具体可参考《MySQL自增列使用replace into的风险》 自增列超过最大值当AUTO_INCREMENT整数列的值用完时，后续INSERT操作将返回重复键错误。这是MySQL的标准处理模式。 MySQL官方文档 - AUTO-INCREMENT样例MySQL官方文档 - InnoDB中AUTO-INCREMENT的处理 |
【本文地址】