MySQL自增列无效的解决方法 (mysql数据库自增无效)

MySQL是世界上更流行的关系型数据库管理系统之一，它提供了一种自动增长的列，即自增列，使得每次向表中插入新数据时，自增列的值会自动加1，从而确保每行数据的唯一性。然而，在实际使用中，有时会出现MySQL自增列无效的情况，这可能会导致数据不一致或者冲突等问题。本文将介绍几种常见的。

一、检查表结构

MySQL的自增列是通过自增属性设置实现的，因此，首先需要检查表结构，确保自增属性已经正确设置。可以使用以下命令查询表结构：

SHOW CREATE TABLE table_name;

其中，table_name是需要查询的表名。如果自增属性已经正确设置，应该能看到如下语句：

AUTO_INCREMENT = value

其中value就是自增列的下一个值。

如果自增属性未设置，可以使用以下命令进行设置：

ALTER TABLE table_name MODIFY column_name INT AUTO_INCREMENT;

其中，table_name是需要修改的表名，column_name是需要设置为自增属性的列名，INT是列的数据类型。如果需要设置其他数据类型，可以替换INT为其他类型。

二、检查插入语句

如果表结构已经正确设置了自增属性，那么可能是插入语句的问题导致自增列无效。可以使用以下命令查看正在执行的所有语句：

SHOW PROCESSLIST;

如果发现有UPDATE或DELETE语句，可以用KILL语句中止这些语句。

如果发现有INSERT语句，可以检查语句是否指定了自增列，例如：

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

如果未指定自增列，MySQL会默认使用当前自增列的更大值加1。如果当前自增列是0，那么新插入的数据的自增列值也是0，这可能会导致冲突。因此，要确保INSERT语句指定了正确的自增列，例如：

INSERT INTO table_name (column1, column2, auto_increment_column) VALUES (value1, value2, null);

其中，auto_increment_column是自增列的列名，null表示自动分配下一个自增列值。

三、检查事务

如果自增列无效的问题仍然存在，可能是事务问题导致的。MySQL的事务是一组SQL语句，它们被当作一个单独的工作单元来处理。在事务中，如果一条语句出现问题，整个事务将被回滚，所有的变化都将被撤销，因此，需要确保所有的操作都在同一个事务中完成，例如：

START TRANSACTION;

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

UPDATE table_name SET column2 = new_value WHERE column1 = value1;

COMMIT;

其中，START TRANSACTION表示开启事务，COMMIT表示提交事务。如果事务提交成功，所有的操作将被永久保存。如果出现问题，可以使用ROLLBACK回滚事务。

四、检查锁表

如果多个用户同时向表中插入新数据，并且使用自增列作为插入的唯一标识，可能会导致冲突或者数据不一致。为了避免这种问题，可以使用锁表机制。锁表是指在进行一定的操作前，先将需要操作的表进行锁定，使得其他用户无法进行读写操作，确保数据的一致性。在使用自增列时，可以使用如下命令锁表：

LOCK TABLES table_name WRITE;

其中，table_name是需要锁定的表名，WRITE表示读写锁。如果需要解锁表，可以使用以下命令：

UNLOCK TABLES;

五、使用分布式ID生成器

MySQL的自增列机制虽然简单有效，但是有一些限制。自增列只能是单调递增的，不能在多个服务器之间进行复制。如果需要使用分布式集群，那么不同的节点之间难以保证自增列的唯一性。为了解决这个问题，可以使用分布式ID生成器。分布式ID生成器是一种可以生成全局唯一ID的工具，它可以在多个节点之间进行复制，并且保证生成的ID是单调递增的。目前市面上有很多分布式ID生成器的实现，例如Twitter的Snowflake算法、美团的Leaf算法、百度的UidGenerator算法等。

以上就是。在实际使用中，如果出现了自增列无效的问题，可以根据以上方法逐一排查，找到并解决问题，保证数据的正确性和一致性。

相关问题拓展阅读：

• 关于mysql数据库的自动增长列的问题
• orm框架sequelize 向mysql数据库插入数据，id设置为自增的主键，总是出错，显示id不能为null？
• MySQL主键自增值为什么有“空洞”?

关于mysql数据库的自动增长列的问题

先把引用该列的外键约束Disable掉，修改为自增列后，再启用外键约束。

orm框架sequelize 向mysql数据库插入数据，id设置为自增的主键，总是出错，显示id不能为null？

你可以自zhidao己定义圆埋表结构，注意下面第三个参数，我把链接茄弊发给你吧，你参考一下，不是让专你橘纳蚂属按下面这样写，只是提供参考

var a= sequelize.define(‘a’, {id: {type: Sequelize.STRING, max: 20},Typess: {type: Sequelize.STRING, max: 20},startStation: {type: Sequelize.STRING, max: 20},endStation: {type: Sequelize.STRING, max: 20},R_Date: {type: Sequelize.STRING, max: 20},Distance: {type: Sequelize.STRING, max: 20} },{ freezeTableName: true,timestamps: false})

MySQL主键自增值为什么有“空洞”?

* G r e a t S Q L 社 区 原 创 内 容 未 经 授缺旦 权 不 得 随 意 使 用 ， 转 载 请 联 系 小 编 并 注 明 来 源 。

本文在测试 insert 、 insert ignore 、 replace into 三种数据插入方式的时候，发现插入数据的时候在表内存在带有“唯一特性”的值重复的情况下三种语句的处理方式。最终发现了MySQL主键自增值“空洞”了

测试场景为MySQL 8.0：

1、建表，包含主键及唯一约束

2、写入初始测试数据

insert方式插入数据在处理过程中发生主键传统等错误时候，语句会被终止，并告知错误的原因。而使用insert ignore的方式进行数据插入，则会忽略插入错误的行继续插入没有问题的行记录，最终以warning进行提示。

在测试过程中惊奇地发现测试表中的主键自增列发生了改变，经过之前的操作已经变成了7：

最后，replace into的方式导致如果插入数据是原值的情况，然后主键冲突，就对该主键的内容进行替换，如果唯一键冲突，唯一值所在行就会删除，重新插入新的行，如果都不冲突则正常插入数据。

上文测试了三种插入数据的方式，可是测试过程中发现插入失败的时候，自增列的自增值居然变大了。

为了更好地理解，首先让我们具体认识一下 AUTO_INCREMENT 属性在不同的存储引擎当中，其自增值的保存策略有所不同：

可是理解了这个并不能马上理解现在的这个问题，我们知道当数据进行数据插入的时候，如果插入的数据中自增列不指定其值的时候，该列就会以当前自增值作为其值，如果指定其值就会插入指定的值，当然也有满足唯一的原则，同时插入指定值大于自增值时，自增值也会随之改变。而自增值使用的算法是以 auto_increment_offset 参数决定开始，以 auto_increment_increment 决定步长来实现的,默认情况都是1：

那么，为什么会出现插入数据未成功，自增值却变大了的情况呢？原因很简伏首扰单，用插入数据的流程来进行分析：

因为自增值的保存是在插入数据真正执行前完成的，因此就会出现这种问题了。

这个时候有人就会想了，可以把 AUTO_INCREMENT 值改回去吗？简单测试一下：

显然，如果自增值往大的方向修改是没有问题的，但如果往小的修改就要看目前数据库插入的值是否会将修改后的自增值“卡”在中间，如果出现这种情况是没办法改回去的，原因显而易见，自增属性与主键配套使用，如果现在表里id=4和id=6之间差了个5的值，将自增值改回5，当插入数据时，自增值就会插入5的值并且把自增值加1，问题就出现了，此时自增值再进行插入就违背了唯一的原则了

在生产环境中还存在很多类似的问题，如：

在插入过程中，开启了一个事务，在插入的时候发生了事务的回滚，当回滚后再次插入数据，发现自增值又出现了“空洞”，那么问题又来了，为什么在插入数据的时候发生了回滚，数据回滚了，自增值却没有回滚呢？为了更直观，继续测试，假设有两个事务。

测试前数据：

进行测试：

测试后数据：

发现还是“空洞”了，而且此时答案也十分清楚了，在不同事务在进行写入操作的时候申请自增值，为了避芹型免两个事务申请到相同的自增值，所以需要对其加锁，按照一定顺序进行申请自增值。根据前面的例子来看：

此时就出现了前面说到的问题了，没办法回滚，回滚就会出现自增值“卡”在中间的情况了，以后有机会再继续聊聊自增锁的问题。

En j o y G r e a t S Q L : )

《深 入 浅 出 M G R 》 视 频 课 程

戳 此 小 程 序 即 可 直 达 B 站

l i . c om/medialist/play/?

business=space_collection&business_id=343928&desc=0

文 章 推 荐 ：

G r e a t S Q L 是 由 万 里 数 据 库 维 护 的 M y S Q L 分 支 ， 专 注 于 提 升 M G R 可 靠 性 及 性 能 ， 支 持 I n n o D B 并 行 查 询 特 性 ， 是 适 用 于 金 融 级 应 用 的 M y S Q L 分 支 版 本 。

G i t e e :

h t t p s : / / g i t e e . c o m / G r e a t S Q L / G r e a t S Q L

G i t H u b :

h t t p s : / / g i t h u b . c o m / G r e a t S Q L / G r e a t S Q L

h t t p s : / / s p a c e . b i l i b i l i . c o m // v i d e o

微 信 & Q Q 群 ：

可 扫 码 添 加 G r e a t S Q L 社 区 助 手 微 信 好 友 ， 发 送 验 证 信 息 “ 加 群 ” 加 入

G r e a t S Q L / M G R 交 流 微 信 群 ，

亦 可 直 接 扫 码 加 入

G r e a t S Q L / M

G R 交 流 Q Q 群

。

关于mysql数据库自增无效的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站。

香港服务器首选树叶云，2H2G首月10元开通。
树叶云（shuyeidc.com）提供简单好用，价格厚道的香港/美国云服务器和独立服务器。IDC+ISP+ICP资质。ARIN和APNIC会员。成熟技术团队15年行业经验。