MySQL游戏用户得分表添加游戏内排名虚拟列的技术问询

这个需求当然有办法实现，但得先理清一个关键点：MySQL的标准虚拟列（Generated Column）没法直接完成这个排名计算——因为虚拟列要求表达式是确定性的，也就是结果只能依赖当前行的列值，而你要的排名是基于同游戏下所有用户的得分对比，属于需要引用其他行数据的非确定性操作，不符合虚拟列的规则。不过我们有两种实用的替代方案，能完美达到你想要的效果：

方案1：创建带排名的视图（推荐） #

视图就像一个“虚拟表”，每次查询时会动态计算排名，既能保证数据实时准确，又不用额外维护数据。你可以用下面的SQL创建视图：

CREATE VIEW game_user_rank_view AS
SELECT 
    user_id,
    game_id,
    score,
    RANK() OVER (PARTITION BY game_id ORDER BY score DESC) AS `rank`
FROM your_table_name; -- 这里替换成你的实际表名

之后查询这个视图，就能直接得到你示例中的结果：

SELECT * FROM game_user_rank_view;

这里的RANK()函数会按game_id分组（也就是每个游戏单独计算），然后按score降序排列给出排名，和你想要的逻辑完全匹配。

方案2：用触发器维护物理排名列 #

如果一定要用类似“表列”的形式（而不是视图），可以添加一个实际的rank列，然后通过触发器在数据增删改时自动更新排名。不过这个方案需要维护触发器，数据变动频繁时会有一定性能开销，适合数据更新不频繁的场景。

步骤如下：

1. 先给表添加物理排名列：

ALTER TABLE your_table_name ADD COLUMN `rank` INT;

2. 创建插入触发器，插入新数据后更新对应游戏的排名：

DELIMITER //
CREATE TRIGGER update_rank_after_insert
AFTER INSERT ON your_table_name
FOR EACH ROW
BEGIN
    UPDATE your_table_name t1
    JOIN (
        SELECT 
            user_id,
            game_id,
            RANK() OVER (PARTITION BY game_id ORDER BY score DESC) AS new_rank
        FROM your_table_name
        WHERE game_id = NEW.game_id
    ) t2 ON t1.user_id = t2.user_id AND t1.game_id = t2.game_id
    SET t1.`rank` = t2.new_rank;
END //
DELIMITER ;

3. 同理，你还需要创建更新和删除的触发器，确保当用户得分修改或数据删除时，排名能同步更新。

额外说明 #

如果你的数据库是Oracle，它的虚拟列支持窗口函数，这时候可以直接创建虚拟列：

ALTER TABLE your_table_name ADD `rank` INT GENERATED ALWAYS AS (
    RANK() OVER (PARTITION BY game_id ORDER BY score DESC)
) VIRTUAL;

但MySQL目前（截至8.0版本）还不支持在生成列中使用窗口函数，所以还是得用前面的视图或触发器方案。

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