数据库中生成列的对比

简介

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

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

目录

简介

语法

oracle 示例

插入数据随机数

查看表占用生成空间大小

增加生成列

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倒是没有回答错误。

关键字说明

        官网给出的示例语法中有两组关键字GENERATED ALWAYS AS 、STORED。

[GENERATED ALWAYS] AS 是必须带有的，而且不可变。[STORED|VIRTUAL] 几种数据库中可以根据这个关键字来判断是否是物化存储列。