mysql - 为什么 mysql innodb 表 - 自动增量列跳跃超过 1 个数字，即使像 'auto_increment_increment' 这样的变量设置为 1？

Question

为什么 mysql innodb 表 - 自动增量列跳跃超过 1 个数字，即使像“auto_increment_increment”这样的变量设置为 1 ？

注意:表上没有使用更新或删除查询。

Answer 1

Auto_increment 值也会在 INSERT 查询时递增。诀窍在于，即使没有添加实际行，它也可以递增。有几种情况，我将使用小演示表来解释它们:

CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY (name)
) ENGINE = INNODB;

root@localhost/test> Show table status like 'test'\G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1 <-- Next auto_increment field value
    Create_time: 2016-06-06 14:39:48
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

情况#1:插入忽略

root@localhost/test> INSERT IGNORE INTO test (name) VALUES ("One");
Query OK, 1 row affected (0.00 sec)

root@localhost/test> INSERT IGNORE INTO test (name) VALUES ("One");
Query OK, 0 rows affected (0.01 sec)

root@localhost/test> SELECT * FROM test ORDER BY id;
+----+------+
| id | name |
+----+------+
|  1 | One  |
+----+------+
1 row in set (0.00 sec)

root@localhost/test> Show table status like 'test'\G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 3
    Create_time: 2016-06-06 14:39:48
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

情况#2:在重复 key 更新时插入...

root@localhost/test> INSERT INTO test (name) VALUES ("One") ON DUPLICATE KEY UPDATE name=VALUES(name);
Query OK, 0 rows affected (0.00 sec)

root@localhost/test> SELECT * FROM test ORDER BY id;
+----+------+
| id | name |
+----+------+
|  1 | One  |
+----+------+
1 row in set (0.00 sec)

root@localhost/test> INSERT INTO test (name) VALUES ("One") ON DUPLICATE KEY UPDATE name=VALUES(name);
Query OK, 0 rows affected (0.00 sec)

root@localhost/test> Show table status like 'test'\G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 5
    Create_time: 2016-06-06 14:39:48
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

情况#3:插入有错误

root@localhost/test> INSERT INTO test (name) VALUES ("One");
ERROR 1062 (23000): Duplicate entry 'One' for key 'name'

root@localhost/test> Show table status like 'test'\G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 6
    Create_time: 2016-06-06 14:39:48
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

您可以在本文中详细了解发生这些漏洞的原因:https://www.percona.com/blog/2011/11/29/avoiding-auto-increment-holes-on-innodb-with-insert-ignore/或 MySQL 文档:http://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-lock-modes