企业官网建设流程全解析

1. 为什么要在数据库里用SQL生成雪花ID

前阵子做数据迁移，新表主键用了雪花ID，可老库里的数据要批量导入，改应用层代码就意味着又要走一轮发版流程。我就在想，能不能直接在MySQL里用SQL把雪花ID生成出来？试下来是可行的，但过程中的坑也不少，尤其是序列号处理、并发控制和类型溢出这三件事，稍不注意就会造出重复ID或者负数ID。

先说结论：如果你只是做一次性数据修复、定时任务批量刷数，或者存储过程里需要生成分布式ID，用纯SQL生成雪花ID完全可行。但如果你面对的是高并发线上写入，我还是建议把生成逻辑放在应用层或者独立的ID服务里。SQL方案的价值在于“不依赖应用层代码、不引入中间件、在数据库内部就能自洽完成”，非常适合数据迁移、清洗、补数这类场景。

1.1 雪花ID并不是“一串随机数字”

雪花ID（Snowflake 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_id=1左移12位，序列号直接用0。意思就是“当前毫秒时间戳+机器1+序列0”拼出来的ID。跑一下你会得到一个20位左右的数字，看起来就是标准的雪花ID。

这个版本最大的问题是序列号永远为0。如果你在同一毫秒内连续执行几次，会得到一模一样的ID。所以它只能用来验证公式，不能直接用在正式数据上。

3.2 加序列号的批量生成方式

真正要批量造数据，就需要让序列号在同一毫秒内递增。MySQL里最简单的办法是用窗口函数，比如从任意一张表取前100行生成100个ID：

SELECT ((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) ) ENGINE=InnoDB; 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、自增主键怎么选

很多人在选主键方案时纠结，我把三者的特点放在一起对比过，下面这张表是我个人的使用结论：

雪花ID最吃亏的地方是需要自己管理worker_id和时间基准，UUID的两段随机拼接起来就能用。但雪花ID对索引更友好，数据写入后物理顺序和时间顺序接近，查询排序、范围扫描都更自然，这一点在数据量大了以后尤其明显。

我个人的体会是：如果你正在设计新系统，分布式场景直接上应用层雪花ID工具类；如果你是面对已经上线的老库，需要做迁移和清洗，SQL生成雪花ID是非常顺手的补充方案。平时我写SQL时也会刻意保持“先算时间戳、再拼位运算、最后验结果”的习惯，这套流程无论换成函数还是脚本都不会跑偏。

最后再分享一个小技巧：序列表里可以多插几条worker_id记录，比如同时插入1到16，这样以后想并发生成数据或者模拟多实例写入时，直接调用snowflake_next(不同worker_id)就行，不用临时改表。SQL能解决的就别让应用层折腾，但能提前预留的容量，也别省。