MySQL自增主键增加10 [英] MySQL Auto increment primary key increases by 10

问题描述

在 azure 上，我创建了一个新的 MySQL 数据库实例.在这个数据库中，我使用这个脚本创建了一个表:

On azure I created a new MySQL Database instance. In this db I create a table using this script:

CREATE TABLE ROLES(
  ID INTEGER PRIMARY KEY AUTO_INCREMENT,
  ROLE_NAME VARCHAR(30) NOT NULL
);

然后我使用这个脚本插入值:

Then I insert values using this script:

INSERT INTO `beezzy`.`roles` (`ROLE_NAME`) VALUES ('admin');
INSERT INTO `beezzy`.`roles` (`ROLE_NAME`) VALUES ('owner');
INSERT INTO `beezzy`.`roles` (`ROLE_NAME`) VALUES ('consultant');

执行后表包含这样的行:

after execution table contains such rows:

为什么 DB 会生成像11"和21"这样的 ID?我在本地机器上运行相同的脚本，一切正常.ID 为1"、2"、3"

Why DB generates IDs like '11' and '21'? I run the same script on my local machine and everything works fine. IDs was '1', '2', '3'

推荐答案

请运行以下查询.

SELECT @@auto_increment_increment

如果值大于 1，则通过以下查询将其设置为 1:

If the value is more than 1 then set it to 1 by the following query:

SET @@auto_increment_increment=1;

注意:此更改仅对当前连接可见.

Note: This change is visible for the current connection only.

为了全局设置它以便其他连接也可以看到您需要为全局和会话设置它的更改.

In order to set it globally so that other connections can also see the change you need to set it for global and session too.

SET @@GLOBAL.auto_increment_increment = 1;

SET @@SESSION.auto_increment_increment = 1;

因此其他连接现在可以看到此更改.

So other connections can see this change now.

更多:

如果您重新启动 MySQL 服务器，此值将被重置.为了使此更改永久化，您需要在 my.cnf [for linux] 或 my.ini [forwindows] 文件.

This value will be reset if you restart your MySQL server. In order to make this change permanent you need to write this variable under [mysqld] secion in your my.cnf [for linux] or my.ini [for windows] file.

[mysqld]
auto-increment-increment = 1

这篇关于MySQL自增主键增加10的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！