如何更换MySQL表的自增主键

最新推荐文章于 2025-11-22 04:58:29 发布
原创 最新推荐文章于 2025-11-22 04:58:29 发布 · 619 阅读 · 9 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库

一、问题

怎么讲MySQL表的自增主键更改为分布式ID？解决这个问题的核心在于上线时间段，会有两种id的生成方式。

为什么需要替换？数据同步的时候。假设A机房要迁移到B机房，两个机房都有流量。这里我们只讨论B机房是新机房，存量数据不会有冲突，只有增量数据才有冲突的情况。

在这里插入图片描述

二、技术点

MySQL主键值的设置有不同的方案。

2.1MySQL自增主键

MySQL自增主键的核心逻辑是通过内存计数器分配唯一递增ID，但不同版本在计数器持久化和锁策略上存在关键差异，直接影响ID连续性和并发性能。

基础生成逻辑（各版本通用）

自增主键依赖表级内存计数器，分配规则如下：

  1. 自动分配：插入时未指定ID/指定为0/null，则用当前计数器值，分配后计数器+1（步长默认1，可通过auto_increment_increment调整）。
  2. 手动指定：若指定ID＞当前计数器，计数器更新为ID+1；若ID≤计数器且未冲突，计数器不变。
  3. 不连续场景：事务回滚、唯一键冲突、批量插入失败会导致ID“浪费”（分配后未使用，不会回滚）。
版本差异核心对比

不同版本的关键区别集中在计数器持久化和锁模式，直接影响重启后ID连续性和并发插入性能：

特性MySQL 5.7及更早MySQL 8.0及以后
计数器存储仅内存，重启后重置为max(id)+1持久化到redo log，重启后恢复原值
默认锁模式innodb_autoinc_lock_mode=1（连续模式）innodb_autoinc_lock_mode=2（交错模式）
批量插入锁策略表级锁（语句结束释放）轻量互斥锁（分配ID后立即释放）
ID连续性批量插入保证连续，简单插入可能不连续所有插入可能不连续，但并发性能最优
关键机制：锁模式

锁模式决定并发插入时ID的分配效率和连续性，各模式适用于不同场景：

锁模式值名称适用场景并发性能ID连续性
0传统模式基于语句的复制（SBR）差严格连续
1连续模式混合复制（默认，平衡性能）中批量连续
2交错模式基于行的复制（RBR/GTID）优不保证

2.2分布式ID生成方案

雪花算法

在分布式系统中，生成全局唯一且有序的ID是关键挑战。雪花算法（Snowflake）作为Twitter开源的经典方案，通过巧妙的位结构设计，平衡了唯一性、有序性和性能需求，成为分布式ID生成的事实标准。

核心原理：64位ID的精妙结构

雪花算法将64位长整型ID划分为4个部分，每部分承担特定职责：

位数名称作用
1位符号位固定为0（确保ID为正数）
41位时间戳记录当前时间-起始时间的毫秒差，支持约69年（2^41/1000/365/24/60/60）
10位机器ID含5位数据中心ID+5位节点ID，支持最多1024台机器（2^10）
12位序列号同一毫秒内自增，支持每节点每毫秒生成4096个ID（2^12）

生成流程：

  1. 时间戳：以自定义起始时间（如2020-01-01）为基准，计算当前时间偏移量；
  2. 时钟回拨处理：若当前时间 < 上次生成时间，抛出异常或等待时钟同步；
  3. 序列号生成：同一毫秒内序列号自增，溢出则等待下一毫秒；
  4. 组合ID：通过位运算拼接时间戳、机器ID和序列号，生成64位整数。
核心优势：为何成为分布式首选？

  1. 全局唯一性：时间戳+机器ID+序列号的三重组合，确保分布式环境下ID无重复，即使多节点并发也不会冲突。

  2. 高性能与低延迟：纯内存计算，无需数据库或中心节点，单机每秒可生成 400万+ ID（1000ms × 4096序列号），适合高并发场景（如秒杀、订单系统）。

  3. 趋势有序性：时间戳位于高位，ID天然按生成时间递增，适合作为数据库主键（减少索引碎片）或排序字段。

  4. 无中心依赖：去中心化设计，避免单点故障风险，支持集群横向扩展。

NT ID

公司自研的Non-Timestamp ID，非基于时间戳生成的全球唯一 ID。算是号段模式的一种，从数据库批量预分配ID段（如 [1001, 2000]），缓存在内存中逐个分配，用完后再申请新号段[4001,5000]。

三、方案

对于上面的具体场景，有两种方案可以选择：

方案1：仍然使用自增主键，不同机房使用不同的数据范围方案2：使用NT ID
图示在这里插入图片描述在这里插入图片描述Q：为什么需要这么多操作？
A：代码发布过程中，如果NTID生成的值有落库，自增主键会在此数值之上增加，可能和NTID生成的值有冲突
优点1. 开发量：无需任何改造解决主键冲突问题，可在创建表或者通过ALTER TABLE 表名 AUTO_INCREMENT = 1000;修改自增主键值1. 完美的支持双向同步
缺点1. 同步方式：该方案只支持单向同步
2. 风险：需要确保A中的表自增主键不会迅速增长到B的值、确保A表中不会设置较大主键
3.全面性：除非A机房后续废弃不用，否则增量同步的数据终会出问题		1. 开发量：需要对表的创建操作设置主键值
2. 主键类型是不是bigint，防止越界
3. 通过接口提供的主键值，过长是否会有问题。如js对int64会丢失精度

最后

大家如果喜欢我的文章，可以关注我的公众号（程序员麻辣烫）

我的个人博客为：https://shidawuhen.github.io/

往期文章回顾：

  1. 设计模式
  2. 招聘
  3. 思考
  4. 存储
  5. 算法系列
  6. 读书笔记
  7. 小工具
  8. 架构
  9. 网络
  10. Go语言
mysql主键自增原理
JustinMars的博客
08-09 944
MySQL 中的主键自增（Auto Increment）是一种用于在插入新记录时自动生成唯一标识符（即主键）的机制。自增字段通常用于唯一标识表中的每一行记录，尤其在主键列中非常常见。
修改mysql表自增主键初始值
weixin_37701609的博客
11-07 927
主键过长的问题 如何修改mysql主键初始值
mysql清空自增主键
weixin_41469705的博客
07-14 457
清空MySQL自增主键的方法及示例 引言 在MySQL数据库中，经常会遇到需要清空自增主键的情况，例如重置表数据或者清空测试数据等。本文将介绍如何清空MySQL中的自增主键，并给出相应的代码示例。 清空自增主键的方法 清空MySQL表的自增主键可以通过两种方法实现： 删除表数据后重新插入数据 重置自增主键的值 方法一：...
Mysql 对表自增主键重新排序
Hong_pro的博客
02-22 341
Mysql自增主键列重新排序
mysql 添加自增主键语法
weixin_33230433的博客
07-03 852
实现mysql添加自增主键语法教程 1. 整体流程 首先，让我们来看一下实现“mysql 添加自增主键”的整体流程： 步骤 操作 1 创建表 2 添加主键列 3 设置主键为自增 2. 具体步骤和代码 步骤1：创建表 首先，我们需要创建一个表，例如名为users的表，用于存储用户信息。 登录后复制...
mysql 新增自增主键id语句
weixin_42346693的博客
07-03 243
实现"mysql 新增自增主键id语句"指南 流程图 #render_6_1043236996-svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#render_6_1043236996-svg .error-...
MySQL自增主键
weixin_72431427的博客
08-20 2818
可以在MySQL数据库中实现自增主键，使每次插入数据时，主键值会自动增长，避免手动指定主键值的麻烦。自增主键可以方便地确保每条记录的唯一性，并且提供了便利的数据管理和查询方式。
深入理解MySQL自增主键
2303_78378466的博客
12-05 1408
因为这类语句在申请自增 id 的时候，是可以精确计算出需要多少个 id 的，然后一次性申请，申请完成后锁就可以释放了。了解过分布式 id 的小伙伴一定知道，为了避免两个库生成的主键发生冲突，我们可以让一个库的自增 id 都是奇数，另一个库的自增 id 都是偶数。现在有两个并行执行的事务 A 和 B，在申请自增值的时候，为了避免两个事务申请到相同的自增 id，肯定要加锁，然后顺序申请，对吧。理解了 MySQL 自增值到底保存在哪里以后，我们再来看看自增值的修改机制，并以此引出第一种自增值不连续的场景。
mysql8 将主键更改为的自增主键
qq_26408545的博客
06-14 898
通过以上步骤，你可以使用 ROW_NUMBER() 函数生成连续的主键，并直接在表上更新这些值。这个方法不需要临时表，但仍需小心操作，以确保数据的一致性和完整性。：如果表中存在外键关系，在更新主键时需要特别注意外键的维护。更新完主键后，可能需要手动更新外键表中的对应值。MySQL 8.0 及以上，使用 ROW_NUMBER 子查询来生成新的主键，然后再更新主键。：确保在更新过程中没有并发的写操作，以防数据不一致。：操作前务必备份数据，以防出现意外情况。或者使用sql 语句。
mysql 主键 自增_MySQL自增主键详解
热门推荐
weixin_27960253的博客
01-18 1万+
一、自增值保存在哪儿？不同的引擎对于自增值的保存策略不同1.MyISAM引擎的自增值保存在数据文件中2.InnoDB引擎的自增值，在MySQL5.7及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+步长作为这个表当前的自增值select max(ai_col) from table_name for upda...
MySQL 自增主键重置详解：保持 ID 连续性
码农研究僧的博客
05-29 548
MySQL自增主键(AUTO_INCREMENT)的特性及使用注意事项。主要内容包括：1) 自增主键在删除数据后不会重置，仍会从当前最大值继续增长；2) 通过ALTER TABLE命令可手动设置自增起始值，但只能设置比当前最大ID更大的值；3) 对比DELETE和TRUNCATE在清空表时的不同表现，前者不重置自增ID，后者会重置；4) 给出不同场景下的使用建议，包括部分删除、全表清空、数据迁移等情况是否推荐手动设置自增值
浅谈MySQL中的自增主键用完了怎么办
12-14
面试官:”用过mysql吧，你们是用自增主键还是UUID？” 你:”用的是自增主键” 面试官:”为什么是自增主键？” 你:”因为采用自增主键，数据在物理结构上是顺序存储，性能最好，blabla…” 面试官:”那自增主键...
MySQL自增主键删除后重复问题
12-15
　设置一张MySQL表，表里有一个自增主键ID，往表里插入数据，假如插入数据之后表后一行的ID是100，我先删除这条ID为100的记录，然后重新启动服务器，按理说如果再往这个表里插入新的记录，新纪录的ID将为101，对吧...
MySQLGraphQLAPI
2509_93945719的博客
11-22 322
另外，权限控制也得注意，GraphQL默认不限制查询，如果公开暴露，可能被恶意请求拖垮服务器。首先，你得安装必要的包，比如、，还有MySQL的连接器，比如。还有，如果数据库表结构复杂，有联表查询，GraphQL的schema也得相应设计成嵌套类型，resolver里用JOIN语句处理。这里，类型对应MySQL表的字段，定义了可用的查询操作，比如获取所有用户或根据ID查单个用户。注意，GraphQL的字段名可以用camelCase风格，和MySQL的snake_case不一样，这点在映射时要处理好。
MySQL视频
最新发布
2509_93946396的博客
11-22 252
现在回头看，当初连brew install mysql都能报错一整天的小白，现在也能熟练做分库分表方案了。视频学习最大的价值就是降低入门门槛，但想真正消化知识，还是得在本地环境多折腾。后来偶然刷到某位老师的安装教程，才发现原来从MySQL 8.0开始默认认证方式变了，视频里连修改my.ini配置文件的坑位都标得明明白白。别看时长吓人，但讲师把每个知识点都拆成10分钟左右的片段，通勤路上都能看几集。去年我跟着视频做订单管理系统项目时，曾遇到个典型问题：视频里讲师创建的复合索引查询速度很快，我的却完全没生效。
MySQL 函数
2509_94006710的博客
11-21 1040
MySQL 函数是服务器端定义的程序，可以接收输入参数，进行处理后返回一个结果值。函数的主要特点是可以在 SQL 语句中直接调用，通常用于计算、数据转换或返回某种特定的结果。与存储过程不同，函数必须返回一个值，并且可以在 SQL 语句的SELECT列表、WHERE子句、ORDER BY子句等位置使用。
MySQL案例分析
2509_93941777的博客
11-22 315
事务A先锁住了product_id=100的记录准备更新状态，同时事务B锁住了product_id=101的记录要减库存，但接着事务A试图去获取product_id=101的锁，事务B又反过来抢product_id=100的锁——典型的循环等待。这个案例给我的教训是：MySQL的锁机制远比想象中复杂，特别是在非主键索引上操作时，间隙锁容易成为性能杀手。另外还把事务隔离级别降到了READ-COMMITTED，虽然可能引发幻读，但对我们这个业务场景影响不大，毕竟促销活动都是短时爆发，数据一致性要求没那么严苛。
DBeaver连接本地MySQL、创建数据库表的基础
u013157943的博客
11-19 392
DBeaver连接本地MySQL、创建数据库表的基础
MySQL自然语言处理应用
2509_93942886的博客
11-22 319
总的来说，虽然MySQL不是专业的自然语言处理工具，但它提供的文本处理功能已经能够解决很多实际业务中的文本分析需求。对于大多数中小型项目来说，充分利用数据库层的文本处理能力，往往能在保证功能实现的同时获得更好的性能表现。最近在优化某个电商平台的订单系统时，我就遇到了这样的需求：要从用户填写的非结构化地址信息中自动提取区县级地名。在实践中我发现几个值得注意的地方：首先，MySQL的文本处理功能虽然方便，但不适合处理海量文本数据，那种情况还是应该用专门的文本搜索引擎。字符串处理：基础中的基础。
mysql创建自增主键
08-22
在 MySQL 中创建带有自增主键的表是一种常见的做法，适用于需要唯一标识每一条记录的场景。自增主键通过 `AUTO_INCREMENT` 关键字实现，通常与 `INT` 类型的列一起使用，并将其设为主键（`PRIMARY KEY`）。 以下是一个创建带有自增主键表的示例： ```sql CREATE TABLE users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(15) NOT NULL ); ``` 在上述语句中： - `id` 列被定义为 `INT UNSIGNED` 类型，表示无符号整数，以支持更大的数值范围。 - `NOT NULL` 约束确保该列不允许存储 `NULL` 值。 - `AUTO_INCREMENT` 关键字表示该列的值会自动递增。 - `PRIMARY KEY` 指定 `id` 列为主键，用于唯一标识表中的每一行记录[^2]。 如果希望自增的起始值不是默认的 1，可以显式指定起始值。例如，若希望 `id` 从 100 开始递增，可以使用如下语句： ```sql CREATE TABLE uu ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(15) NOT NULL ) AUTO_INCREMENT = 100; ``` 在此情况下，`AUTO_INCREMENT = 100` 表示自增序列的起始值为 100[^1]。 此外，还可以在定义列时直接指定 `PRIMARY KEY`，也可以在表定义的最后单独指定主键。例如，以下语句定义了一个 `products` 表，并将 `product_id` 列设为主键： ```sql CREATE TABLE products ( product_id INT AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(50), price DECIMAL(10, 2) ); ``` 此语句中同样使用了 `AUTO_INCREMENT` 来定义 `product_id` 列为自增主键[^3]。 对于 `PRIMARY KEY` 的定义，MySQL 提供了两种方式：可以在列定义时直接指定，也可以在表定义的最后单独指定主键。例如，以下语句展示了如何在表定义的最后单独指定主键： ```sql CREATE TABLE ss ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_name VARCHAR(15) NOT NULL, PRIMARY KEY (id) ); ``` 在此情况下，`PRIMARY KEY (id)` 明确指定了 `id` 列为主键[^4]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

程序员麻辣烫

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值