安科网

MySQL字段自增长AUTO_INCREMENT用法实例详解

炒饭

炒饭

2018-12-26

关注 关注

之前有碰到过开发同事指出一张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

mysql mysql update语句 innodb

炒饭

炒饭

0 关注 0 粉丝 0 动态

关注 关注

相关推荐

MySQL中使用binlog时格式该如何选择

每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。另外就是,由于mysql现在发展比较快，很多的新功能加入，使mysql的复制遇到了不小的挑战,自然

要啥自行车一把梭 2020-11-12

MySQL数据操作-DML语句的使用

DML数据操作语言，是指对数据库进行增删改的操作指令，主要有INSERT、UPDATE、DELETE三种，代表插入、更新与删除，这是学习MySQL必要掌握的基本知识。方语法中 [] 中内容可以省略。值的顺序和表中字段顺序须保持一致。

minerk 2020-11-12

详解 MySQL中count函数的正确使用方法

当搞清楚count函数的运行原理后，相信上面几个问题的答案就会了然于胸。为了解决上述的问题，我创建了一张 user 表，它有两个字段：主键id和name，后者可以为null，建表语句如下。`id` int NOT NULL AUTO_INCREMENT C

vitasfly 2020-11-12

Golang操作MySql数据库的完整步骤记录

MySQL是业界常用的关系型数据库，在平时开发中会经常与MySql数据库打交道，所以在接下来将介绍怎么使用Go语言操作MySql数据库。Go语言中的database/sql包提供了保证SQL或类SQL数据库的泛用接口，并不提供具体的数据库驱动。我们常用的数

CoderToy 2020-11-16

MySQL主从复制原理以及需要注意的地方

最近在写Mycat专题，由于不少小伙伴最近要出去面试，问我能不能简单写下MySQL的主从复制原理和注意事项，因为在之前的面试中被问到了这些问题。 Master 将数据改变记录到二进制日志中，也就是配置文件 log-bin 指定的文件， 这些记录叫做二进制日

emmm00 2020-11-17

Mysql联表update数据的示例详解

在MySQL中，可以在 UPDATE语句 中使用JOIN子句执行跨表更新。employees表将存储在员工编号，姓名，工作表现和工资的数据。employees 和 merits 表之间以是 performance 字段相关联的。对于 employees 表

王艺强 2020-11-17

MySQL数据类型优化原则

MySQL支持的数据类型很多，选择正确的数据类型对于高性能至关重要。下面几个简单的原则都有助于做出更好的选择。应该尽量使用可以正确储存数据的最小数据类型。如果无法确定哪个数据类型时最好的，就选择你认为不会超过范围的最小类型。比如用MySQ内建的类型而不是使

ribavnu 2020-11-16

专业级的MySQL开发设计规范及SQL编写规范

在团队开发过程中为了项目的稳定，代码的高效，管理的便捷制定内部种开发设计规范是必不可少的，命名规范的对象是指数据库SCHEMA、表TABLE、索引INDEX、约束CONSTRAINTS等的命名约定。数据库创建时必须添加默认字符集和校对规则子句。设计应至少满

bianruifeng 2020-11-16

Mysql 查询JSON结果的相关函数汇总

计算 JSON 深度，计算方式 {} [] 有一个符号即为一层，符号下有数据增加一层，复杂 JSON 算到最深的一次为止，官方文档说 null 值深度为 0，但是实际效果并非如此，列举几个例子。计算 JSON 最外层或者指定 path 的长度，标量的长度为

wangshuangbao 2020-11-13

Mysql 实现字段拼接的三个函数

给运营导出数据时，难免需要对字段进行拼接，如果 Mysql 可以完成的话，就可以少些很多代码。不过如果有字段值为 NULL，则结果为 NULL。上面这种方式如果想要使用分隔符分割，就需要每个字段中间插一个字符串，非常麻烦。concat_ws() 可以一次性

苏康申 2020-11-13

浅谈MySQL中的自增主键用完了怎么办

" (然后，你就可以回去等通知了！我们以无符号整型为例，存储范围为0～4294967295，约43亿！我们先说一下，一旦自增id达到最大值，此时数据继续插入是会报一个主键冲突异常如下所示。因此，表中的真实id必然会出现断续的情况。

vivenwan 2020-11-13

MySql索引使用策略分析

(建立索引会占用磁盘空间的索引文件。2.很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率

moyekongling 2020-11-13

mysql 如何动态修改复制过滤器

2、这个rds上有一个本地的ECS只读从库，这个只读从库会实时同步线上的rds数据库中的数据，这个只读从库供业务方查询使用

gloryli 2020-11-12

MySQL ddl语句的使用

数据定义语言create、drop、alter语句 。定义对数据库记录的增、删、改操作。定义对数据库、表、字段、用户的访问权限和安全级别。这小节主要了解下数据定义语言DDL。我们用它对数据库、表进行一些管理操作，比如：建库、删库、建表、修改表、删除表、对字

云中舞步 2020-11-12

mysql 8.0.22 安装配置图文教程

解压后的目录并没有的my.ini文件，没关系可以自行创建在安装根目录下添加的my.ini ，写入基本配置：。# 允许连接失败的次数。初始化MySQL，在安装时，避免权限问题出错我们尽量使用管理员身份运行CMD，否则在安装时会报错，会导致安装失败的情况，如下

aydh 2020-11-12

解决Navicat Premium 连接 MySQL 8.0 报错\"1251\"的问题分析

人闲太久，努力一下就以为是在拼命。1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client. My

kuwoyinlehe 2020-11-12

MySQL 基于时间点的快速恢复方案

万幸，这份数据是平台上某些商品的价格，基本上是有限个商品，然后价格值也都是固定的，之前有对这个价格表进行备份，于是给他直接重新导入了一份价格表的数据，这个问题也算是解决了。新建一个实例，全库还原，然后应用备份的binlog，一直去追，追到数据被该坏的时间点

jazywoo在路上 2020-11-11

MySQL外键约束的实例讲解

MySQL的外键约束是用来在两个表之间建立链接的，其中一个表发生变化，另外一个表也发生变化。从这个特点来看，它主要是为了保证表数据的一致性和完整性的。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

敏敏张 2020-11-11

MySQL用truncate命令快速清空一个数据库中的所有表

用文本编辑器把每条truncate语句前后的“|”替换为空字符，方便后面一次复制多条执行。truncate与drop是DDL语句，执行后无法回滚；delete是DML语句，可回滚。truncate会清空表中的所有行，但表结构及其约束、索引等保持不变；dro

世樹 2020-11-11

修改MySQL8.0 默认的数据目录(快捷操作无配置)

使用场景：我们使用的是阿里云，单独购买了数据盘，MySQL 8.0 数据库默认装在系统盘上，为了考虑安全性和空间问题，我们需要将默认的数据库目录更改到其它位置。操作系统：CentOS 7.6 数据库：MySQL 8.0 查看我本人更多原创文章，请点击

zry 2020-11-11
炒饭

炒饭

W3CSchool教程
HTML 教程
CSS 教程
Bootstrap 教程
Javascript 教程
jQuery 教程
后端教程
C 教程
Java 教程
PHP 教程
Python 教程
Go 教程
移动开发
Android 教程
Swift 教程
Kotlin 教程
jQuery Mobile 教程
ionic 教程
关于我们
新闻动态
联系方式
招聘英才
安科实验室
帮助与反馈

安科网(Ancii)，中国第一极客网

Copyright © 2013 - 2019 Ancii.com

京ICP备18063983号 京公网安备11010802014868号