问题描述
我有一个 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 使用基于自增增量的机制来生成唯一的非冲突序列,因此在每个单个节点上序列都会有间隙.
使用 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.
即使没有集群,由于事务回滚和删除记录,依靠自动递增列成为密集序列也很少是一个好"的主意.
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.
这篇关于为什么auto_increment id不一一增加,怎么设置呢?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!