为什么auto_increment id不一一增加，怎么设置呢?

2022-01-15 00:00:00 mariadb mysql primary-key

我有一个 MariaDB Galera 集群(3 个节点)，我将 uid 设置为自动增加并作为表的主键

I have a MariaDB Galera Cluster(3 nodes), I set uid to increase automatically and be the primary key of the table as 

 `uid       | int(11)     | NO   | PRI | NULL    | auto_increment`. 


 MariaDB [hello_cluster]> select uid from table order by uid limit 10;
    +-----+
    | uid |
    +-----+
    |   3 |
    |   6 |
    |   9 |
    |  12 |
    |  15 |
    |  18 |
    |  21 |
    |  24 |
    |  27 |
    |  30 |
    +-----+

我试过下面的命令，还是不行

I tried the following command, and it does not work

 alter table uid AUTO_INCREMENT=1

推荐答案

这是设计使然，报告在 MariaDB Galera 集群 - 已知限制:

This is by design and is reported in MariaDB Galera Cluster - Known Limitations:

不要依赖自动递增值是连续的.Galera 使用基于自增增量的机制来生成唯一的非冲突序列，因此在每个单个节点上序列都会有间隙.

Do not rely on auto-increment values to be sequential. Galera uses a mechanism based on autoincrement increment to produce unique non-conflicting sequences, so on every single node the sequence will have gaps.

使用 Multi Masters 管理自动增量中解释了原因，这也是为什么观察到的自增量与聚类数具有相同的步长.

The rational is explained in Managing Auto Increments with Multi Masters, and is also why the observed auto-increment has the same step as the number of clusters.

MySQL 有系统变量 auto_increment_increment 和 auto_increment_offset 用于管理多主环境中的自动增量序列".使用这些变量，可以设置多主复制，其中每个主节点中的自动递增序列交错，并且集群中不应发生冲突.无论哪个 master(s) 获得 INSERT.

MySQL has system variables auto_increment_increment and auto_increment_offset for managing auto increment 'sequences' in multi master environment. Using these variables, it is possible to set up a multi master replication, where auto increment sequences in each master node interleave, and no conflicts should happen in the cluster. No matter which master(s) get the INSERTs.

即使没有集群，由于事务回滚和删除记录，依靠自动递增列成为密集序列也很少是一个好"的主意.

Even without clusters, it is rarely a "good" idea to rely on auto-increment columns to be dense sequences due to transaction rollbacks and deleted records.

相关文章