AUTO_INCREMENT

    AUTO_INCREMENT 是用于自动填充缺省列值的列属性。当 INSERT 语句没有指定 AUTO_INCREMENT 列的具体值时，系统会自动地为该列分配一个值。

    出于性能原因，自增编号是系统批量分配给每台 TiDB 服务器的值（默认 3 万个值），因此自增编号能保证唯一性，但分配给 INSERT 语句的值仅在单台 TiDB 服务器上具有单调性。

    1. INSERT INTO t(c) VALUES (1);
    2. INSERT INTO t(c) VALUES (2);
    3. INSERT INTO t(c) VALUES (3), (4), (5);
    1. SELECT * FROM t;
    2. +----+---+
    3. | id | c |
    4. +----+---+
    5. | 1  | 1 |
    6. | 2  | 2 |
    7. | 3  | 3 |
    8. | 4  | 4 |
    9. | 5  | 5 |
    10. +----+---+
    11. 5 rows in set (0.01 sec)

    此外，AUTO_INCREMENT 还支持显式指定列值的插入语句，此时 TiDB 会保存显式指定的值：

    1. INSERT INTO t(id, c) VALUES (6, 6);
    1. SELECT * FROM t;
    2. +----+---+
    3. | id | c |
    4. +----+---+
    5. | 1  | 1 |
    6. | 2  | 2 |
    7. | 3  | 3 |
    8. | 4  | 4 |
    9. | 5  | 5 |
    10. | 6  | 6 |
    11. +----+---+
    12. 6 rows in set (0.01 sec)

    以上用法和 MySQL 的 AUTO_INCREMENT 用法一致。但在隐式分配的具体值方面，TiDB 和 MySQL 之间具有较为显著的差异。

    TiDB 实现 AUTO_INCREMENT 隐式分配的原理是，对于每一个自增列，都使用一个全局可见的键值对用于记录当前已分配的最大 ID。由于分布式环境下的节点通信存在一定开销，为了避免写请求放大的问题，每个 TiDB 节点在分配 ID 时，都申请一段 ID 作为缓存，用完之后再去取下一段，而不是每次分配都向存储节点申请。例如，对于以下新建的表：

    1. CREATE TABLE t(id int UNIQUE KEY AUTO_INCREMENT, c int);

    假设集群中有两个 TiDB 实例 A 和 B，如果向 A 和 B 分别对 t 执行一条插入语句：

    警告：

    在集群中有多个 TiDB 实例时，如果表结构中有自增 ID，建议不要混用显式插入和隐式分配（即自增列的缺省值和自定义值），否则可能会破坏隐式分配值的唯一性。

    例如在上述示例中，依次执行如下操作：

    1. 客户端向实例 B 插入一条将 id 设置为 2 的语句 INSERT INTO t VALUES (2, 1)，并执行成功。
    2. 客户端向实例 A 发送 INSERT 语句 INSERT INTO t (c) (1)，这条语句中没有指定 id 的值，所以会由 A 分配。当前 A 缓存了 [1, 30000] 这段 ID，可能会分配 2 为自增 ID 的值，并把本地计数器加 1。而此时数据库中已经存在 id 为 2 的数据，最终返回 Duplicated Error 错误。

    TiDB 保证 AUTO_INCREMENT 自增值在单台服务器上单调递增。以下示例在一台服务器上生成连续的 AUTO_INCREMENT 自增值 1-3：

    1. CREATE TABLE t (a int PRIMARY KEY AUTO_INCREMENT, b timestamp NOT NULL DEFAULT NOW());
    2. INSERT INTO t (a) VALUES (NULL), (NULL), (NULL);
    3. SELECT * FROM t;
    1. Query OK, 0 rows affected (0.11 sec)
    2. Query OK, 3 rows affected (0.02 sec)
    3. Records: 3  Duplicates: 0  Warnings: 0
    4. +---+---------------------+
    5. | a | b                   |
    6. +---+---------------------+
    7. | 1 | 2020-09-09 20:38:22 |
    8. | 2 | 2020-09-09 20:38:22 |
    9. | 3 | 2020-09-09 20:38:22 |
    10. +---+---------------------+
    11. 3 rows in set (0.00 sec)

    TiDB 能保证自增值的单调性，但并不能保证其连续性。参考以下示例：

    1. CREATE TABLE t (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, a VARCHAR(10), cnt INT NOT NULL DEFAULT 1, UNIQUE KEY (a));
    2. INSERT INTO t (a) VALUES ('A'), ('B');
    3. SELECT * FROM t;
    4. INSERT INTO t (a) VALUES ('A'), ('C') ON DUPLICATE KEY UPDATE cnt = cnt + 1;
    5. SELECT * FROM t;
    1. Query OK, 0 rows affected (0.00 sec)
    5. +----+------+-----+
    6. | id | a    | cnt |
    7. +----+------+-----+
    8. |  1 | A    |   1 |
    9. |  2 | B    |   1 |
    10. +----+------+-----+
    11. 2 rows in set (0.00 sec)
    13. Query OK, 3 rows affected (0.00 sec)
    14. Records: 2  Duplicates: 1  Warnings: 0
    16. +----+------+-----+
    17. | id | a    | cnt |
    18. +----+------+-----+
    19. |  1 | A    |   2 |
    20. |  2 | B    |   1 |
    21. |  4 | C    |   1 |
    22. +----+------+-----+
    23. 3 rows in set (0.00 sec)

    在以上示例 INSERT INTO t (a) VALUES ('A'), ('C') ON DUPLICATE KEY UPDATE cnt = cnt + 1; 语句中，自增值 3 被分配为 A 键对应的 id 值，但实际上 3 并未作为 id 值插入进表中。这是因为该 INSERT 语句包含一个重复键 A，使得自增序列不连续，出现了间隙。该行为尽管与 MySQL 不同，但仍是合法的。MySQL 在其他情况下也会出现自增序列不连续的情况，例如事务被中止和回滚时。

    如果在另一台服务器上执行插入操作，那么 AUTO_INCREMENT 值的顺序可能会剧烈跳跃，这是由于每台服务器都有各自缓存的 AUTO_INCREMENT 自增值。

    1. CREATE TABLE t (a INT PRIMARY KEY AUTO_INCREMENT, b TIMESTAMP NOT NULL DEFAULT NOW());
    2. INSERT INTO t (a) VALUES (NULL), (NULL), (NULL);
    3. INSERT INTO t (a) VALUES (NULL);
    4. SELECT * FROM t;

    以下示例在最先的一台服务器上执行一个插入 INSERT 操作，生成 AUTO_INCREMENT 值 4。因为这台服务器上仍有剩余的 AUTO_INCREMENT 缓存值可用于分配。在该示例中，值的顺序不具有全局单调性：

    1. INSERT INTO t (a) VALUES (NULL);
    2. Query OK, 1 row affected (0.01 sec)
    4. SELECT * FROM t ORDER BY b;
    5. +---------+---------------------+
    6. | a       | b                   |
    7. +---------+---------------------+
    8. |       1 | 2020-09-09 20:38:22 |
    9. |       2 | 2020-09-09 20:38:22 |
    10. |       3 | 2020-09-09 20:38:22 |
    11. | 2000001 | 2020-09-09 20:43:43 |
    12. |       4 | 2020-09-09 20:44:43 |
    13. +---------+---------------------+
    14. 5 rows in set (0.00 sec)
    1. INSERT INTO t (a) VALUES (NULL);
    2. Query OK, 1 row affected (0.01 sec)
    4. SELECT * FROM t ORDER BY b;
    5. +---------+---------------------+
    6. | a       | b                   |
    7. +---------+---------------------+
    8. |       1 | 2020-09-09 20:38:22 |
    9. |       2 | 2020-09-09 20:38:22 |
    10. |       3 | 2020-09-09 20:38:22 |
    11. | 2000001 | 2020-09-09 20:43:43 |
    12. |       4 | 2020-09-09 20:44:43 |
    13. | 2030001 | 2020-09-09 20:54:11 |
    14. +---------+---------------------+
    15. 6 rows in set (0.00 sec)

    TiDB 服务器频繁重启可能导致 AUTO_INCREMENT 缓存值被快速消耗。在以上示例中，最先的一台服务器本来有可用的缓存值 [5-3000]。但重启后，这些值便丢失了，无法进行重新分配。

    用户不应指望 AUTO_INCREMENT 值保持连续。在以下示例中，一台 TiDB 服务器的缓存值为 [2000001-2030000]。当手动插入值 2029998 时，TiDB 取用了一个新缓存区间的值：

    1. INSERT INTO t (a) VALUES (2029998);
    2. Query OK, 1 row affected (0.01 sec)
    4. INSERT INTO t (a) VALUES (NULL);
    5. Query OK, 1 row affected (0.01 sec)
    7. Query OK, 1 row affected (0.00 sec)
    9. INSERT INTO t (a) VALUES (NULL);
    10. Query OK, 1 row affected (0.02 sec)
    12. INSERT INTO t (a) VALUES (NULL);
    13. Query OK, 1 row affected (0.01 sec)
    15. SELECT * FROM t ORDER BY b;
    16. +---------+---------------------+
    17. | a       | b                   |
    18. +---------+---------------------+
    19. |       1 | 2020-09-09 20:38:22 |
    20. |       2 | 2020-09-09 20:38:22 |
    21. |       3 | 2020-09-09 20:38:22 |
    22. | 2000001 | 2020-09-09 20:43:43 |
    23. |       4 | 2020-09-09 20:44:43 |
    24. | 2030001 | 2020-09-09 20:54:11 |
    25. | 2029998 | 2020-09-09 21:08:11 |
    26. | 2029999 | 2020-09-09 21:08:11 |
    27. | 2030000 | 2020-09-09 21:08:11 |
    28. | 2060001 | 2020-09-09 21:08:11 |
    29. | 2060002 | 2020-09-09 21:08:11 |
    30. +---------+---------------------+
    31. 11 rows in set (0.00 sec)

    以上示例插入 2030000 后，下一个值为 2060001，即顺序出现跳跃。这是因为另一台 TiDB 服务器获取了中间缓存区间 [2030001-2060000]。当部署有多台 TiDB 服务器时，AUTO_INCREMENT 值的顺序会出现跳跃，因为对缓存值的请求是交叉出现的。

    TiDB 自增 ID 的缓存大小在早期版本中是对用户透明的。从 v3.1.2、v3.0.14 和 v4.0.rc-2 版本开始，TiDB 引入了 AUTO_ID_CACHE 表选项来允许用户自主设置自增 ID 分配缓存的大小。例如：

    1. CREATE TABLE t(a int AUTO_INCREMENT key) AUTO_ID_CACHE 100;
    2. Query OK, 0 rows affected (0.02 sec)
    4. INSERT INTO t VALUES();
    5. Query OK, 1 row affected (0.00 sec)
    6. Records: 1  Duplicates: 0  Warnings: 0
    8. SELECT * FROM t;
    9. +---+
    10. | a |
    11. +---+
    12. | 1 |
    13. +---+
    14. 1 row in set (0.01 sec)

    此时如果将该列的自增缓存无效化，重新进行隐式分配：

    1. DELETE FROM t;
    2. Query OK, 1 row affected (0.01 sec)
    4. RENAME TABLE t to t1;
    5. Query OK, 0 rows affected (0.01 sec)
    7. INSERT INTO t1 VALUES()
    8. Query OK, 1 row affected (0.00 sec)
    10. SELECT * FROM t;
    11. +-----+
    12. | a   |
    13. +-----+
    14. | 101 |
    15. +-----+
    16. 1 row in set (0.00 sec)

    可以看到再一次分配的值为 101，说明该表的自增 ID 分配缓存的大小为 100。

    此外如果在批量插入的 INSERT 语句中所需连续 ID 长度超过 AUTO_ID_CACHE 的长度时，TiDB 会适当调大缓存以便能够保证该语句的正常插入。

    从 v3.0.9 和 v4.0.rc-1 开始，和 MySQL 的行为类似，自增列隐式分配的值遵循 session 变量 @@auto_increment_increment 和 @@auto_increment_offset 的控制，其中自增列隐式分配的值 (ID) 将满足式子 (ID - auto_increment_offset) % auto_increment_increment == 0。

    • 定义的列必须为主键或者索引的首列。
    • 只能定义在类型为整数、FLOAT 或 DOUBLE 的列上。
    • 不支持与列的默认值 DEFAULT 同时指定在同一列上。
    • 不支持使用 ALTER TABLE 来添加 AUTO_INCREMENT 属性。
    • 支持使用 ALTER TABLE 来移除 AUTO_INCREMENT 属性。但从 TiDB 2.1.18 和 3.0.4 版本开始，TiDB 通过 session 变量 @@tidb_allow_remove_auto_inc 控制是否允许通过 ALTER TABLE MODIFY 或 ALTER TABLE CHANGE 来移除列的 AUTO_INCREMENT 属性，默认是不允许移除。