MySQL如何为重复拼接生成的邮箱字段追加连续计数序号？

需求说明 #

需要为员工生成符合规则的邮箱：

• 格式：小写名字首字母 + 小写姓氏 + @company.net
• 重复处理：当多个员工的基础邮箱（首字母+姓氏）重复时，为后续重复项追加连续序号（如第一个为jmiller@company.net，第二个为jmiller1@company.net，第三个为jmiller2@company.net）

解决方案 #

利用窗口函数ROW_NUMBER()对重复的基础邮箱分组编号，再根据编号生成最终邮箱。以下是两种实现方式：

方式1：使用CTE（适用于PostgreSQL、MySQL 8.0+等支持CTE的数据库）

WITH employee_emails AS (
    SELECT 
        emp_no,
        CONCAT(LOWER(SUBSTRING(first_name, 1, 1)), LOWER(last_name)) AS base_email,
        -- 按基础邮箱分组，按员工编号排序生成序号
        ROW_NUMBER() OVER (PARTITION BY base_email ORDER BY emp_no) AS rn
    FROM employees_test
    WHERE email IS NULL
)
UPDATE employees_test et
SET email = CASE 
    WHEN ee.rn = 1 THEN CONCAT(ee.base_email, '@company.net')
    ELSE CONCAT(ee.base_email, ee.rn - 1, '@company.net')
END
FROM employee_emails ee
WHERE et.emp_no = ee.emp_no;

方式2：使用JOIN子查询（兼容更多数据库版本）

UPDATE employees_test et
JOIN (
    SELECT 
        emp_no,
        CONCAT(LOWER(SUBSTRING(first_name, 1, 1)), LOWER(last_name)) AS base_email,
        ROW_NUMBER() OVER (PARTITION BY base_email ORDER BY emp_no) AS rn
    FROM employees_test
    WHERE email IS NULL
) ee ON et.emp_no = ee.emp_no
SET et.email = CASE 
    WHEN ee.rn = 1 THEN CONCAT(ee.base_email, '@company.net')
    ELSE CONCAT(ee.base_email, ee.rn - 1, '@company.net')
END
WHERE et.email IS NULL;

逻辑说明 #

1. 基础邮箱计算：通过CONCAT(LOWER(SUBSTRING(first_name, 1, 1)), LOWER(last_name))生成统一格式的基础邮箱前缀
2. 分组编号：ROW_NUMBER() OVER (PARTITION BY base_email ORDER BY emp_no)会将相同基础邮箱的员工分组，按emp_no排序后分配1、2、3...的序号
3. 邮箱生成：序号为1的员工直接使用基础邮箱+域名；序号大于1的员工，在基础邮箱后追加序号-1（确保第二个员工加1，第三个加2，以此类推）

你可以根据实际需求调整ORDER BY后的字段（比如换成hire_date或birth_date），保证序号的排序逻辑符合业务要求。

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