数据库中生成列的对比

原创

wx656401c57a623 2024-09-01 15:58:34 博主文章分类：postgresql ©著作权

文章标签 mysql postgresql oracle 文章分类 数据仓库大数据

©著作权归作者所有：来自51CTO博客作者wx656401c57a623的原创作品，请联系作者获取转载授权，否则将追究法律责任

简介

生成列（虚拟列）：在实际开发中，相对一个历史数据的表增加一个字段，增加下游报表，数据分析的可用性。常见的方法就是删表重建，或者使用ADD column 语法。如果是一个历史表，删掉表数据是有风险的，历史的数据不一定还能再复现。第二中ADD COLUMN语法，随然你可以再存储中增加这个字段的处理，但是先前的数据行并不会有数据。那意义也就不大了。由此postgresql衍生出生成列的概念。

生成列（虚拟列）目前仅仅支持，现有字段的值衍生计算后的值作为生成列的值。不支持子查询、表关联相关的运算。且生成列在运算中，不能被在INSERT 或 UPDATE 。以下用oracle、postgresql、mysql数据库进行举例。

语法

/****************************oracel*****************************/
CREATE TABLE products (
    product_no integer,
    name varchar2(100),
    price number,
    discounted_price AS (price * 0.9)
);
 
/****************************postgresql***********************/
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    discounted_price numeric GENERATED ALWAYS AS (price * 0.9) STORED
);
 
/*************************mysql*********************/
 
CREATE TABLE products (
    product_no INT,
    name VARCHAR(100),
    price DECIMAL(10,2),
    discounted_price DECIMAL(10,2) AS (price * 0.9)
);

oracle 示例

----向数据库中插入随机数
 
CREATE TABLE products (
    product_no integer,
    name varchar2(100),
    price number
);

插入数据随机数

BEGIN
   FOR i IN 1..1000000 LOOP
      INSERT INTO products (product_no, name, price)
      VALUES (i, 'Product ' || i, DBMS_RANDOM.VALUE(1,100));
   END LOOP;
   COMMIT;
END;
/

ALTER TABLE products ADD (discounted_price AS (price * 0.9));

和
ALTER TABLE products
ADD (discounted_price number GENERATED ALWAYS AS (price * 0.9) VIRTUAL);

两种语法是等价关系，并且VIRTUAL关键字是”虚拟化“的含义，并不会占用存储空间。

查看表占用生成空间大小

SELECT segment_name "Table Name", 
       BYTES/1024/1024 "Size (MB)"
FROM user_segments
WHERE segment_name = 'PRODUCTS';

增加生成列

使用ALTER TABLE products ADD (discounted_price AS (price * 0.9)); 语法

ALTER TABLE products ADD (discounted_price AS (price * 0.9));

增加非物理存储的生成列并没有增加表空间大小。

重新增加生成列使用ALTER TABLE products ADD (discounted_price number GENERATED ALWAYS AS (price * 0.9) VIRTUAL); 语法

##删掉刚刚增加的生成列，重新增加一个
 ALTER TABLE products drop column discounted_price ;
##新增物理存储式的生成列
ALTER TABLE products ADD (discounted_price number GENERATED ALWAYS AS (price * 0.9) VIRTUAL);

两种生成方式在Oracle中并不占用空间，只是会在表被检索的时候重新被计算出来。

mysql示例

创建测试表，并插入数据

CREATE TABLE products (
    product_no INT,
    name VARCHAR(100),
    price DECIMAL(10,2)
);
 
DELIMITER //
CREATE PROCEDURE insert_random_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 1000000 DO
    INSERT INTO products (product_no, name, price) 
    VALUES (i, CONCAT('Product ', i), RAND() * 100);
    SET i = i + 1;
  END WHILE;
END; //
DELIMITER ;
CALL insert_random_data();

为增加生成列前看一下表的空间大小

SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024  ), 2) `Size in KB` 
FROM information_schema.TABLES 
WHERE table_schema = "world"
    AND table_name = "products";

mysql 生成列的创建方式有两种

非物理存储的生成列：
ALTER TABLE products
ADD COLUMN discounted_price DECIMAL(10,2) AS (price * 0.9);
 
 
物理存储的生成列：
ALTER TABLE products
ADD COLUMN discounted_price DECIMAL(10,2) AS (price * 0.9) STORED;
 
 
在这些示例中，discounted_price是一个生成列，它的值总是等于price列的值乘以0.9。每当price列的值改变时，discounted_price列的值也会自动更新。第一个示例中的生成列是非物理存储的，即它的值在查询时实时计算。第二个示例中的生成列是物理存储的，即它的值在物理存储中保存。

使用ALTER TABLE products ADD COLUMN discounted_price DECIMAL(10,2) AS (price * 0.9);语法增加生成

ALTER TABLE products

 ADD COLUMN discounted_price DECIMAL(10,2) AS (price * 0.9);

此时表空间大小仍然是352KB 没有变化

使用ALTER TABLE products ADD COLUMN discounted_price DECIMAL(10,2) AS (price * 0.9) STORED;语法增加生成列

drop table products ;
 
CREATE TABLE products (
    product_no INT,
    name VARCHAR(100),
    price DECIMAL(10,2)
);
 
--增加数据
CALL insert_random_data();
 
--查看表大小
 
SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024  ), 2) `Size in KB` 
FROM information_schema.TABLES 
WHERE table_schema = "world"
    AND table_name = "products";
--增加生成列
 
ALTER TABLE products
ADD COLUMN discounted_price DECIMAL(10,2) AS (price * 0.9) STORED;
 
 
--再查看表大小
 
SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024  ), 2) `Size in KB` 
FROM information_schema.TABLES 
WHERE table_schema = "world"
    AND table_name = "products";

这里说明一下在mysql、oracle、postgresql三个数据库中只有mysql虚拟列物理化存储和非物理化存储，Oracle只是支持非物理化存储，postgresql只支持物理化存储。这个关于mysql的回答AI倒是没有回答错误。