无用户ID时多用户名关联的MySQL表结构优化咨询

现有方案通过tblUsers的alias字段关联同名用户，但每次用户改名并合并分数时，需要手动批量更新所有关联记录的alias值，操作繁琐且易出错。以下是两种更高效的优化方案：

方案一：独立用户层级关联表（推荐） #

表结构设计 #

拆分用户关联逻辑，用main_user_id字段建立主用户与别名用户的层级关系，无需修改历史记录即可完成关联：

1. 主用户表

CREATE TABLE tblUsers (
    userid INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    main_user_id INT DEFAULT NULL, -- 主用户此字段为NULL，别名用户指向对应主用户ID
    FOREIGN KEY (main_user_id) REFERENCES tblUsers(userid)
);

2. 统计数据表（保持原有结构，无需修改）

CREATE TABLE tblStats (
    statid INT PRIMARY KEY AUTO_INCREMENT,
    userid INT NOT NULL,
    score INT NOT NULL,
    FOREIGN KEY (userid) REFERENCES tblUsers(userid)
);

数据示例 #

tblUsers数据：

userid   username  main_user_id
...............................
1        john      4
2        mary      NULL
3        john2     4
4        john3     NULL

• john3是主用户，john、john2为其别名
• mary为独立主用户

tblStats数据：

userid   score 
...............
1        10
1        100
3        50
4        150

合并分数查询 #

MySQL 8.0+（支持递归CTE）

WITH RECURSIVE user_hierarchy AS (
    -- 主用户自身作为层级起点
    SELECT userid, username, userid AS main_id
    FROM tblUsers
    WHERE main_user_id IS NULL
    UNION ALL
    -- 递归关联所有别名用户到主用户
    SELECT u.userid, u.username, uh.main_id
    FROM tblUsers u
    JOIN user_hierarchy uh ON u.main_user_id = uh.userid
)
SELECT uh.main_id, MAX(u.username) AS main_username, SUM(s.score) AS totScore
FROM user_hierarchy uh
JOIN tblStats s ON uh.userid = s.userid
LEFT JOIN tblUsers u ON uh.main_id = u.userid
GROUP BY uh.main_id;

MySQL 8.0以下版本

SELECT COALESCE(u2.userid, u1.userid) AS main_id,
       COALESCE(u2.username, u1.username) AS main_username,
       SUM(s.score) AS totScore
FROM tblUsers u1
LEFT JOIN tblUsers u2 ON u1.main_user_id = u2.userid
JOIN tblStats s ON u1.userid = s.userid
GROUP BY main_id;

处理用户名关联 #

当需要将旧用户名关联到新主用户时，只需执行一次更新：

UPDATE tblUsers 
SET main_user_id = 4 
WHERE username IN ('john', 'john2');

新增别名用户时，直接插入记录并指定main_user_id即可，无需修改历史数据。

方案二：简化主用户标记方案 #

如果不想调整表结构复杂度，可优化现有字段逻辑：

修改表结构 #

将原alias字段改为main_user_id，默认值设为用户自身ID（即默认自己是主用户）：

ALTER TABLE tblUsers CHANGE alias main_user_id INT DEFAULT userid;

数据示例 #

userid   username  main_user_id
...............................
1        john      4
2        mary      2
3        john2     4
4        john3     4

合并分数查询 #

直接按main_user_id分组即可：

SELECT u.main_user_id, MAX(u.username) AS main_username, SUM(s.score) AS totScore
FROM tblUsers u
JOIN tblStats s ON u.userid = s.userid
GROUP BY u.main_user_id;

处理用户名关联 #

同样只需更新目标用户的main_user_id：

UPDATE tblUsers 
SET main_user_id = 4 
WHERE username IN ('john', 'john2');

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