linux从a自增10_MySQL字段自增长AUTO_INCREMENT用法实例详解

最新推荐文章于 2022-12-07 17:46:10 发布
最新推荐文章于 2022-12-07 17:46:10 发布
阅读量324 收藏
点赞数
文章标签： linux从a自增10
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39895684/article/details/111554020
版权

之前有碰到过开发同事指出一张InnoDB表的自增列 AUTO_INCREMENT 值莫明的变大，由于这张表是通过MySQLdump导出导入的。

问题排查：

1、首先，查看表表义的sql部分的 auto_increment 值部分是正常，所以排除是导入表问题所引起的；

2、最后，经过沟通了解怀疑是插入时指定自增列的值，并且值过大，随之发现自增列的值出错时又进行大量删除时引起的问题。

为了验证这个怀疑的准确性，同时学习下InnoDB处理 AUTO_INCREMENT 的机制，因此在测试环境做了测试总结。

本文使用的MySQL版本为官方社区版 5.7.24。

(root@localhost) [test] > select version();

+------------+

| version() |

+------------+

| 5.7.24-log |

+------------+

1 row in set (0.00 sec)

测试环境测试表参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

(root@localhost) [test] > show create table t1\G;

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` varchar(10) DEFAULT NULL,

PRIMARY KEY (`c1`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

(root@localhost) [test] > show columns from t1;

+-------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+----------------+

| c1 | int(11) | NO | PRI | NULL | auto_increment |

| c2 | varchar(10) | YES | | NULL | |

+-------+-------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)

AUTO_INCREMENT锁模式

InnoDB自增锁的模式由参数 innodb_autoinc_lock_mode 在启动时指定，这是一个只读参数，并不能在实例运行中动态修改参数值。参数值选项如下：

innodb_autoinc_lock_mode = {0|1|2}

InnoDB表insert语句主要可以分为三种类型：

Simple inserts(简单插入)

可以通过语句预先判断插入的行数。包括不包含子查询的单行、多行 INSERT 和 REPLACE 语句，还有语句 INSERT ... ON DUPLICATE KEY UPDATE。

Bulk inserts(大量插入)

无法通过语句预先判断插入的行数。包括 INSERT ... SELECT 、 REPLACE ... SELECT 和 LOAD DATA 语句。InnoDB每处理一行才会为 AUTO_INCREMENT 列分配一个值。

Mixed-mode inserts(混合模式插入)

在简单插入语句当中，有的行有为自增列指定值，而有的行没有为自增列指定值。例如：

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

其中c1为自增列。

还有一种混合模式插入语句 INSERT ... ON DUPLICATE KEY UPDATE ，这类语句InnoDB为自增列预分配的值有可能被使用，也有可能不被使用。

传统锁模式 (traditional)

innodb_autoinc_lock_mode = 0

在这个模式下，所有的 INSERT 语句在插入有自增属性的列时都要获取一个特殊的 AUTO-INC 表级锁。该锁的持有时间到语句结束(而不是到事务结束，一个事务中可能包含多条语句)，它能够确保为有自增属性列在 INSERT 一行或者多行数据时分配连续递增的值。

连续锁模式 (consecutive)

innodb_autoinc_lock_mode = 1

这是默认的锁模式。在这个模式下，大量插入每条语句执行时都将获得特殊的表级 AUTO-INC 锁，语句执行完成后释放。每次只有一条语句可以执行并持有 AUTO-INC 锁。

Bulk inserts(大量插入)

如果大量插入的源表和目标表是不同的，则在源表第一行数据获得共享锁之后，目标表就加上 AUTO-INC 表锁；

如果大量插入的源表和目标表是同一张表，当源表选取所有行获得共享锁之后，目标表才加上 AUTO-INC 表锁。

Simple inserts(简单插入)

通过 MUTEX(轻量级的锁) 而不是 AUTO-INC特殊表锁控制插入分配自增属性列的值；

MUTEX 只在分配值的过程中持有，而无需等到语句结束，并且性能花销更少；

简单插入不持有 AUTO-INC 锁，但如果其他事务持有，需等待其他事务释放，就像大量插入那样。

交叉锁模式 (interleaved)

innodb_autoinc_lock_mode = 2

在这种锁模式下，没有插入语句使用 AUTO-INC 表级锁，并且多条语句可以并发执行。这是最快并且扩展性最好的锁模式，但是如果binlog使用基于语句级复制的在从库重放SQL语句时是不安全的。

AUTO_INCREMENT锁模式使用说明

用于复制

基于语句级别的复制，当 innodb_autoinc_lock_mode = 0 | 1 时，主从使用的是相同的自增列值。当 innodb_autoinc_lock_mode = 2 或者主从使用不同的 innodb_autoinc_lock_mode 时，主从无法保证使用相同的自增列值；

基于行级别和复合模式的复制，innodb_autoinc_lock_mode 的所有取值都是安全的，因为SQL语句执行顺序对基于行级别的复制没影响。

自增值缺失与间隙

无论 AUTO_INCREMENT 处于哪种锁模式下，即 innodb_autoinc_lock_mode 的所有取值情况下，在一个事务当中自增列分配的值是不能被回滚的。这会导致事务回滚了，但是自增列分配的值却消失了，自增列分配的值是无法随着事务回滚而重复利用，这样就自增列上的值就产生了间隙。

测试：

--1、 开启一个事务

(root@localhost) [test] > begin;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test] > insert into t1(c2) values('aa');

Query OK, 1 row affected (0.00 sec)

(root@localhost) [test] > select * from t1;

+----+------+

| c1 | c2 |

+----+------+

| 1 | aa |

+----+------+

1 row in set (0.00 sec)

(root@localhost) [test] > show create table t1\G;

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` varchar(10) DEFAULT NULL,

PRIMARY KEY (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

--2、事务回滚，不提交

(root@localhost) [test] > rollback;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test] > select * from t1;

Empty set (0.00 sec)

(root@localhost) [test] > show create table t1\G;

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` varchar(10) DEFAULT NULL,

PRIMARY KEY (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

--3、再次开启事务，插入值并提交

(root@localhost) [test] > begin;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test] > insert into t1(c2) values('aaa');

Query OK, 1 row affected (0.02 sec)

(root@localhost) [test] > select * from t1;

+----+------+

| c1 | c2 |

+----+------+

| 2 | aaa |

+----+------+

1 row in set (0.00 sec)

(root@localhost) [test] > commit;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test] > show create table t1\G;

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` varchar(10) DEFAULT NULL,

PRIMARY KEY (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

可以看出自增列分配的值是不会再出现 c1=1 的。

为自增列指定NULL或者0

无论 AUTO_INCREMENT 处于哪种锁模式下，如果在 INSERT 语句为自增列指定 NULL 或者 0 时，InnoDB认为并没有为自增列指定值，同时也会为其分配值。

测试：

(root@localhost) [test] > select * from t1;

+----+------+

| c1 | c2 |

+----+------+

| 2 | aaa |

+----+------+

1 row in set (0.00 sec)

(root@localhost) [test] > insert into t1 values(NULL,'bbb'),(0,'ccc');

Query OK, 2 rows affected (0.02 sec)

Records: 2 Duplicates: 0 Warnings: 0

(root@localhost) [test] > select * from t1;

+----+------+

| c1 | c2 |

+----+------+

| 2 | aaa |

| 3 | bbb |

| 4 | ccc |

+----+------+

3 rows in set (0.00 sec)

(root@localhost) [test] > show create table t1\G;

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` varchar(10) DEFAULT NULL,

PRIMARY KEY (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

为自增列指定负值

无论 AUTO_INCREMENT 处于哪种锁模式下，自增列分配值机制不会生效，即为自增列指定负值是不影响 AUTO_INCREMENT 值的。

测试：

(root@localhost) [test] > SELECT auto_increment FROM information_schema.tables where table_schema='test' and table_name='t1';

+----------------+

| auto_increment |

+----------------+

| 5 |

+----------------+

1 row in set (0.00 sec)

(root@localhost) [test] > insert into t1 values(-1,'ddd');

Query OK, 1 row affected (0.01 sec)

(root@localhost) [test] > select * from t1;

+----+------+

| c1 | c2 |

+----+------+

| -1 | ddd |

| 2 | aaa |

| 3 | bbb |

| 4 | ccc |

+----+------+

4 rows in set (0.00 sec)

(root@localhost) [test] > SELECT auto_increment FROM information_schema.tables where table_schema='test' and table_name='t1';

+----------------+

| auto_increment |

+----------------+

| 5 |

+----------------+

1 row in set (0.00 sec)

自增列值大于列类型最大的整型值

无论 AUTO_INCREMENT 处于哪种锁模式下，自增列分配的值如果大于自增列所属字段类型的最大值，则自增列分配值机制就不会生效。

测试：

在MySQL当���，INT类型的最大值为 -2147483648~2147483647

(root@localhost) [test] > select * from t1;

+----+------+

| c1 | c2 |

+----+------+

| -1 | ddd |

| 2 | aaa |

| 3 | bbb |

| 4 | ccc |

+----+------+

4 rows in set (0.00 sec)

(root@localhost) [test] > SELECT auto_increment FROM information_schema.tables where table_schema='test' and table_name='t1';

+----------------+

| auto_increment |

+----------------+

| 5 |

+----------------+

1 row in set (0.00 sec)

(root@localhost) [test] > insert into t1 values(2147483647,'eee');

Query OK, 1 row affected (0.02 sec)

(root@localhost) [test] > select * from t1;

+------------+------+

| c1 | c2 |

+------------+------+

| -1 | ddd |

| 2 | aaa |

| 3 | bbb |

| 4 | ccc |

| 2147483647 | eee |

+------------+------+

5 rows in set (0.00 sec)

(root@localhost) [test] > SELECT auto_increment FROM information_schema.tables where table_schema='test' and table_name='t1';

+----------------+

| auto_increment |

+----------------+

| 2147483647 |

+----------------+

1 row in set (0.00 sec)

可以看出自增列 AUTO_INCREMENT 并不会分配。

大量插入导致的间隙

当参数值 innodb_autoinc_lock_mode 设置为 0 或 1 时，每条语句生成的自增列值都是连续的，不会产生间隙。因为表级 AUTO-INC 锁会一直持有直到语句结束，并且同一时间只有一条语句在执行；

当参数值 innodb_autoinc_lock_mode 设置为 2 时，在大量插入时有可能会产生间隙，但是只有当并发执行 INSERT 语句时。

对于设置成 1 或者 2 情形下，在连续的语句之间可能产生间隙，因为对于大量插入InnoDB并不清楚每条语句所需自增量值数量。

混合模式插入自增列值分配

测试表：

-- t1表：表中无数据，但自增列下一个分配值从101开始

(root@localhost) [test] > show create table t1\G;

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` varchar(10) DEFAULT NULL,

PRIMARY KEY (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

(root@localhost) [test] > select count(*) from t1;

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

-- t2表：表中有100万行数据，并且自增列值是连续的

(root@localhost) [test] > show create table t2\G;

*************************** 1. row ***************************

Table: t2

Create Table: CREATE TABLE `t2` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` varchar(10) DEFAULT NULL,

PRIMARY KEY (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

(root@localhost) [test] > select count(*) from t2;

+----------+

| count(*) |

+----------+

| 1000000 |

+----------+

1 row in set (0.22 sec)

(root@localhost) [test] > select min(c1),max(c1) from t2;

+---------+---------+

| min(c1) | max(c1) |

+---------+---------+

| 1 | 1000000 |

+---------+---------+

1 row in set (0.01 sec)

innodb_autoinc_lock_mode = 0

(root@localhost) [test] > show global variables like 'innodb_autoinc_lock_mode';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| innodb_autoinc_lock_mode | 0 |

+--------------------------+-------+

1 row in set (0.02 sec)

-- 1、开启session 1执行TRX1

TRX1: insert into t1(c2) select c2 from t2;

(root@localhost) [test] > insert into t1(c2) select c2 from t2;

Query OK, 1000000 rows affected (6.37 sec)

Records: 1000000 Duplicates: 0 Warnings: 0

-- 2、在TRX1执行期间开启session 2执行TRX2

TRX2: insert into t1 (c1,c2) values (1,'test_inc_a'), (NULL,'test_inc_b'), (5,'test_inc_c'), (0,'test_inc_d');

(root@localhost) [test] > insert into t1 (c1,c2) values (1,'test_inc_a'), (NULL,'test_inc_b'), (5,'test_inc_c'), (0,'test_inc_d');

Query OK, 4 rows affected (5.01 sec)

Records: 4 Duplicates: 0 Warnings: 0

-- 3、查看TRX2插入值的记录

(root@localhost) [test] > select * from t1 where c2 in ('test_inc_a','test_inc_b','test_inc_c','test_inc_d');

+---------+------------+

| c1 | c2 |

+---------+------------+

| 1 | test_inc_a |

| 5 | test_inc_c |

| 1000101 | test_inc_b |

| 1000102 | test_inc_d |

+---------+------------+

4 rows in set (0.34 sec)

-- 4、查看当前AUTO_INCREMENT值

(root@localhost) [test] > select auto_increment from information_schema.tables where table_schema='test' and table_name='t1';

+----------------+

| auto_increment |

+----------------+

| 1000103 |

+----------------+

1 row in set (0.00 sec)

TRX1：持有了 AUTO-INC 表锁，自动分配了101~1000100的自增值，并保证是连续的；

TRX2：需等待 AUTO-INC 锁的释放，自动分配了1000101~1000102的自增值。

可以看出下一个自增列值为 103，因为自增列的值是在每条插入语句执行时分配的，而不是一开始就分配完的。

innodb_autoinc_lock_mode = 1

(root@localhost) [test] > show global variables like 'innodb_autoinc_lock_mode';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| innodb_autoinc_lock_mode | 1 |

+--------------------------+-------+

1 row in set (0.01 sec)

-- 1、开启session 1执行TRX1

TRX1: insert into t1(c2) select c2 from t2;

(root@localhost) [test] > insert into t1(c2) select c2 from t2;

Query OK, 1000000 rows affected (5.88 sec)

Records: 1000000 Duplicates: 0 Warnings: 0

-- 2、在TRX1执行期间开启session 2执行TRX2

TRX2: insert into t1 (c1,c2) values (1,'test_inc_a'), (NULL,'test_inc_b'), (5,'test_inc_c'), (0,'test_inc_d');

(root@localhost) [test] > insert into t1 (c1,c2) values (1,'test_inc_a'), (NULL,'test_inc_b'), (5,'test_inc_c'), (0,'test_inc_d');

Query OK, 4 rows affected (4.38 sec)

Records: 4 Duplicates: 0 Warnings: 0

-- 3、查看TRX2插入值的记录

(root@localhost) [test] > select * from t1 where c2 in ('test_inc_a','test_inc_b','test_inc_c','test_inc_d');

+---------+------------+

| c1 | c2 |

+---------+------------+

| 1 | test_inc_a |

| 5 | test_inc_c |

| 1048661 | test_inc_b |

| 1048662 | test_inc_d |

+---------+------------+

4 rows in set (0.32 sec)

-- 4、查看当前AUTO_INCREMENT值

(root@localhost) [test] > select auto_increment from information_schema.tables where table_schema='test' and table_name='t1';

+----------------+

| auto_increment |

+----------------+

| 1048665 |

+----------------+

1 row in set (0.00 sec)

-- 5、查看t1表的前10行记录、后10行记录和总行数

(root@localhost) [test] > select * from t1 order by c1 asc limit 10;

+-----+------------+

| c1 | c2 |

+-----+------------+

| 1 | test_inc_a |

| 5 | test_inc_c |

| 101 | CwAkHbEWs |

| 102 | re |

| 103 | uqrQbj |

| 104 | SQ |

| 105 | ojyPkMA |

| 106 | 03qNqZ |

| 107 | G8J |

| 108 | Uo3 |

+-----+------------+

10 rows in set (0.00 sec)

(root@localhost) [test] > select * from t1 order by c1 asc limit 999994,10;

+---------+------------+

| c1 | c2 |

+---------+------------+

| 1000093 | o4AzuR |

| 1000094 | NJMJJk |

| 1000095 | 0o5xPuDnE |

| 1000096 | QLLtImQC |

| 1000097 | N1Fojm |

| 1000098 | 6aZemarPC |

| 1000099 | 4OUGSM1JzL |

| 1000100 | l8g6J |

| 1048661 | test_inc_b |

| 1048662 | test_inc_d |

+---------+------------+

10 rows in set (0.32 sec)

(root@localhost) [test] > select count(*) from t1;

+----------+

| count(*) |

+----------+

| 1000004 |

+----------+

1 row in set (0.17 sec)

在此模式下：

TRX1：大量插入时持有 AUTO-INC 表锁，自增列的值是预先分配的，101~10000100，总共100万个连续值；

TRX2：混合插入情况下，语句为简单 INSERT 语句，有的行自增列有指定值，而有的行没有，这时TRX2是无须等待持有 AUTO-INC 锁的，由于TRX1语句还在执行，InnoDB并不知道需要分配多少个自增列值，也不清楚TRX用了多少个自增列值，所以在并行执行 INSERT 时对于未指定行的自增列值分配就会产生间隙(1000100~1048661之间的间隙)，但是语句当中分配的自增列(1048661和1048662)值依然是连续的。

可以看出下一个自增列值为 1048665 ，因为自增列值个数在语句执行开始就已经分配了4个(1048661~1048664)，但实际语句只使用了2个。

innodb_autoinc_lock_mode = 2

(root@localhost) [test] > show global variables like 'innodb_autoinc_lock_mode';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| innodb_autoinc_lock_mode | 2 |

+--------------------------+-------+

1 row in set (0.01 sec)

-- 1、开启session 1执行TRX1

TRX1: insert into t1(c2) select c2 from t2;

(root@localhost) [test] > insert into t1(c2) select c2 from t2;

Query OK, 1000000 rows affected (4.67 sec)

Records: 1000000 Duplicates: 0 Warnings: 0

-- 2、在TRX1执行期间开启session 2执行TRX2

TRX2: insert into t1 (c1,c2) values (1,'test_inc_a'), (NULL,'test_inc_b'), (5,'test_inc_c'), (0,'test_inc_d');

(root@localhost) [test] > insert into t1 (c1,c2) values (1,'test_inc_a'), (NULL,'test_inc_b'), (5,'test_inc_c'), (0,'test_inc_d');

Query OK, 4 rows affected (0.02 sec)

Records: 4 Duplicates: 0 Warnings: 0

-- 3、查看TRX2插入值的记录

(root@localhost) [test] > select * from t1 where c2 in ('test_inc_a','test_inc_b','test_inc_c','test_inc_d');

+--------+------------+

| c1 | c2 |

+--------+------------+

| 1 | test_inc_a |

| 5 | test_inc_c |

| 262241 | test_inc_b |

| 262242 | test_inc_d |

+--------+------------+

4 rows in set (0.28 sec)

-- 4、查看当前AUTO_INCREMENT值

(root@localhost) [test] > select auto_increment from information_schema.tables where table_schema='test' and table_name='t1';

+----------------+

| auto_increment |

+----------------+

| 1048665 |

+----------------+

1 row in set (0.00 sec)

-- 5、查看t1表的前10行记录、后10行记录和总行数

(root@localhost) [test] > select * from t1 order by c1 asc limit 10;

+-----+------------+

| c1 | c2 |

+-----+------------+

| 1 | test_inc_a |

| 5 | test_inc_c |

| 101 | CwAkHbEWs |

| 102 | re |

| 103 | uqrQbj |

| 104 | SQ |

| 105 | ojyPkMA |

| 106 | 03qNqZ |

| 107 | G8J |

| 108 | Uo3 |

+-----+------------+

10 rows in set (0.00 sec)

(root@localhost) [test] > select * from t1 order by c1 asc limit 999994,10;

+---------+------------+

| c1 | c2 |

+---------+------------+

| 1000095 | KHukB |

| 1000096 | bnpyaevl79 |

| 1000097 | o4AzuR |

| 1000098 | NJMJJk |

| 1000099 | 0o5xPuDnE |

| 1000100 | QLLtImQC |

| 1000101 | N1Fojm |

| 1000102 | 6aZemarPC |

| 1000103 | 4OUGSM1JzL |

| 1000104 | l8g6J |

+---------+------------+

10 rows in set (0.22 sec)

(root@localhost) [test] > select count(*) from t1;

+----------+

| count(*) |

+----------+

| 1000004 |

+----------+

1 row in set (0.17 sec)

-- 6、查看TRX2插入时产生间隙位置行记录

(root@localhost) [test] > select * from t1 where c1 between 262240 and 262250;

+--------+------------+

| c1 | c2 |

+--------+------------+

| 262240 | mNfx37 |

| 262241 | test_inc_b |

| 262242 | test_inc_d |

| 262245 | Taqi |

| 262246 | Ojpakez |

| 262247 | 2fKtmm6rW |

| 262248 | AysE |

| 262249 | wqWGs |

| 262250 | lC |

+--------+------------+

9 rows in set (0.00 sec)

在此模式下：

TRX1：因为大量插入时是不持有 AUTO-INC 表锁，所在TRX1在执行期间，TRX2很快就插入完成并分配了自增列值。

TRX2：简单插入语句时可以判断出须分配4个自增列值，但是只使用了2个，这样就造成了2个间隙(262243~262244)。

可以看出下一个自增列值为 1048665 ，TRX1执行时因为大量插入时无法预估插入值数量，TRX2执行时分配了4个自增值，但只使用了2个(262241~262242)，造成了2个间隙，TRX1和TRX2的自增列值是交叉分配的。

修改自增列当中的值

无论 AUTO_INCREMENT 处于哪种锁模式下，更新自增列的值都有可能会产生 Duplicate entry 重复值错误。

-- 测试表：

(root@localhost) [test] > create table t3 (c1 int not null auto_increment primary key);

Query OK, 0 rows affected (0.06 sec)

(root@localhost) [test] > show create table t3\G;

*************************** 1. row ***************************

Table: t3

Create Table: CREATE TABLE `t3` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`c1`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

-- 1、插入几行数据

(root@localhost) [test] > insert into t3 values(0),(0),(3);

Query OK, 3 rows affected (0.04 sec)

Records: 3 Duplicates: 0 Warnings: 0

(root@localhost) [test] > select * from t3;

+----+

| c1 |

+----+

| 1 |

| 2 |

| 3 |

+----+

3 rows in set (0.00 sec)

(root@localhost) [test] > select auto_increment from information_schema.tables where table_schema='test' and table_name='t3';

+----------------+

| auto_increment |

+----------------+

| 4 |

+----------------+

1 row in set (0.00 sec)

-- 2、更新c1值为5

(root@localhost) [test] > update t3 set c1 = 5 where c1 = 3;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

(root@localhost) [test] > select * from t3;

+----+

| c1 |

+----+

| 1 |

| 2 |

| 5 |

+----+

3 rows in set (0.00 sec)

(root@localhost) [test] > select auto_increment from information_schema.tables where table_schema='test' and table_name='t3';

+----------------+

| auto_increment |

+----------------+

| 4 |

+----------------+

1 row in set (0.00 sec)

-- 3、再次插入几行数据

(root@localhost) [test] > insert into t3 values(0),(0),(3);

ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

(root@localhost) [test] > select * from t3;

+----+

| c1 |

+----+

| 1 |

| 2 |

| 5 |

+----+

3 rows in set (0.00 sec)

可以看出更新了自增列的值之后产生了 Duplicate entry 重复值错误。建议不要更新自增列的值，或是把自增列值更新为大于 AUTO_INCREMENT 的值。

AUTO_INCREMENT计数器的初始化

当为一张InnoDB表指定自增列时，此时表在数据字典中维护着一个特殊的计数器为自增列分配值，名称为 auto_increment 计数器，这个计数器是存储中内存中，而不是在磁盘上。

当服务器重启之后后，为了初始化 auto_increment 计数器，InnoDB执行如下等效语句来确定自增列下个需要分配的值：

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

默认的，自增列的下一个分配值由以上语句得出的值增加 1 ，这个增加步长由参数 auto_increment_increment 决定。如果是空表，则自增列的下一个分配值为 1 ，这个空表时默认值由参数 auto_increment_offset 决定。

当表中指定自增列，如果没有为自增列值指定值，则由计数器分配。如果为自增列指定了值，还是插入指定的值。如果指定插入值大于计数器的值，同时计数器也会跟着调整，这样容易造成间隙。

由于计数据器是运行在内存中，所以当服务器重启后，InnoDB会重新初始化计数器。

服务器重启的同时也重置了之前 CREATE TABLE 和 ALTER TABLE 为表指定的 AUTO_INCREMENT 值，可以再次通过语句进行指定。

参考

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

weixin_39895684
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL设置主键从10开始增量为10，比如10,20,30.....
stone_tomcate的博客
07-11 1287
CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, dtname VARCHAR(20), loc VARCHAR(100) ); SET auto_increment_increment=10; SET auto_increment_offset=10; INSERT INTO dept(dtname,loc) VALUES('开发...
Mysql自增
Ping
07-17 3888
ALTER TABLE cold_wh_goodslocations MODIFY COLUMN id int(20) NOT NULL AUTO_INCREMENT FIRST ;
mysql的auto_increment
weixin_33831196的博客
08-05 157
用法: CREATE TABLE test ( id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, username VARCHAR(15) NOT NULL ) AUTO_INCREMENT = 100; 在数据库应用，我们经常要用到唯一编号，以标识记录。在MySQL中可通过数据列的AUTO_I...
如何查看mysql自增到哪里_MyBatis如何获取Mysql自增id
weixin_39669204的博客
01-19 659
@Overrideprotected Statement instantiateStatement(Connection connection) throws SQLException {String sql = boundSql.getSql();if (mappedStatement.getKeyGenerator() instanceof Jdbc3KeyGenerator) {String...
mysql自定增长_Mysql中设置自增长起始值和递增值
weixin_36084095的博客
01-19 363
1.查询默认配置mysql> show variables like 'auto_inc%';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| auto_increment_increment | 1 || auto...
MySQL 序列 AUTO_INCREMENT详解及实例代码
12-16
MySQL 序列 AUTO_INCREMENT详解及实例代码 MySQL序列是一组整数：1, 2, 3, …，由于一张数据表只能有一个字段自增主键， 如果你想实现其他字段也实现自动增加，就可以使用MySQL序列来实现。 本章我们将介绍如何使用...
mysql数据类型和字段属性原理与用法详解
12-14
本文实例讲述了mysql数据类型和字段属性。分享给大家供大家参考，具体如下： 本文内容： 数据类型 ... 自增长：auto_increment 默认值：default 字段描述：comment 补充： 复合键 首发日期：2
04_JPA详解_第一个JPA实例与JPA主键生成策略.zip
05-05
1. **GenerationType.IDENTITY**: 这种策略依赖于数据库自增字段，适用于MySQL、SQL Server等支持自动增长的数据库。 2. **GenerationType.AUTO**: 这是默认策略，具体实现取决于所使用的JPA提供商，如Hibernate。...
Mysql中Identity 详细介绍
09-10
MySQL中的Identity特性主要涉及到自动增长列（auto_increment），它是数据库设计中经常用到的一个功能，特别是在需要为每个新记录分配唯一标识的时候。Identity属性通常应用于整数类型字段，特别是主键，确保每条...
mysql双主带双从
01-10
### MySQL双主带双从架构详解与搭建步骤 在高可用性和数据冗余需求日益增长的背景下，MySQL的双主带双从（Multi-Master with Slave Replication）架构成为了一种有效的解决方案。此架构不仅提高了数据库系统的可用...
sql 创建表时AUTO_INCREMENT=10
Mr.horse的博客
12-05 550
默认地，主键的开始值是 1，每条新记录递增 1，AUTO_INCREMENT=10 代表初始值为10。
MySQL自增列AUTO_INCREMENT详解（含NULL值、初始值、指定值等说明）- MySQL基本函数和命令（四）
reading-conference-writing
09-04 1万+
MySQL可以通过AUTO_INCREMENT，实现列数据自增，一般可用于单表的唯一标识列，常见的有主键。 基本用法 样例 CREATE TABLE `test` ( `k` BIGINT(19) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(64) NOT NULL, PRIMARY KEY (`k`), UNIQUE KEY ...
关于自增约束auto_increment需要注意的地方，mysql8版本的报错
weixin_46060711的博客
12-07 4184
关于自增约束auto_increment需要注意的地方，mysql8版本的报错
MySQL修改自动增长值10无效_设置主键自增，初始值从10开始，每次自增10
weixin_34404808的博客
01-18 2464
一、首先题主提问的标题会让人误解初始值从10开始，每次自增10根据截图反推回去，最开始的auto_increment_%变量应该是这样的↓顺便纠正下@eason_li 的解释，这两个变量正确的含义：auto_increment_increment:列的增量值，这才是步长。auto_increment_offset:初始值，也称偏移量。因为题主CREATE TABLE后马上更改了变量SET auto...
MYSQL AUTO_INCREMENT自增的用法
热门推荐
冰三尺的Blog
06-01 16万+
alter table tbname auto_increment = x ; 设置表tbname的唯一auto_increment字段起始值从x开始，如果此表数据量很多的话，这样执行起来会很慢. auto_increment_offset表示自增长字段从那个数开始，他的取值范围是1 .. 65535 auto_increment_increment表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535
mysql AUTO_INCREMENT 设置主键自增
wll的专栏
04-16 9万+
主键自增： CREATE TABLE   test ( id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, username VARCHAR(15) NOT NULL )AUTO_INCREMENT = 100; 可在建表时可用“AUTO_INCREMENT=n”选项来指定一个自增的初始值。 alter table tbname
Mysql中自增字段（AUTO_INCREMENT)的一些常识
dazhi_100的专栏
05-24 5516
Mysql中自增字段（AUTO_INCREMENT)的一些常识在系统开发过程中，我们经常要用到唯一编号。使用过mysql的人都应该知道，mysql有一个定义列为自增的属性：AUTO_INCREMENT。指定了AUTO_INCREMENT的列必须要建索引，不然会报错，索引可以为主键索引，当然也可以为非主键索引。（不一定要做主键）mysql> create table t4 (id int auto_
mysql'建表后跟ENGINE=INNODB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
12-17 5820
ENGINE=INNODB 表示将数据库的引擎设置为InnoDB,从MySQL 5.6开始默认使用该引擎。 AUTO_INCREMENT=10 表示自动增长的起始值为10 DEFAULT CHARSET=utf8表示设置数据库的默认字符集为utf8...
记一次MySQL AUTO_INCREMENT的故障
程序猿DD
05-13 617
一、前言开发突然紧急的过来说，他们记录无法插入了，有报重复键错误ERROR 1062 (23000): Duplicate entry '2147483647' for k...
mysql AUTO_INCREMENT 怎么自动变成 AUTO_INCREMENT=1662029892334329859 了
最新发布
05-27
AUTO_INCREMENT 是 MySQL 中用来定义自增字段的关键字，它指定一个列为自增列，每次插入一条数据时，该列的值将自动递增。AUTO_INCREMENT 的起始值默认为1，如果你想指定一个不同的起始值，可以在创建表时使用以下...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值