CREATE-TABLE-AND-GENERATED-COLUMN
生成列是一种特殊的数据库表列,其值由其他列的值计算而来,而不是直接由用户插入或更新。该功能支持预先计算表达式的结果,并存储在数据库中,适用于需要频繁查询或进行复杂计算的场景。
生成列可以在数据导入或更新时自动根据预定义的表达式计算结果,并将这些结果持久化存储。这样,在后续的查询过程中,可以直接访问这些已经计算好的结果,而无需在查询时再进行复杂的计算,从而显著减少查询时的计算负担,提升查询性能。
下面是一个使用生成列的例子:
CREATE TABLE products (
product_id INT,
price DECIMAL(10,2),
quantity INT,
total_value DECIMAL(10,2) GENERATED ALWAYS AS (price * quantity)
) UNIQUE KEY(product_id)
DISTRIBUTED BY HASH(product_id) PROPERTIES ("replication_num" = "1");
INSERT INTO products VALUES(1, 10.00, 10, default);
INSERT INTO products(product_id, price, quantity) VALUES(1, 20.00, 10);
从表中查询数据:
mysql> SELECT * FROM products;
+------------+-------+----------+-------------+
| product_id | price | quantity | total_value |
+------------+-------+----------+-------------+
| 1 | 10.00 | 10 | 100.00 |
| 1 | 20.00 | 10 | 200.00 |
+------------+-------+----------+-------------+
在这个示例中, total_value 列是一个生成列,其值由 price 和 quantity 列的值相乘计算而来。 生成列的值在导入或更新时计算并存储在表中。
语法
col_name data_type [GENERATED ALWAYS] AS (expr)
[NOT NULL | NULL] [COMMENT 'string']
生成列的限制
- 使用的函数只能是内置的标量函数和运算符,不允许使用udf,聚合函数等其它。
- 不允许使用变量,子查询,Lambda表达式。
- AUTO_INCREMENT列不能用作生成的列定义中的基列。
- 生成的列定义可以引用其他生成的列,但只能引用表定义中较早出现的列。 生成的列定义可以引用表中的任何基本(非生成)列,无论其定义发生得早还是晚。
- 聚合模型中,生成列是VALUE列时,仅允许使用REPLACE和REPLACE_IF_NOT_NULL聚合类型。
导入数据
导入数据时,如果违反了生成列的NOT NULL限制,例如导入数据时,没有指定生成列引用的列,并且此列没有默认值,将导致导入失败。
INSERT
指定列时,指定的列不能包含生成列,否则将报错。
INSERT INTO products(product_id, price, quantity) VALUES(1, 20.00, 10);
没有指定列时,生成列需要使用DEFAULT关键字进行占位。
INSERT INTO products VALUES(1, 10.00, 10, default);
Load
使用load方式进行数据导入时,需要显式指定导入列。不应当指定生成列为导入列,当指定导入生成列并在数据文件中有对应的数据时,生成列不会使用数据文件中的值,生成列的值仍然是根据表达式计算得到的结果。
Stream Load
创建表:
mysql> CREATE TABLE gen_col_stream_load(a INT,b INT,c DOUBLE GENERATED ALWAYS AS (abs(a+b)) not null)
DISTRIBUTED BY HASH(a)
PROPERTIES("replication_num" = "1");
准备数据,并进行stream load:
cat gen_col_data.csv
1,2
3,5
2,9
curl --location-trusted -u root: \
-H "Expect:100-continue" \
-H "column_separator:," \
-H "columns:a,b" \
-T gen_col_data.csv \
-XPUT http://127.0.0.1:8030/api/testdb/gen_col_stream_load/_stream_load
{
"TxnId": 223227,
"Label": "d4a615c9-6e73-4d95-a8a4-e4c30d3b2262",
"Comment": "",
"TwoPhaseCommit": "false",
"Status": "Success",
"Message": "OK",
"NumberTotalRows": 3,
"NumberLoadedRows": 3,
"NumberFilteredRows": 0,
"NumberUnselectedRows": 0,
"LoadBytes": 12,
"LoadTimeMs": 152,
"BeginTxnTimeMs": 5,
"StreamLoadPutTimeMs": 39,
"ReadDataTimeMs": 0,
"WriteDataTimeMs": 66,
"CommitAndPublishTimeMs": 37
}
查看数据导入结果:
mysql> SELECT * FROM gen_col_stream_load;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 2 | 3 |
| 2 | 9 | 11 |
| 3 | 5 | 8 |
+------+------+------+
3 rows in set (0.07 sec)
HTTP Stream Load
创建表:
mysql> CREATE TABLE gencol_refer_gencol_http_load(a INT,c DOUBLE GENERATED ALWAYS AS (abs(a+b)) NOT NULL,b INT, d INT GENERATED ALWAYS AS(c+1))
DISTRIBUTED BY HASH(a)
PROPERTIES("replication_num" = "1");
准备数据,并进行http stream load:
curl --location-trusted -u root: -T gen_col_data.csv -H "Expect: 100-Continue" \
-H "sql:insert into testdb.gencol_refer_gencol_http_load(a, b) select * from http_stream(\"format\" = \"CSV\", \"column_separator\" = \",\" )" \
http://127.0.0.1:8030/api/_http_stream
{
"TxnId": 223244,
"Label": "label_824464cba2a1eabc_bee78e427ea55e81",
"Comment": "",
"TwoPhaseCommit": "false",
"Status": "Success",
"Message": "OK",
"NumberTotalRows": 3,
"NumberLoadedRows": 3,
"NumberFilteredRows": 0,
"NumberUnselectedRows": 0,
"LoadBytes": 12,
"LoadTimeMs": 142,
"BeginTxnTimeMs": 0,
"StreamLoadPutTimeMs": 45,
"ReadDataTimeMs": 46,
"WriteDataTimeMs": 59,
"CommitAndPublishTimeMs": 36
}
查看数据导入结果:
mysql> SELECT * FROM gencol_refer_gencol_http_load; +------+------+------+------+
| a | c | b | d |
+------+------+------+------+
| 2 | 11 | 9 | 12 |
| 1 | 3 | 2 | 4 |
| 3 | 8 | 5 | 9 |
+------+------+------+------+
3 rows in set (0.04 sec)
MySQL Load
建表,加载数据和查询的过程如下:
mysql> CREATE TABLE gen_col_mysql_load(a INT,b INT,c DOUBLE GENERATED ALWAYS AS (abs(a+b)) NOT NULL)
DISTRIBUTED BY HASH(a)
PROPERTIES("replication_num" = "1");
mysql> LOAD DATA LOCAL
INFILE '/path_to_data/gen_col_data.csv'
INTO TABLE gen_col_mysql_load
COLUMNS TERMINATED BY ','
(a,b);
Query OK, 3 rows affected (0.14 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT * FROM gen_col_mysql_load;
+------+------+------+
| a | b | c |
+------+------+------+
| 2 | 9 | 11 |
| 3 | 5 | 8 |
| 1 | 2 | 3 |
+------+------+------+
3 rows in set (0.06 sec)
其它Load
BROKER LOAD, ROUTINE LOAD等方式都可以将数据导入有生成列的表,不再一一列举。
生成列与部分列更新
在进行部分列更新时,必须在columns中指定生成列引用的所有普通列,否则会报错。
下面是一个示例, 建表和插入一行数据,并设置session变量:
CREATE TABLE test_partial_column_unique_gen_col (a INT, b INT, c INT AS (a+b), d INT AS (c+1), e INT)
UNIQUE KEY(a) DISTRIBUTED BY HASH(a) PROPERTIES(
"enable_unique_key_merge_on_write" = "true",
"replication_num"="1"
);
SET enable_unique_key_partial_update=true;
SET enable_insert_strict=false;
SET enable_fallback_to_original_planner=false;
INSERT INTO test_partial_column_unique_gen_col(a,b,e) VALUES(1,2,7);
如果没有指定所有被引用的普通列会报错:
mysql> INSERT INTO test_partial_column_unique_gen_col(a) VALUES(3);
ERROR 1105 (HY000): errCode = 2, detailMessage = Partial update should include all ordinary columns referenced by generated columns, missing: b
LOAD也是这样,-H "columns: a, b"中需要指定所有被引用的普通列,下面是使用stream load的示例:
curl --location-trusted -u root: -H "Expect:100-continue" -H "column_separator:," \
-H "columns: a, b" -H "partial_columns:true" \
-T /Users/moailing/Documents/tmp/gen_col_data.csv \
http://127.0.0.1:8030/api/testdb/partial_column_unique_gen_col/_stream_load