如何在MySQL（HeidiSQL）中通过查询为列条件赋值？

步骤1：新增income_level列 #

先给表添加目标列，通用SQL语句：

ALTER TABLE chile ADD COLUMN income_level VARCHAR(20);

步骤2：根据income值批量赋值 #

使用标准SQL的CASE语句完成条件赋值，执行以下更新语句：

UPDATE chile
SET income_level = CASE
    WHEN income < 10000 THEN 'low_income'
    WHEN income BETWEEN 10000 AND 100000 THEN 'medium_income'
    WHEN income > 100000 THEN 'high_income'
END;

进阶：让列自动同步值（可选） #

如果希望后续插入新数据或更新income时，income_level自动同步计算结果，可以使用对应数据库的生成列/计算列功能：

MySQL/PostgreSQL

ALTER TABLE chile ADD COLUMN income_level VARCHAR(20) GENERATED ALWAYS AS (
    CASE
        WHEN income < 10000 THEN 'low_income'
        WHEN income BETWEEN 10000 AND 100000 THEN 'medium_income'
        WHEN income > 100000 THEN 'high_income'
    END
) STORED;

SQL Server

ALTER TABLE chile ADD income_level AS (
    CASE
        WHEN income < 10000 THEN 'low_income'
        WHEN income BETWEEN 10000 AND 100000 THEN 'medium_income'
        WHEN income > 100000 THEN 'high_income'
    END
);

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