如何有效使用 MySQL 生成列

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

MySQL生成列简介

创建新表时，您可以在CREATE TABLE语句中指定表列。然后，您使用INSERT 、 UPDATE和DELETE语句直接修改表列中的数据。

MySQL 5.7 引入了一个称为生成列的新功能。生成列是因为这些列中的数据是根据预定义的表达式计算的。

例如，您的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,
    email VARCHAR(100) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

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

SELECT 
    id, 
    CONCAT(first_name, ' ', last_name), 
    email
FROM
    contacts;Code language: SQL (Structured Query Language) (sql)

这还不是最漂亮的查询。

通过使用 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
);Code language: PHP (php)

GENERATED ALWAYS as (expression)是用于创建生成列的语法。

要测试fullname名列，请在contacts表中插入一行。

INSERT INTO contacts(first_name,last_name, email)
VALUES('john','doe','[email protected]');Code language: SQL (Structured Query Language) (sql)

现在，您可以从contacts表中查询数据。

SELECT 
    *
FROM
    contacts;Code language: SQL (Structured Query Language) (sql)

当您从contacts表中查询数据时，会动态计算fullname列中的值。

MySQL 提供两种类型的生成列：存储列和虚拟列。每次读取数据时都会动态计算虚拟列，而更新数据时会物理计算和存储存储列。

根据此定义，上例中的fullname列是虚拟列。

MySQL 生成列的语法

定义生成列的语法如下：

column_name data_type [GENERATED ALWAYS] AS (expression)
   [VIRTUAL | STORED] [UNIQUE [KEY]]Code language: SQL (Structured Query Language) (sql)

首先，指定列名称及其数据类型。

接下来，添加GENERATED ALWAYS子句以指示该列是生成列。

然后，使用相应的选项指示生成列的类型： VIRTUAL或STORED 。默认情况下，如果您没有显式指定生成列的类型，MySQL 将使用VIRTUAL 。

之后，在AS关键字后面的大括号内指定表达式。表达式可以包含文字、不带参数的内置函数、运算符或对同一表中任何列的引用。如果您使用函数，它必须是标量且确定的。

最后，如果存储了生成的列，则可以为其定义唯一约束。

MySQL 存储列示例

让我们看一下示例数据库中的products表。

通过quantityInStock和buyPrice列中的数据，我们可以使用以下表达式计算每个SKU 的库存价值：

quantityInStock * buyPriceCode language: SQL (Structured Query Language) (sql)

但是，我们可以使用以下ALTER TABLE ...ADD COLUMN语句将名为stock_value的存储生成列添加到products表中：

ALTER TABLE products
ADD COLUMN stockValue DOUBLE 
GENERATED ALWAYS AS (buyprice*quantityinstock) STORED;Code language: SQL (Structured Query Language) (sql)

通常， ALTER TABLE语句需要全表重建，因此，如果更改大表，则非常耗时。然而，虚拟列的情况并非如此。

现在，我们可以直接从products表中查询库存值。

SELECT 
    productName, 
    ROUND(stockValue, 2) stock_value
FROM
    products;Code language: SQL (Structured Query Language) (sql)

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

本教程有帮助吗？