mysql - 为什么auto_increment id不逐一递增，怎么设置？

Question

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

 `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 |
    +-----+

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

 alter table uid AUTO_INCREMENT=1

Answer 1

这是设计使然，并在 MariaDB Galera Cluster - Known Limitations 中报告:

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.

理性在 Managing Auto Increments with Multi Masters 中解释。 , 这也是为什么观察到的自动增量与聚类数量步长相同的原因。

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.

即使没有集群，由于事务回滚和删除记录，依赖自增列成为密集序列也很少是一个“好”主意。