mysql怎么使用虚拟表 sql创建虚拟表

Mysql 5.7新特性: JSON字段、虚拟列、视图

alter table ba_bgt_info add v_is_auto varchar(300) generated always AS(manage_categories-"$.isAuto")

创新互联建站长期为成百上千家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为洞口企业提供专业的成都网站设计、网站制作、外贸营销网站建设，洞口网站改版等技术服务。拥有10年丰富建站经验和众多成功案例,为您定制开发。

在MySQL 5.7中，支持两种Generated Column，即Virtual Generated Column和Stored Generated Column，前者只将Generated Column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将Generated Column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与Virtual Column相比并没有优势，因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column。

如果需要Stored Generated Golumn的话，可以在Virtual Generated Column上建立索引更加合适

综上，一般情况下，都使用Virtual Generated Column，这也是MySQL默认的方式

能正常加索引、特殊用途冗余列、列长度还是和定义类型一样、查询效率上，物理STOREDVIRTUAL

虚拟列： 一个或多个字段的数据函数处理后的结果集映射成的字段

视图： 一个或多个表依照某个条件组合而成的结果集（相当于一个查询sql语句的结果集映射成的虚拟表）

其本质其实都是以空间换时间

什么是视图？视图是干什么用的？

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。

通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。

基表： 用来创建视图的表叫做基表base table

因为视图的诸多优点，如下

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

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

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

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

虚拟列 列类似于普通列，但您不能手动更改其值。这是因为表达式定义了如何根据从同一行的其他列中读取的其他值来生成生成列的值。因此，生成的列在表的域内工作，其定义不能涉及 JOIN 语句。

换句话说，您可以将生成的列视为一种视图，但仅限于列。请注意，生成的列与 SQL 触发器 不同，您只能在使用 CREATE TABLE or语句时定义它们，语法如下： ALTER TABLE

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

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

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

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

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

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

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

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

优点

缺点

优点

缺点

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

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

这将产生：

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

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

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

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

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

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

这样的列将产生：

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

MySQL之虚拟列

从MySQL 5.7开始，支持两种Generated Column，即Virtual Generated Column和Stored Generated Column;

CREATE TABLE t1 (c1 INT);

ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);

1、主键索引不能包含virtual generated column 如：

mysql create table t(a int, b int , c int as (a / b), primary key(c));

ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns.

2、Virtual Generated Column不能作为外键

3、不能使用非确定函数，如：

mysql alter table a ADD p3 DATE GENERATED ALWAYS AS (curtime()) virtual;

ERROR 3763 (HY000): Expression of generated column 'p3' contains a disallowed function: curtime.

4、无法删除源列，如：

mysql alter table t100w drop k1;

ERROR 3108 (HY000): Column 'k1' has a generated column dependency.

5、非法数据，如：

mysql create table t( x int, y int, z int as( x / 0));

Query OK, 0 rows affected (0.22 sec)

mysql insert into t(x,y) values(1,1);

ERROR 1365 (22012): Division by 0

测试sql:

SELECT test_vv FROM t100w limit 10000; #虚拟列无索引

虚拟列函数：

(concat( k1 ,_utf8mb4'-', k2 ))

mysql常见实战例子

myql 在实战使用过程中会遇到不同的场景，针对不同场景，使用方式需要整理记录，给以后遇到类似问题做参考使用。

这里主要是对dual虚拟表的一种认识。实际上可以直接函数方式：

【场景描述】

类似手机号，需要中间4位 ‘*’ 处理。如：“ 187****4567 ”，姓名3个字的中间1位 ‘*’ 处理，2个字的后面1位 ‘*’处理。如: “ 李 * , 张*研 ”

【实现方式】

（1） insert(mobile, 4,4,'****') :函数

（2） concat(left(mobile,3), '****', right(mobile,4)) :联接函数

（3） REPLACE(mobile, SUBSTR(mobile,4,4), '****') :替换函数

【实现语句】

mysql中的union用法

UNION在mysql中被称为集合操作，操作类型分为两种：UNION DISTINCT 和  UNION ALL；注意：UNION和UNION DISTINCT是一样的功能。UNION功能为合并多个查询的结果并去重，UNION ALL的功能为合并多个查询的结果不去重。

集合操作时，两边的输入必须拥有相同的列数，如果数据类型不同的话，mysql会自动进行隐式转化 ；同时，结果列的名称由第一个查询的列的名称决定。下面看一下例子：

sql语句为：SELECT 'abc' as o,'haha' as p,4 as q FROM DUAL UNION SELECT 'abc' as a,'haha',4 FROM DUAL;结果是：

如果是：SELECT 'abc' as o,'haha' as p,4 as q FROM DUAL UNION ALL SELECT 'abc' as a,'haha',4 FROM DUAL;

如果两次查询的列个数不一致，如：SELECT 'abc' as o,'haha' as p,4 as q FROM DUAL UNION ALL SELECT 'abc' as a,'haha' FROM DUAL;

在多个列查询时，只要有一个列的数据不一致时，都是无法去重的；也就是去重是每个列的数据完全一致，比如：SELECT 'abc' as o,'haha' as p,4 as q FROM DUAL UNION DISTINCT SELECT 'abc' as a,'haha',5 FROM DUAL;

这几个例子可以看出UNION (DISTINCT) 与 UNION ALL的用法了吧，下面讲一下UNION (DISTINCT）的实现方式：

一：创建一张虚拟表；

二：对这张虚拟表的列添加唯一索引，即UNIQUE INDEX;

三：将结果插入虚拟表

四：返回虚拟表; 

如何判断是否创建一个虚拟表，我们可以通过一下语句判断:

SHOW STATUS LIKE 'Created_tmp_tables'; 

SELECT 'abc' as o,'haha' as p,4 as q FROM DUAL UNION SELECT 'abc' as a,'haha',4 FROM DUAL;

SHOW STATUS LIKE 'Created_tmp_tables';                                                                                                             结果是：

可见结果3比结果1多1，在操作第二个 UNION语句时创建了一个虚拟表；如果UNION创建了索引，插入会相对变慢

mysql怎么将两个表查询出来的结果再去关联下一张表？

可以用两表的查询结果集做为一个虚拟表（为其取一个表别名），然后再用该虚拟表与另一张表实施连接查询即可。请参考以下例子：

假设有三张表

1）商品表（商品ID，商品名称）

2）入库表（商品ID，入库数量，入库时间）

3）出库表（商品ID，出库数量，出库时间）

要求列出所有商品名称、商品ID及其当前库存余额

SQL查询语句如下：

select 商品表.商品名称,商品表.商品ID,b.库存余额

from 商品表 left join

(select a.商品ID,sum(a.数量）as 库存余额 from

(select 商品ID,入库数量 as 数量 from 入库表 union all

select 商品ID,-1*出库数量 from 出库表) a group by a.商品ID) b

on 商品表.商品ID=b.商品ID;

-- 说明上述语句中的“a”和“b”分别是两个中间过渡查询结果集取的表别名，也就所谓的虚拟表。