计算列(Computed Column)是根据表中其他列的值通过表达式计算得出的列。以下是几种主流数据库中添加计算列的方法:
-- 创建表时添加计算列
CREATE TABLE products (
id INT PRIMARY KEY,
price DECIMAL(10,2),
quantity INT,
total_price DECIMAL(10,2) AS (price * quantity) -- 计算列
);
-- 为已有表添加计算列
ALTER TABLE products
ADD COLUMN discounted_price DECIMAL(10,2) AS (price * 0.9);
-- 创建表时添加计算列
CREATE TABLE products (
id INT PRIMARY KEY,
price DECIMAL(10,2),
quantity INT,
total_price AS (price * quantity) -- 计算列
);
-- 为已有表添加计算列
ALTER TABLE products
ADD discounted_price AS (price * 0.9);
-- 创建表时添加计算列(GENERATED ALWAYS AS)
CREATE TABLE products (
id SERIAL PRIMARY KEY,
price DECIMAL(10,2),
quantity INT,
total_price DECIMAL(10,2) GENERATED ALWAYS AS (price * quantity) STORED
);
-- 为已有表添加计算列
ALTER TABLE products
ADD COLUMN discounted_price DECIMAL(10,2) GENERATED ALWAYS AS (price * 0.9) STORED;
-- 创建表时添加计算列
CREATE TABLE products (
id NUMBER PRIMARY KEY,
price NUMBER(10,2),
quantity NUMBER,
total_price NUMBER(10,2) GENERATED ALWAYS AS (price * quantity) VIRTUAL
);
-- 为已有表添加计算列
ALTER TABLE products
ADD (discounted_price NUMBER(10,2) GENERATED ALWAYS AS (price * 0.9) VIRTUAL;
存储方式:
STORED
:计算结果物理存储在表中VIRTUAL
:每次查询时动态计算(默认)性能考虑:
不同数据库的语法差异较大,需要根据具体数据库调整
计算列通常不能直接插入或更新值
计算列可以像普通列一样用于查询条件
需要根据您的具体数据库类型选择合适的方法实现计算列。