如何通过单条SQL为相同邮箱地址的用户生成唯一user编号

完全可以用纯SQL实现，不需要写PHP之类的后端脚本处理。
首先明确核心逻辑：需求只要求不同邮箱对应不同的user编号，不需要从1开始连续递增，所以不需要复杂的循环逻辑，用SQL的集合操作就能搞定。 #

最稳妥的通用方案（兼容所有MySQL版本，无数据冲突风险） #

利用表中自增主键id全局唯一的特性：同一个邮箱的所有订单，取该邮箱下最早的订单id作为user值即可。不同邮箱对应的最早订单id肯定不会重复，完全满足要求，连窗口函数都不需要，兼容性拉满。
操作可以放在一个事务里执行，保证原子性，全程不会出现中间态：

1. 先新增字段，给个临时默认值避免加NOT NULL字段时报错：

ALTER TABLE `orders` ADD COLUMN `user` int(10) unsigned NOT NULL DEFAULT 0;

2. 单条UPDATE语句完成全量数据赋值，不需要遍历循环：

UPDATE `orders` o
JOIN (
    SELECT email, MIN(id) AS uid
    FROM `orders`
    GROUP BY email
) map ON o.email = map.email
SET o.`user` = map.uid;

3. 如果需要严格匹配你给出的字段定义，把临时默认值删掉即可：

ALTER TABLE `orders` ALTER COLUMN `user` DROP DEFAULT;

行业内说的"纯SQL方案"一般指不需要外部程序写逻辑，上述操作完全在数据库侧完成，符合要求。如果你的团队要求必须是语法层面严格的单条语句，也可以实现，看下面的方案。

严格单条SQL方案（MySQL 8.0+支持） #

如果必须要求只执行一条SQL语句，可以通过重建表的方式实现，一条语句完成加字段、填数据的全流程：

RENAME TABLE `orders` TO `orders_tmp`,
CREATE TABLE `orders` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `number` varchar(20) NOT NULL,
    `ordered` datetime DEFAULT NULL,
    `email` varchar(255) NOT NULL,
    `user` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
AS SELECT 
    o.id,
    o.number,
    o.ordered,
    o.email,
    DENSE_RANK() OVER(ORDER BY o.email) AS `user`
FROM `orders_tmp` o;

注意用这个方案的话，需要手动补全原表的其他索引、字段约束、字符集配置，和原表结构保持一致，执行完校验数据没问题后删掉临时表orders_tmp就行，适合表结构不复杂的场景。 #

避坑提醒 #

• 别用CRC32(email)、哈希值转整数这类取巧方案，存在哈希碰撞概率，会导致不同邮箱被分到同一个user编号，造成数据错误。
• 大表执行操作建议选业务低峰期，避免锁表影响正常业务。
• 执行任何数据变更前建议先备份表，避免误操作无法回滚。

内容的提问来源于stack exchange，提问作者Werner