MySQL数据库必会技能，虚拟列的用法

对于想要将自动生成的数据添加到表中的任何人来说， MySQL 虚拟列是一个强大、易于使用和高级的功能。

INSERT生成的列允许您在不使用andUPDATE子句的情况下将自动生成的数据存储在表中。这个有用的特性自 5.7 版起就已成为 MySQL 的一部分，它代表了在生成数据时触发器的另一种方法。此外，生成的列可以帮助您更轻松、更高效地查询。

虚拟列列类似于普通列，但您不能手动更改其值。这是因为表达式定义了如何根据从同一行的其他列中读取的其他值来生成生成列的值。因此，生成的列在表的域内工作，其定义不能涉及JOIN语句。
换句话说，您可以将生成的列视为一种视图，但仅限于列。请注意，生成的列与SQL 触发器不同，您只能在使用CREATE TABLEor语句时定义它们，语法如下： ALTER TABLE

generate_column_name column_type [GENERATED ALWAYS] AS (generation_expression)
[VIRTUAL | STORED] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY][COMMENT 'string']

该AS (generated_column_expression)子句指定要添加或更新到表中的列是生成的列。定义 MySQL 将用于计算列值的generation_expression表达式，它不能引用另一个生成的列或除当前表的列之外的任何内容。另外，请注意生成表达式只能涉及不可变函数。例如，您不能在生成的列表达式定义中使用返回当前日期的函数，因为它是一个可变函数。

您还可以在关键字前面AS加上GENERATED ALWAYS关键字以使生成的列的性质更加明确，但这是可选的。然后，您可以指示生成列的类型是VIRTUAL还是STORED。您将在下面的章节中了解这两种类型之间的区别。默认情况下，如果没有在查询中明确指定，MySQL 会将生成的列标记为VIRTUAL.

现在让我们看看生成的列语法在CREATE TABLE查询中的作用：

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(100) NOT NULL,
first_name VARCHAR(60) NOT NULL,
last_name VARCHAR(60) NOT NULL,
full_name varchar(120) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name))
);

在此示例中，该full_name列将自动存储first_name和last_name列的连接。

MySQL 中存在哪些类型的虚拟列？

如前所述，您可以将生成的列定义为 VIRTUAL 或 STORED。现在让我们仔细看看这两种类型。

虚拟生成的列

MySQL 不存储标记为 VIRTUAL 的虚拟列。这意味着 MySQL 在需要时动态评估其值。BEFORE这通常在触发任何查询后立即发生。换句话说，虚拟生成的列不占用存储空间。

存储虚拟列列

MySQL 存储任何生成的标记为 STORED 的列。这意味着每次**或更新行时，MySQL 都会评估其值并将其存储在磁盘上。换句话说，存储列需要存储空间，就好像它是普通列一样。

虚拟生成列与存储生成列

现在让我们进一步了解虚拟列和存储生成列的优缺点。

虚拟生成的列

优点

它们的创建是即时的，因为 MySQL 只需更改 表元数据。它们不需要磁盘空间。INSERT并且UPDATE查询没有开销，因为 MySQL 不需要生成它们。

缺点

MySQL 在读取表时必须评估它们，从而使SELECT涉及它们的查询变慢。

存储生成的列

优点

MySQL 可以像读取普通列一样读取它们，从而确保快速检索而没有开销。

缺点

当添加到新表时，MySQL 必须重建整个表。INSERT或UPDATE带有开销，因为 MySQL 必须生成它们。它们需要磁盘空间。
另外，请注意您可以在一个表中混合使用 VIRTUAL 和 STORED 列，它们都支持 MySQL 索引和二级索引。但正如 官方文档中所解释的，与存储生成的列相比，虚拟列上的二级索引占用的空间和内存更少。因此，在二级索引方面，虚拟生成的列更有效。

为什么采用生成的列？

采用生成的列有几个原因，但以下三个是最重要的。

它们为您提供缓存功能，使您的查询更快：生成的列使您可以定义包含有用数据的列，以便您可以在需要时有效地检索它。它们允许您简化查询表达式：您可以将复杂性分散到生成的列上，然后在简单的过滤*作中使用它们，而不是使查询变得复杂。它们使您能够定义功能索引：MySQL 将功能索引实现 为隐藏的虚拟生成列。换句话说，生成的列使您可以定义涉及 MySQL 函数的高效和高级索引。在开发应用程序的前端时，您可能会注意到数据表示中的某些模式。例如，在体育运动中，团队中的球员通常用以下格式标识：first_name [first_carachter_middle_name.] last_name [(jersey_number)]

如您所见，您可以通过将四列与以下生成的列聚合来轻松生成此数据字段：

string_identifier VARCHAR(255) GENERATED ALWAYS AS
(CONCAT(first_name, ' ', IF(middle_name ** NULL, '', CONCAT(LEFT(middle_name, 1), '. ')), last_name, IF(jersey_number ** NULL, '', CONCAT(' (', jersey_number, ')'))))
STORED

这将产生：

Cristiano Ronaldo (7)
Lionel A. Messi (10)

在这种情况下，生成的列使您能够直接在数据库级别标准化数据字段格式。此外，存储生成的列避免了每次需要时都构造此字段的不可避免的开销。

使用生成的列自动生成散列 ID

通常，您使用网站 URL 中的资源 ID 或REST API来检索您需要的数据。但是公开暴露您的 ID 可能会带来安全问题。当您发现自己使用自动增量 ID 时尤其如此，这很容易预测并使抓取或机器人攻击更容易。

为避免这种情况，您可以考虑通过使用自动生成的、随机的、更安全的公共 ID 来隐藏您的原始 ID。您可以通过对您的 ID 进行散列处理，使用虚拟生成的列来实现这一点，如下所示：

public_id VARCHAR(40) GENERATED ALWAYS AS  SHA1(CONCAT("PLAYER", id)) VIRTUAL

请注意，为避免生成已知的哈希值，您可以将您的 ID 与特殊关键字连接起来。 在此处了解有关 MySQL 加密和压缩功能的更多信息。

定义生成的列以简化数据过滤

过滤数据时，有些列比其他列更有用。此外，您通常必须更改存储在列中的值的表示形式，以使过滤更简单或更直观。您可以定义一个有用的生成列来存储以所需格式执行过滤所需的信息，而不是在每个过滤器查询中执行此*作。

例如，您可以定义一个生成的列，以便更轻松地找到篮球队中的球员，如下所示：

filter_string VARCHAR(255) GENERATED ALWAYS AS
(CONCAT(first_name, ' ', last_name, IF(birthdate ** NULL, '', CONCAT(' ', DATE_FORMAT(birthdate, "%m-%d-%Y"))))
STORED

这样的列将产生：

LeBron James 12-30-1984
Stephen C**ry 03-14-1988

生成的列与触发器

如前所述，您只能在表中使用生成的列。此外，它们只能涉及不可变函数，并且MySQL 生成它们的值以响应INSERTorUPDATE查询。另一方面，触发器是 MySQL 自动执行的存储程序，每当与特定表关联的 或 事件发生INSERT时UPDATE。DELETE换句话说，触发器可以涉及多个表和所有 MySQL 函数。与生成的列相比，这使它们成为更完整的解决方案。同时，MySQL 触发器本质上使用和定义更复杂，也比生成的列慢。