如何在SQL中创建为两个已有列之和的派生属性列

前置说明 #

selling_price是scost和dcost的和，属于计算列（派生列），无需手动写入值，数据库会自动根据规则计算结果。以下分建表前、表已存在两种场景，给出主流数据库的实现语法：

场景1：还未创建Software表，直接修改建表语句 #

MySQL 5.7+ 写法 #

CREATE TABLE Software
(
    name varchar(8) not null,
    title varchar(20) not null,
    dev_in varchar(8) not null,
    scost decimal(7,2),
    dcost integer(5),
    sold integer(3),
    -- VIRTUAL表示不持久化，查询时实时计算，换成STORED则会持久化存储
    selling_price decimal(7,2) GENERATED ALWAYS AS (scost + dcost) VIRTUAL
);

SQL Server 写法 #

CREATE TABLE Software
(
    name varchar(8) not null,
    title varchar(20) not null,
    dev_in varchar(8) not null,
    scost decimal(7,2),
    dcost integer(5),
    sold integer(3),
    selling_price AS (scost + dcost)
);

PostgreSQL 12+ 写法 #

CREATE TABLE Software
(
    name varchar(8) not null,
    title varchar(20) not null,
    dev_in varchar(8) not null,
    scost decimal(7,2),
    dcost integer(5),
    sold integer(3),
    selling_price decimal(7,2) GENERATED ALWAYS AS (scost + dcost) STORED
);

场景2：Software表已存在，新增派生列 #

MySQL 5.7+ 写法 #

ALTER TABLE Software 
ADD COLUMN selling_price decimal(7,2) GENERATED ALWAYS AS (scost + dcost) VIRTUAL;

SQL Server 写法 #

ALTER TABLE Software
ADD selling_price AS (scost + dcost);

PostgreSQL 12+ 写法 #

ALTER TABLE Software
ADD COLUMN selling_price decimal(7,2) GENERATED ALWAYS AS (scost + dcost) STORED;

注意事项 #

• scost为decimal(7,2)类型，dcost为int类型，求和后结果为decimal类型，selling_price建议定义为decimal(7,2)避免精度丢失
• VIRTUAL与STORED类型的区别：
  • VIRTUAL：不占用存储空间，查询时实时计算，适合写入频繁的场景
  • STORED：持久化存储到磁盘，写入时计算结果，查询性能更高，适合读多写少的场景

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