在本教學中，您將學習如何使用MySQL生成的列來存儲從運算式或其他列計算的數據。
MySQL生成列簡介
創建新表時，可以在CREATE TABLE語句中指定表列。 然後，使用INSERT，UPDATE和DELETE語句直接修改表列中的數據。
MySQL 5.7引入了一個名為生成列的新功能。它之所以叫作生成列，因為此列中的數據是基於預定義的運算式或從其他列計算的。
例如，假設有以下結構的一個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)是創建生成列的語法。
要測試“全名”列，請在contacts表中插入一行。
INSERT INTO contacts(first_name,last_name, email)
VALUES('john','doe','john.doe@xuhuhu.com');
現在，可以從contacts表中查詢數據。
當從contacts表中查詢數據時，fullname列中的值將立即計算。
MySQL提供了兩種類型的生成列：存儲和虛擬。每次讀取數據時，虛擬列都將在運行中計算，而存儲的列在數據更新時被物理計算和存儲。
基於此定義，上述示例中的fullname列是虛擬列。
MySQL生成列的語法
定義生成列的語法如下：
column_name data_type [GENERATED ALWAYS] AS (expression)
[VIRTUAL | STORED] [UNIQUE [KEY]]
首先，指定列名及其數據類型。
接下來，添加GENERATED ALWAYS子句以指示列是生成的列。
然後，通過使用相應的選項來指示生成列的類型：VIRTUAL或STORED。 默認情況下，如果未明確指定生成列的類型，MySQL將使用VIRTUAL。
之後，在AS關鍵字後面的大括弧內指定運算式。 該運算式可以包含文字，內置函數，無參數，操作符或對同一表中任何列的引用。 如果你使用一個函數，它必須是標量和確定性的。
最後，如果生成的列被存儲，可以為它定義一個唯一約束。
MySQL存儲列示例
我們來看一下示例資料庫(zaixiandb)中的products表。
mysql> desc products;
+--------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| productCode | varchar(15) | NO | PRI | | |
| productName | varchar(70) | NO | | NULL | |
| productLine | varchar(50) | NO | MUL | NULL | |
| productScale | varchar(10) | NO | | NULL | |
| productVendor | varchar(50) | NO | | NULL | |
| productDescription | text | NO | | NULL | |
| quantityInStock | smallint(6) | NO | | NULL | |
| buyPrice | decimal(10,2) | NO | | NULL | |
| MSRP | decimal(10,2) | NO | | NULL | |
+--------------------+---------------+------+-----+---------+-------+
9 rows in set
使用quantityInStock和buyPrice列的數據，通過以下運算式計算每個SKU的股票值：
quantityInStock * buyPrice
但是，可以使用以下ALTER TABLE … ADD COLUMN語句將名為stock_value的存儲的生成列添加到products表：
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 |
|************** 省略了一大波數據 ****************************|
| The Queen Mary | 272869.44 |
| American Airlines: MD-11S | 319901.40 |
| Boeing X-32A JSF | 159163.89 |
| Pont Yacht | 13786.20 |
+---------------------------------------------+-------------+
110 rows in set
在本教學中，我們向您介紹了MySQL生成的列以存儲從運算式或其他列計算的數據。