如何将MySQL数据表列设置为另外两列的平均值自动计算列

你要的自动计算列功能在MySQL 5.7及以上版本可以直接通过**生成列（Generated Column）**实现，无需在INSERT/UPDATE语句中手动计算赋值。

实现说明 #

MySQL的生成列分为两类，可根据业务场景选择：

• VIRTUAL类型：不存储数据，每次查询时实时计算，不占用存储空间，为默认类型
• STORED类型：将计算结果持久化存储，插入/更新数据时自动计算，适合查询频率高的场景，会占用额外存储空间

修改后的建表语句 #

CREATE TABLE `tbl_leaderboard` (
    `leaderboardID` INTEGER NOT NULL AUTO_INCREMENT,
    `totalScore` INTEGER DEFAULT 0 NOT NULL,
    `gamesPlayed` INTEGER DEFAULT 0 NOT NULL,
    -- 生成列定义，VIRTUAL可按需替换为STORED
    `averageScore` INTEGER GENERATED ALWAYS AS (IFNULL(`totalScore` / NULLIF(`gamesPlayed`, 0), 0)) VIRTUAL NOT NULL,
    `player_score` INTEGER DEFAULT 0 NOT NULL,
    `userID` INTEGER NOT NULL,
    `playerID` INTEGER NOT NULL,
    PRIMARY KEY (`leaderboardID`),
    FOREIGN KEY (`userID`) REFERENCES `tbl_user`(`userID`)  ON DELETE CASCADE,
    FOREIGN KEY (`playerID`) REFERENCES `tbl_player`(`playerID`) ON DELETE CASCADE
);

语句中NULLIF和IFNULL是为了规避gamesPlayed为0时的除零错误，此时averageScore会自动赋值为0。

已创建表添加生成列的方法 #

如果表已经创建完成，可通过ALTER语句修改：

ALTER TABLE `tbl_leaderboard`
DROP COLUMN `averageScore`,
ADD COLUMN `averageScore` INTEGER GENERATED ALWAYS AS (IFNULL(`totalScore` / NULLIF(`gamesPlayed`, 0), 0)) VIRTUAL NOT NULL;

低版本MySQL兼容方案 #

如果你使用的是MySQL 5.7以下不支持生成列的版本，可以通过触发器实现自动计算：

1. 插入触发器

DELIMITER //
CREATE TRIGGER `calc_average_before_insert` BEFORE INSERT ON `tbl_leaderboard`
FOR EACH ROW
BEGIN
    SET NEW.averageScore = IFNULL(NEW.totalScore / NULLIF(NEW.gamesPlayed, 0), 0);
END //
DELIMITER ;

2. 更新触发器

DELIMITER //
CREATE TRIGGER `calc_average_before_update` BEFORE UPDATE ON `tbl_leaderboard`
FOR EACH ROW
BEGIN
    SET NEW.averageScore = IFNULL(NEW.totalScore / NULLIF(NEW.gamesPlayed, 0), 0);
END //
DELIMITER ;

注意事项 #

• 生成列不支持手动赋值，INSERT/UPDATE时不需要传入该列的值，否则会触发报错
• 如果需要保留小数的平均分，可将averageScore的字段类型从INTEGER修改为DECIMAL

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