1. 为什么要在数据库里用SQL生成雪花ID前阵子做数据迁移新表主键用了雪花ID可老库里的数据要批量导入改应用层代码就意味着又要走一轮发版流程。我就在想能不能直接在MySQL里用SQL把雪花ID生成出来试下来是可行的但过程中的坑也不少尤其是序列号处理、并发控制和类型溢出这三件事稍不注意就会造出重复ID或者负数ID。先说结论如果你只是做一次性数据修复、定时任务批量刷数或者存储过程里需要生成分布式ID用纯SQL生成雪花ID完全可行。但如果你面对的是高并发线上写入我还是建议把生成逻辑放在应用层或者独立的ID服务里。SQL方案的价值在于“不依赖应用层代码、不引入中间件、在数据库内部就能自洽完成”非常适合数据迁移、清洗、补数这类场景。1.1 雪花ID并不是“一串随机数字”雪花IDSnowflake ID本质上是一个64位的长整型数字由几段信息按位拼接而成。很多人以为这东西就是UUID变种其实完全不是。UUID是128位随机数而雪花ID的每一段都有明确含义最关键的是它能做到“大致按时间递增”这对数据库主键和索引非常友好。标准布局是1位符号位 41位毫秒时间戳 10位机器ID 12位同一毫秒内序列号。符号位固定为0保证ID是正整数时间戳部分是从某个自定义纪元开始的毫秒数机器ID用来区分不同实例序列号解决同一毫秒内的并发重复。用生活化的说法雪花ID就像把快递单号拆成了三段编码。时间戳告诉你“这单是什么时候生成的”机器ID告诉你“是哪个站点收件的”序列号告诉你“同一秒内是第几单”。三段拼到一起就是一个全链路唯一的快递单号。在MySQL里的拼接方式通常是这样((timestamp_ms - epoch_ms) 22) | (worker_id 12) | sequence其中是左移|是按位或。左移的作用是给后面的段腾出位置按位或的作用是把各段信息“粘”成一个整体。理解了这个公式后面所有SQL写起来就顺了。1.2 哪些场景适合用SQL直接生成不是所有场景都适合在SQL里生成雪花ID我踩过坑之后总结了几类判断标准第一类是批量数据迁移和导入。旧表切新表、历史数据回填、多实例数据汇聚这类操作通常是一次性的去改应用代码性价比太低直接在SQL里生成ID最省事。第二类是存储过程或者定时任务。比如每天凌晨跑统计任务、定时从外部同步数据这类任务本身就在数据库内部中途再调应用接口去拿ID链路又长又容易出错。第三类是数据清洗和补漏。比如某张表原本用自增主键现在要改成雪花ID需要把存量数据全部更新一遍这种情况下SQL脚本是最顺手的工具。反过来说如果是业务高峰期每秒几万次写入整个系统都是微服务架构我就建议老老实实在代码里写个雪花ID工具类或者用专门的ID生成服务。SQL方案在单机执行时性能可控但一旦落到多台数据库节点、多个业务线程高并发调用的场景锁和事务的开销会迅速放大。2. 动手前先确认MySQL版本和ID设计参数在写生成SQL之前有两件事必须提前确认一是数据库版本二是自定义纪元。版本决定你能不能用毫秒级时间戳和窗口函数自定义纪元决定你的ID会不会过早溢出变成负数。2.1 版本与函数支持检查雪花ID需要毫秒级时间戳MySQL 5.7及以上版本支持NOW(3)也就是带3位毫秒的时间配合UNIX_TIMESTAMP()就能转换成毫秒数。MySQL 8.0还支持窗口函数在批量生成时可以用ROW_NUMBER()拿序列号方便很多。先跑一句确认版本SELECT VERSION();如果是5.7以下的老版本NOW(3)可能不生效那就只能用UNIX_TIMESTAMP()拿秒级时间戳ID的时间粒度会粗糙一些同一个worker在一秒内就需要靠序列号硬扛。这种情况下我会把序列号位数用满并尽量把worker_id分配得分散一些降低撞ID概率。还有一个容易踩的坑是UNIX_TIMESTAMP(NOW(3)) * 1000返回的是浮点数直接做位运算可能出现精度问题所以要加一层FLOOR()。实际我习惯这样取毫秒时间差SELECT FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000) - 1577836800000 AS delta_ms;1577836800000是2020-01-01 00:00:00的毫秒时间戳。为什么要减它因为标准雪花ID只给时间戳留了41位最多表示约69年的毫秒数。如果直接从1970-01-01算起到2020年以后这个数已经很大了虽然没超限但没必要。选一个近一些的起始点能让ID在更长周期内保持安全。2.2 自定义纪元怎么选自定义纪元就是“从哪一天开始算时间”。建议选一个固定的、容易记的时间比如2020年1月1日。这个选择有两个好处一是时间戳差值稳定二是41位空间可以用到接近2089年对绝大多数业务来说都足够。这个纪元的值可以一次性查出来SELECT UNIX_TIMESTAMP(2020-01-01 00:00:00) * 1000;结果就是1577836800000写SQL时建议直接写成常量并且加注释方便后来人看懂这个数字是从哪来的。另外如果你需要同时支持多个业务线也可以定义不同的epoch这样不同业务生成的ID即使落在同一台机器上也不会发生时间戳段重复。2.3 worker_id怎么分配机器ID在标准雪花算法里占10位也就是0到1023。SQL方案里worker_id可以是一个手工维护的数字也可以抽成配置表。我建议在配置表里维护因为后续如果要扩容机器至少要保证新worker_id不重复。如果你只有一台数据库那用1作为worker_id就够了。如果有多台实例同时生成ID务必在每台实例上用不同的worker_id否则同一毫秒内容易出现ID重复。这一点是所有方案中最容易忽略的。3. 快速上手用SQL计算出雪花ID确认完参数之后就可以写第一版SQL了。先从最简单的表达式开始再逐步加上序列号最后落到INSERT语句里。3.1 最简版脚本一条SELECT算出ID下面这段SQL是纯表达式版本适合在测试环境验证位运算逻辑SELECT (FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000) - 1577836800000) 22 | (1 12) | 0 AS snowflake_id;这条SQL里(1 12)代表worker_id1左移12位序列号直接用0。意思就是“当前毫秒时间戳机器1序列0”拼出来的ID。跑一下你会得到一个20位左右的数字看起来就是标准的雪花ID。这个版本最大的问题是序列号永远为0。如果你在同一毫秒内连续执行几次会得到一模一样的ID。所以它只能用来验证公式不能直接用在正式数据上。3.2 加序列号的批量生成方式真正要批量造数据就需要让序列号在同一毫秒内递增。MySQL里最简单的办法是用窗口函数比如从任意一张表取前100行生成100个IDSELECT ((FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000) - 1577836800000) 22) | (1 12) | (ROW_NUMBER() OVER () - 1) AS snowflake_id FROM information_schema.tables LIMIT 100;这里ROW_NUMBER() OVER ()会在结果集内生成从1开始的序号减1之后正好是0到99对应序列号段。同一毫秒内生成的这100个ID因为序列号不同所以不会重复。这个方案够简单但有两处要注意一是NOW(3)在一条SQL语句中会被当成常量整条语句执行期间时间戳不变所以靠序列号区分二是如果一次要生成超过4096个ID序列号就不够用了必须把时间推后到下一毫秒再继续。后面我会讲更稳妥的存储函数方案这里先理解原理。3.3 在INSERT和UPDATE里怎么用在SQL中生成ID最终要落到表里。我最常用的是这种INSERT SELECT写法INSERT INTO new_table (id, user_name, created_at) SELECT ((FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000) - 1577836800000) 22) | (1 12) | (ROW_NUMBER() OVER () - 1), user_name, created_at FROM old_table WHERE created_at 2024-01-01;这种写法适合一次性把旧表数据迁移到新表。需要注意的是SELECT出来的顺序要和INSERT的表字段一一对应最好给每个字段写清楚别名SQL语句格式清晰排查问题也方便。比如有人喜欢把整条SQL压缩成一行一旦报错定位字段位置特别痛苦我习惯像上面这样每个字段一行、运算符对齐。还有一个经验是如果目标表原本有自增主键并且历史数据里已经有ID要先把目标表主键设置成不含AUTO_INCREMENT的普通主键再执行INSERT。否则MySQL可能会因为自增冲突而报错。4. 生产级改造用存储函数稳定生成前面几版SQL适合测试和一次性刷数但如果你要在存储过程、定时任务里反复调用或者需要更严格的并发保障就得把ID生成逻辑封装成存储函数。4.1 为什么需要序列表前面提到的窗口函数方案只能保证单条SQL内部不重复不能保证多次调用之间不重复。因为MySQL的用户变量在跨SQL语句时不持久同一毫秒内第二次调用很可能又把序列号归零这样就撞ID了。解决思路是搞一张序列表把每个worker_id对应的序列号和时间戳持久化下来。每次生成ID时先看当前时间戳和表里记录的上一次时间戳是否相同如果相同就在原序列号基础上加1如果不同说明已经进入新毫秒序列号重新从0开始。有人可能会想能不能直接用MySQL用户变量保存状态试过之后会发现跨会话不共享而且在并发场景下变量赋值顺序完全不可控。所以一张带主键的序列表是最简单可靠的状态存储方式。建表语句CREATE TABLE IF NOT EXISTS sys_snowflake_seq ( worker_id SMALLINT UNSIGNED NOT NULL, seq INT UNSIGNED NOT NULL DEFAULT 0, last_ts BIGINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (worker_id) ) ENGINEInnoDB; INSERT INTO sys_snowflake_seq (worker_id) VALUES (1);这张表一次只操作一行InnoDB的行锁足以保证安全。4.2 存储函数完整实现存储函数的核心是“一次性UPDATE”利用LAST_INSERT_ID(expr)在更新行时把最新的序列值带出来。这个技巧比先SELECT再UPDATE少一次查询而且更安全因为UPDATE是原子的行锁会保证并发线程不会同时读到同一个旧值。完整函数如下DELIMITER $$ CREATE FUNCTION snowflake_next(p_worker_id SMALLINT UNSIGNED) RETURNS BIGINT UNSIGNED MODIFIES SQL DATA BEGIN DECLARE v_ts BIGINT UNSIGNED; DECLARE v_seq INT UNSIGNED; REPEAT SET v_ts FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000) - 1577836800000; UPDATE sys_snowflake_seq SET seq LAST_INSERT_ID( IF(last_ts v_ts, seq 1, 0) ), last_ts v_ts WHERE worker_id p_worker_id; SET v_seq LAST_INSERT_ID(); IF v_seq 4095 THEN DO SLEEP(0.001); END IF; UNTIL v_seq 4095 END REPEAT; RETURN ((v_ts 22) | (p_worker_id 12) | v_seq); END$$ DELIMITER ;这里的IF(last_ts v_ts, seq 1, 0)就是核心判断。如果当前时间戳和上次记录相同序列号加1如果已经跨毫秒了序列号归零。LAST_INSERT_ID(expr)会把expr的值同时写入last_insert_id()所以我们紧接着用SET v_seq LAST_INSERT_ID()就能拿到最新的序列号。为什么加DO SLEEP(0.001)因为12位序列号最多表示0到4095同一毫秒内超过4096个请求序列号就溢出了。此时睡1毫秒再进入下一轮循环让时间戳发生变化序列号归零之后就能继续生成ID。这个设计牺牲了点性能但换来的是强一致性。创建函数时如果MySQL报log_bin_trust_function_creators相关的错误需要先执行SET GLOBAL log_bin_trust_function_creators 1;这是开启二进制日志后对函数创建者的限制不影响业务但要注意这个设置在生产环境需要DBA评估。4.3 调用方式与批量数据写入函数创建好之后单条插入可以直接写INSERT INTO orders (id, order_no, amount) VALUES (snowflake_next(1), SO12345, 99.50);批量迁移可以这样写INSERT INTO new_orders (id, order_no, amount, create_time) SELECT snowflake_next(1), order_no, amount, create_time FROM old_orders WHERE status 1;这里每一个row都会调用一次snowflake_next函数内部会做一次UPDATE。对于几万行的迁移没问题但如果一次性刷几百万行性能会比较吃紧。我的习惯是分批跑每批几万行中间加个小停顿避免锁和日志文件暴涨。序列表加函数方案还有一个好处你能在函数返回的ID里反推出生成时间。比如拿到一个ID想确认它是几点生成的用SQL就能解析SELECT (id 22) 1577836800000 AS ts_ms FROM new_orders;这个时间戳就是ID生成时的毫秒时间配合FROM_UNIXTIME()还能转成可读格式。在做数据对账、按时间排序、定位问题数据时非常有用。5. 常见问题与避坑实录纯SQL生成雪花ID的坑大多集中在类型、并发和时钟三个方向上。我把实际踩过的问题整理成了一份速查表方便对照排查。5.1 生成的ID是负数或者数字偏小这是我第一次测试时最先遇到的问题。原因一般是位运算结果被当成了有符号BIGINT一旦时间戳左移后最高位变成1就会显示成负数。排查方法很简单用CAST(... AS UNSIGNED)包一层再看SELECT CAST( ((FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000) - 1577836800000) 22) | (1 12) | 0 AS UNSIGNED) AS snowflake_id;如果还是负数那就是自定义纪元选得太早导致时间戳差值太大占满了41位甚至溢出。把基准时间往前调整比如从我上面说的2020-01-01改成2020-01-01之后某个时间或者统一用一个更晚的epoch。5.2 并发插入时出现重复ID并发重复的根源通常是没有维护好序列号。你如果只是在SELECT里用NOW(3)加固定序列号两个连接在同一个毫秒内就会生成相同ID。我见过有人把snowflake_next()函数里的序列表去掉直接用seq : seq 1变量结果压测的时候大量主键冲突。验证重复可以用这条SQL排查SELECT id, COUNT(*) FROM orders GROUP BY id HAVING COUNT(*) 1;一跑就能看到问题。想要彻底避免序列号必须持久化并且更新时要用原子操作。上面给出的函数方案里UPDATE语句的LAST_INSERT_ID(expr)就是原子操作两个连接同时调用也会串行执行。5.3 时钟回拨问题雪花ID依赖系统时间如果服务器时间被NTP校准或者人为调回去新生成的ID时间戳就会比之前小有可能出现重复或乱序。在应用层实现里通常会记录上一次生成ID的时间一旦发现当前时间小于等于上次时间就直接拿“上次时间1”作为时间戳保证单调递增。纯SQL方案也能做但不够完美。我建议在函数里加一层防护SELECT last_ts INTO v_ts FROM sys_snowflake_seq WHERE worker_id p_worker_id;然后把v_ts GREATEST(v_ts, 当前时间戳)作为最终时间戳。但严格的并发防护还是需要引入锁SQL层的成本会更高。我的实际建议是数据库服务器本身要保持NTP同步并且把回拨风险纳入监控回拨超过阈值就报警。生成ID的SQL到底了只做兜底不能把所有希望寄托在数据库时间上。5.4 雪花ID、UUID、自增主键怎么选很多人在选主键方案时纠结我把三者的特点放在一起对比过下面这张表是我个人的使用结论方案生成方式是否排序是否跨库典型使用场景自增主键数据库生成是否单体系统、内部表UUID应用或SQL生成否是不需要排序、需要全局唯一雪花ID应用或SQL生成基本排序是分布式主键、数据迁移、报表排序雪花ID最吃亏的地方是需要自己管理worker_id和时间基准UUID的两段随机拼接起来就能用。但雪花ID对索引更友好数据写入后物理顺序和时间顺序接近查询排序、范围扫描都更自然这一点在数据量大了以后尤其明显。我个人的体会是如果你正在设计新系统分布式场景直接上应用层雪花ID工具类如果你是面对已经上线的老库需要做迁移和清洗SQL生成雪花ID是非常顺手的补充方案。平时我写SQL时也会刻意保持“先算时间戳、再拼位运算、最后验结果”的习惯这套流程无论换成函数还是脚本都不会跑偏。最后再分享一个小技巧序列表里可以多插几条worker_id记录比如同时插入1到16这样以后想并发生成数据或者模拟多实例写入时直接调用snowflake_next(不同worker_id)就行不用临时改表。SQL能解决的就别让应用层折腾但能提前预留的容量也别省。