做后端这些年雪花ID谁没写过呢Java里new一个SnowflakeIdGenerator几行代码就完事。可我在好几个数据迁移、存量表回填的项目里被一个特别朴素的问题卡住了手边只有一台MySQL客户端也只有一个行云流水的Navicat不让部署代码不让我起Java服务但要给百万行老表生成全局唯一的long型主键ID。这种时候如果还在等应用层方案项目周期早就凉了。其实雪花算法的本质就是把“时间戳、机器编号、序列号”三样东西按位拼成一个64位整数那么没有编程语言加持的时候直接用SQL语句一样能算出来。这篇文章就把我用纯SQL生成雪花ID的完整思路、可落地的脚本和踩过的坑整理出来适合DBA、后端同学、搞数据迁移和测试造数的朋友直接参考。1. 雪花算法与MySQL场景的匹配分析1.1 雪花ID的64位结构先看懂位是怎么排的标准的雪花算法ID是一个64位整数很多人记不清每一段到底占多少位。默认Twitter原版分布是1位符号位 41位毫秒时间戳 10位机器ID 12位序列号。符号位在Java里固定是0保证ID为正数。41位毫秒时间戳可以表示约69.7年如果从一个自定义起始时间开始算完全够用。10位机器ID最多支持1024个节点12位序列号则是同一毫秒内最多生成4096个ID。放到MySQL里这个结构刚好对应一个BIGINT类型。BIGINT是64位有符号整数范围是-9223372036854775808到9223372036854775807只要符号位为0正数范围正好能装下雪花ID。注意这里说的是“刚好装下”不是“勉强装下”。很多第一次写SQL版雪花ID的人会把时间戳、机器ID、序列号用字符串拼起来最后得到一个20位的字符串。这样做虽然也是唯一值但它不再是雪花ID失去了数值排序能力索引效率也会退化和“雪花算法”关系不大了。位数拼接的时候时间戳左移22位是核心操作。因为机器ID占10位序列号占12位加起来低位一共22位所以时间戳必须左移22位给低位腾地方。机器ID左移12位序列号直接在最低位。你可以把这三段理解成三个数字分别放在“亿位”“万位”“个位”上互不干扰拼出来的数字天然保证唯一。1.2 为什么偏要在SQL里生成哪些场景真能受益很多人一看到SQL生成雪花ID就觉得这是脱裤子放屁应用层一行代码的事为什么要折腾数据库但经历过存量数据改造的人会明白SQL版最大的价值是“不用改代码”。第一个典型场景就是存量表回填ID。老系统早期可能是用自增ID或者UUID做主键分库分表之后自增ID会冲突UUID又太长、无序。现在要加一个雪花ID列把旧数据全部刷一遍。这个时候应用服务可能早就下线了或者接口已经冻结你只能在数据库环境里直接操作。用UPDATE语句逐行调用函数一次搞定比写Java批量任务再导出导入干净太多。第二个场景是数据迁移合并。两个库要并到一起两边各自有一套ID体系直接用原ID肯定撞车。在INSERT ... SELECT的过程中直接生成新的雪花ID省掉中间落盘和洗数的环节。第三个场景是测试造数和联调。测试环境经常需要快速插入一批带合理主键的数据又不希望引入复杂的发号依赖。直接在SQL里生成查询、插入、脚本一气呵成。但也不要盲目乐观。高并发在线业务不太适合用纯SQL生成雪花ID。MySQL函数调用有开销多个数据库连接之间的状态又不共享无法保证全局唯一。比较合理的使用方式是在批量脚本、离线修复、低并发场景里用在线发号还是要放在应用层或者单独的发号服务里。SQL版本的价值是“救急能用、方案清晰”不是取代发号器。2. 写SQL前必须搞懂的三个基础点2.1 MySQL位运算左移、或运算如何拼出64位ID如果没用过MySQL位运算先花两分钟认识一下。MySQL支持、、、|、^分别代表左移、右移、按位与、按位或、按位异或。左移N位相当于乘以2的N次方右移N位相当于除以2的N次方后向下取整。比如SELECT 1 22返回4194304SELECT 1024 12返回4194304这就是给不同片段腾位置的过程。雪花ID三段式拼装的SQL核心写法是这样的((时间戳部分) 22) | ((机器编号部分) 12) | 序列号为什么用“或”而不是“加”因为时间戳左移22位后低22位全部是0机器编号左移12位后低12位全部是0。三段在二进制的位段上完全不重叠这时候“或”和“加法”结果一样。但“或”更符合位运算语义看到代码的人能立刻明白这是拼接位段。如果你图省事用加号结果也对只是可读性差一些后续别人维护时容易误会。还有一个容易忽略的细节MySQL里位运算的操作数如果太大结果可能变成DECIMAL类型而不是BIGINT。虽然最终存入BIGINT字段时MySQL会隐式转换但我在函数里测过建议对关键运算结果做一次显式转换避免边界情况下出现类型不匹配的报错。后面给的函数版本里会体现这个习惯。2.2 毫秒时间戳的正确取法NOW(3)和UNIX_TIMESTAMP的配合很多人写时间戳时第一反应是UNIX_TIMESTAMP()但这个函数不带参数时只返回秒级时间戳配合雪花算法就废了。因为一秒钟内有1000个毫秒如果只用秒同一秒内所有ID就全靠序列号区分万一序列号从0重新开始立刻重复。正确做法是取毫秒先用NOW(3)拿到带3位毫秒的时间再通过UNIX_TIMESTAMP(NOW(3))转成带小数的秒级时间戳最后乘1000并向下取整。SET now_ms FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000);这条语句做完now_ms就是一个典型的毫秒时间戳比如1700000000123。注意这里必须用FLOOR取整不能四舍五入。毫秒是离散的刻度向上取整可能会把当前毫秒值变成未来一毫秒的值这在雪花ID里会造成时间跳变。更深一层的坑是NOW()和SYSDATE()的区别。NOW()在一条SQL语句的执行过程中返回的是语句开始执行的时间同一个语句里多次调用结果是一样的。SYSDATE()返回的是调用那一刻的实时时间。在存储函数里写循环等待“下一毫秒到来”的时候如果还用NOW()会陷入死循环因为函数里反复取得的都是函数开始时的那个时间。这种时候必须用SYSDATE(3)。我在下面的函数版本里统一用SYSDATE(3)取当前时间就是为了同时照顾“取值实时”和“等待下一毫秒”这两个需求。2.3 机器编号怎么来没有部署脚本时在SQL里现算应用层的雪花ID会从配置文件或环境变量读机器编号SQL里没这个条件。但机器编号也不能随便写死至少同一套数据库环境里不同来源的数据要能区分开。我的经验是按使用场景分三级策略固定值直接SET worker_id 1;适合单实例、单数据库、明确知道只有自己在用的时候。连接ID取模用CONNECTION_ID() % 1024当机器号。同一个连接内这个值是稳定的能分担一点并发连接之间的重复概率。库名/实例标识hash用ABS(CRC32(DATABASE())) % 1024多库并行的时候每个库算出来的机器号大概率不一样即使以后数据合并不同库之间的ID也不容易撞。这里多说一句CRC32碰撞概率虽然存在但我们用的是模1024后的结果实际碰撞只取决于后10位概率不算特别低。所以如果两个库要合并还是要人工给每个库分配一个不同的worker_id不推荐完全依赖hash。临时用一下可以长期用必须静态分配。3. 三种可落地的SQL写法从一条表达式到存储函数3.1 一条SELECT直接拼ID适合验证与临时补数如果你只是想在查询里临时看一眼雪花ID长什么样或者一次性给某条记录补个ID可以用最简版一条SELECT就能出结果。-- 选一个自定义起始时间这里用2023-01-01 00:00:00对应的毫秒值 SET epoch_ms 1672531200000; SET worker_id 1; SELECT ((FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000) - epoch_ms) 22) | (worker_id 12) | 0 AS snowflake_id;运行后你会得到一个19位左右的数字这就是一个结构正确的雪花ID。注意我把序列号写死成0所以同一毫秒内执行两次会拿到一模一样的ID。这个版本只能验证结构不能拿到生产环境当真正的发号器用。这个版本在两种情况下有价值。一是验证位运算公式对不对将生成的ID右移22位再把低22位屏蔽掉能倒推出当前时间戳可以和系统时间对上确认公式没写错。二是给单条测试数据补ID手工执行一次结果可预期不需要考虑并发。如果担心位运算在不同MySQL版本上有类型问题可以用等价的算术写法替代SELECT ((FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000) - epoch_ms) * 4194304) (worker_id * 4096) 0 AS snowflake_id;4194304是2的22次方4096是2的12次方。这个版本纯算术运算兼容性更好但可读性略差。两者结果完全相同。3.2 存储函数版同一毫秒内序列号自增的真正实现单条SELECT搞不定序列号因为序列号必须记住上一次的值还要判断当前是否还停留在同一个毫秒。这个状态用用户变量可以存四段流程也能写成存储函数。下面给一个我在项目里实际使用的函数版本可以直接抄DELIMITER $$ DROP FUNCTION IF EXISTS sf_snowflake_id$$ CREATE FUNCTION sf_snowflake_id(p_worker_id BIGINT) RETURNS BIGINT NOT DETERMINISTIC READS SQL DATA BEGIN DECLARE v_now_ms BIGINT DEFAULT 0; -- 实时取毫秒时间戳尽量贴近每行执行的真实时间 SET v_now_ms FLOOR(UNIX_TIMESTAMP(SYSDATE(3)) * 1000); -- 回拨保护如果系统时间后退了沿用上一次的毫秒值 IF sf_snow_ts IS NOT NULL AND v_now_ms sf_snow_ts THEN SET v_now_ms sf_snow_ts; END IF; -- 进入新毫秒则重置序列号否则序列号1 IF sf_snow_ts IS NULL OR v_now_ms sf_snow_ts THEN SET sf_snow_ts v_now_ms; SET sf_snow_seq 0; ELSE SET sf_snow_seq sf_snow_seq 1; -- 序列号超过4095等待真正进入下一毫秒 IF sf_snow_seq 4096 THEN SET sf_snow_seq 0; WHILE v_now_ms sf_snow_ts DO SET v_now_ms FLOOR(UNIX_TIMESTAMP(SYSDATE(3)) * 1000); END WHILE; SET sf_snow_ts v_now_ms; END IF; END IF; RETURN ((v_now_ms - 1672531200000) 22) | ((p_worker_id % 1024) 12) | sf_snow_seq; END$$ DELIMITER ;创建函数前检查一下自己的MySQL账号权限。如果binlog开启MySQL默认不允许创建存储函数需要执行SET GLOBAL log_bin_trust_function_creators 1;或者让DBA统一处理。这个变量是一次性的重启后仍以配置文件为准。调用方式很简单SELECT sf_snowflake_id(1); INSERT INTO biz_log(snow_id, content) VALUES (sf_snowflake_id(2), hello);同一个数据库连接内连续调用同一毫秒内序列号会从0、1、2这样递增到4095后自动等到下一毫秒从0重新开始。时间戳、机器编号、序列号三段组合起来保持唯一。这个函数有一个先决条件它只保证单个MySQL连接内唯一。如果多个应用连接同时调用由于各自维护各自的sf_snow_ts和sf_snow_seq同一个毫秒内很可能产生相同的ID。这一点必须时刻记在心里后面第4章会详细说。另外用户变量的命名我做了一点手脚加了个sf_前缀。千万不要用last_ts、seq这种太通吃的名字否则你业务代码里别人也用了同名变量会互相覆盖。脚本跑出来一堆重复ID还不知道怎么回事这个坑我踩过一次。3.3 批量给存量表回填ID的完整实操函数有了批量回填就是常规操作。假设有一张业务表biz_log原来没有雪花ID列现在要加一列并刷上ID。第一步加列ALTER TABLE biz_log ADD COLUMN snow_id BIGINT NULL;第二步设置机器编号。单库直接用固定值多库并行建议用库名hash或者手动分配SET wk (SELECT ABS(CRC32(DATABASE())) % 1024);第三步按主键分批UPDATE。即使是百万行也千万不要一条UPDATE刷全表一方面undo日志膨胀厉害另一方面长事务会锁大量行影响线上读写。按主键范围一段段推进UPDATE biz_log SET snow_id sf_snowflake_id(wk) WHERE snow_id IS NULL AND id BETWEEN 1 AND 50000;每次跑完检查一下影响行数确认没有报错再跑下一段。UPDATE语句执行时函数是逐行调用的每一行拿到的时间戳和序列号都会推进因此ID不会重复。如果中途事务回滚序列号会跳过一些值但只需要唯一性不需要连续完全没问题。第四步全表刷完后加唯一索引ALTER TABLE biz_log ADD UNIQUE KEY uk_snow_id(snow_id);索引加上后如果真出现重复ID会直接报错。这也是给整个方案上了一道保险。4. 常见问题与排查实录照着这个速查表避坑4.1 ID重复了最常踩的并发与序列号陷阱用我上面的函数在同一个连接里连续插入几万条数据理论上不会重复。但实践中还是会遇到重复最常见的原因有三个。第一多个连接并发调用函数。每个连接都有自己的用户变量同一毫秒内都从序列号0开始算。如果机器编号又恰好相同生成的ID必然一样。解决办法很简单要么所有写入都走同一个连接要么给不同连接分配不同的worker_id。应用层如果用连接池很难保证每一次INSERT都落在同一条连接上所以这个方案在线写入场景要慎用。第二每次调用函数前手动重置了用户变量。很多人看到函数里用了sf_snow_ts就顺手在调用前执行了SET sf_snow_ts NULL;本意是想清理会话状态。结果同一个毫秒内连续两次调用函数发现变量为NULL都从序列号0开始ID立刻重复。记住状态要交给函数内部维护不要在外面动它。第三使用自定义表达式版本但忘了序列号。最简版里序列号写死为0连续执行必然重复。这个版本只能用来验证结构不能插入生产表。排查重复ID的标准SQL是SELECT snow_id, COUNT(*) AS cnt FROM biz_log GROUP BY snow_id HAVING cnt 1;查出来后可以拆解ID的位段确认到底是哪一位出问题SELECT snow_id 22 AS ts_part, (snow_id 12) 1023 AS worker_part, snow_id 4095 AS seq_part FROM biz_log WHERE snow_id IN (重复的ID);如果时间戳部分相同、机器编号相同、序列号也相同说明是同一毫秒内同一个worker_id重复生成。如果时间戳部分相同但机器号不同说明并发连接之间worker_id分配出了问题。4.2 时间回拨和时钟抖动函数里怎么补系统时钟不是永远闷头往前走的NTP同步、人工校时、虚拟机时钟漂移都可能让时间倒退。雪花算法对时间回拨非常敏感因为ID的时间戳部分一旦变小新生成的ID可能小于之前已经生成的ID导致索引倒挂、排序混乱甚至唯一键冲突。应用层做回拨保护通常分两种策略如果回拨时间很短几十毫秒内就继续沿用上一次记录的时间戳如果回拨时间很长直接拒绝发号等时钟追上来。SQL函数里没法无限精修但可以做到最基本的“沿用旧时间”。我在前面的函数版本里已经写了这行IF sf_snow_ts IS NOT NULL AND v_now_ms sf_snow_ts THEN SET v_now_ms sf_snow_ts; END IF;它保证一旦发现系统时间小于上次使用的时间戳就假装时间没动继续用旧值。代价是未来一段时间内ID里的时间戳会保持不变这在语义上仍然满足“ID递增”因为序列号还在推进。如果回拨到非常久以前比如倒退几个小时这个简单策略就不行了因为旧时间戳会沿用很久序列号很快撑爆4096。那种场景必须有外部存储持久化记录最后时间比如放到Redis或者一张专门的表里。对于批量数据修复这种短周期任务上面的简单保护已经够用。如果是长期运行的业务系统我不建议用纯SQL方案在线发号风险还是有点大。4.3 负数、溢出、Java解析问题MySQL的BIGINT是有符号类型最大正数是9223372036854775807。雪花ID一旦超过这个值存进BIGINT就会变负数。这个情况在选自定义起始毫秒值的时候就要算清楚。41位时间戳最多表示约69.7年比如从2023年开始大约到2092年前后才会触顶。在那之前时间戳左移22位不会越过符号位所以正常设计下不会负数。但有一种情况真会遇到测试环境里随手写了一个很大的epoch毫秒值比如把基准时间设成了1970年时间戳直接占满41位还要多生成的ID立刻变成负数。解决办法是检查基准时间设置确保当前毫秒数 - 基准毫秒数 2的41次方。如果下游是Java系统还有一个容易被忽略的细节Java的Long也是64位有符号整数和MySQL的BIGINT行为一致。如果ID是负数Java侧解析也会是负数。所以做压测时要随机抽几个ID拿到Java里转一下确认都是正数否则后面联调会出一堆莫名其妙的bug。4.4 纯SQL方案的性能边界与适用判断我给一个实际参考单连接下这个存储函数每秒大约能执行几万次。听起来不低但和本地生成器每秒几十万的吞吐量还是有差距。更重要的不是吞吐量而是扩展性。每个MySQL连接维护自己的一份序列号状态意味着并发越大重复风险越高无法通过加机器线性扩展。适合用纯SQL方案的场景我总结成了这张速查表使用场景是否推荐原因存量表回填ID推荐单连接顺序执行函数状态连续唯一性可靠数据迁移时INSERT...SELECT推荐入库时即时生成无需应用层干预测试环境造数推荐灵活方便不引入额外组件高并发在线发号不推荐多连接状态隔离无法保证全局唯一跨库数据合并谨慎worker_id需要人工分配避免hash碰撞如果业务真的需要高并发发号我建议还是把雪花算法实现在Java或者Go里用本地缓存、Redis发号器等方案。SQL版本可以作为备份手段在故障恢复、手工修复的时候顶上去。从我个人的使用习惯来说这套纯SQL方案最舒服的点是“所见即所得”。以前修复数据要写Java任务、打包、部署、跑日志现在直接在SQL客户端里几条命令一个UPDATE就完事。而且因为整个逻辑都在数据库里排查问题的时候可以直接用SQL拆位不需要再把ID导出去用程序解析。最后分享一个小技巧每次跑批量回填之前先执行一次最简单的SELECT语句把生成结果的三个位段拆出来人工看一眼确认时间戳部分是递增的、worker_id是你预期的那一位、序列号还在0附近。这个验证习惯花不了10秒钟但能帮你提前拦住80%的低级错误。