MySQL 列生成
简介:在本教程中,您将学习如何使用MySQL生成的列来存储从表达式或其他列计算的数据。
MySQL生成列简介
创建新表时,请在UPDATE和
MySQL 5.7引入了一个名为generated column 的新功能。它被称为生成列,因为此列中的数据是基于预定义的表达式或其他列计算的。
例如,您具有contacts以下结构:
CREATE TABLE IF NOT EXISTS contacts (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL
);
要获取联系人的全名,请使用以下
SELECT
id, CONCAT(first_name, ' ', last_name), email
FROM
contacts;
这还不是最漂亮的查询。
通过使用MySQL生成的列,您可以contacts按如下方式重新创建表:
DROP TABLE IF EXISTS contacts;
CREATE TABLE contacts (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
fullname varchar(101) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name)),
email VARCHAR(100) NOT NULL
);
这GENERATED ALWAYS as (expression)是创建生成列的语法。
要测试fullname列,请contacts在表中插入一行。
INSERT INTO contacts(first_name,last_name, email)
VALUES('john','doe','john.doe@mysqltutorial.org');
现在,您可以从表中查询数据contacts。
SELECT
*
FROM
contacts;
+----+------------+-----------+----------+----------------------------+
| id | first_name | last_name | fullname | email |
+----+------------+-----------+----------+----------------------------+
| 1 | john | doe | john doe | john.doe@mysqltutorial.org |
+----+------------+-----------+----------+----------------------------+
1 row in set (0.00 sec)
当您从contacts表中查询数据时,将立即计算fullname列中的值。
MySQL提供了两种类型的生成列:存储列和虚拟列。每次读取数据时都会动态计算虚拟列,而在更新数据时会物理计算和存储存储的列。
根据此定义,上例中的 fullname列是虚拟列。
MySQL生成了列的语法
定义生成列的语法如下:
column_name data_type [GENERATED ALWAYS] AS (expression)
[VIRTUAL | STORED] [UNIQUE [KEY]]
指定列名称及其数据类型。
添加GENERATED ALWAYS子句以指示列是生成的列。
使用相应的选项指示生成列的类型:VIRTUAL或STORED。默认情况下,如果您未明确指定生成列的类型,则使用VIRTUAL 。
在AS关键字后面的大括号内指定表达式。表达式可以包含文字,没有参数的内置函数,运算符或对同一表中任何列的引用。如果使用函数,它必须是标量和确定性的。
如果存储了生成的列,则可以为其定义 唯一约束。
MySQL存储列示例
我们来看看示例数据库中的products表。
+--------------------+
| products |
+--------------------+
| productCode |
| productName |
| productLine |
| productScale |
| productVendor |
| productDescription |
| quantityInStock |
| buyPrice |
| MSRP |
+--------------------+
9 rows in set (0.00 sec)
来自quantityInStock和buyPrice列的数据 允许我们使用以下表达式计算每个SKU的股票价值:
quantityInStock * buyPrice
然而,我们可以添加一个名为存储生成列stock_value在products表中使用ALTER TABLE ...ADD COLUMN语句:
ALTER TABLE products
ADD COLUMN stockValue DOUBLE
GENERATED ALWAYS AS (buyprice*quantityinstock) STORED;
通常,ALTER TABLE语句需要完整的表重建,因此,如果更改大表,则会非常耗时。但是,虚拟列不是这种情况。
现在,我们可以直接从products表中查询股票价值。
SELECT
productName, ROUND(stockValue, 2) AS stock_value
FROM
products;
+---------------------------------------------+-------------+
| productName | stock_value |
+---------------------------------------------+-------------+
| 1969 Harley Davidson Ultimate Chopper | 387209.73 |
| 1952 Alpine Renault 1300 | 720126.90 |
| 1996 Moto Guzzi 1100i | 457058.75 |
| 2003 Harley-Davidson Eagle Drag Bike | 508073.64 |
| 1972 Alfa Romeo GTA | 278631.36 |
| 1962 LanciaA Delta 16V | 702325.22 |
| 1968 Ford Mustang | 6483.12 |
| 2001 Ferrari Enzo | 345940.21 |
...
在本教程中,我们向您介绍了MySQL生成的列,以存储从表达式或其他列计算的数据。