MySQL 列生成

---

简介：在本教程中，您将学习如何使用MySQL生成的列来存储从表达式或其他列计算的数据。

---

MySQL生成列简介

创建新表时，请在CREATE TABLE语句中指定表列。然后，您可以使用INSERT，UPDATE和DELETE 语句直接修改表中的列数据。 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
);

要获取联系人的全名，请使用以下CONCAT()函数：

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]]

1. 指定列名称及其数据类型。
2. 添加GENERATED ALWAYS子句以指示列是生成的列。
3. 使用相应的选项指示生成列的类型：VIRTUAL或STORED。默认情况下，如果您未明确指定生成列的类型，则使用VIRTUAL 。
4. 在AS关键字后面的大括号内指定表达式。表达式可以包含文字，没有参数的内置函数，运算符或对同一表中任何列的引用。如果使用函数，它必须是标量和确定性的。
5. 如果存储了生成的列，则可以为其定义  唯一约束。

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生成的列，以存储从表达式或其他列计算的数据。

---