MySQL虚拟列的使用示例_Mysql

1、概述

在 mysql 中，虚拟列（也称为计算列或生成列）是一种特殊的列，它不存储在数据库表中，而是在查询时动态计算生成。虚拟列可以基于表中的其他列进行计算，或者直接计算出一些值。它们在数据库设计和查询优化中有一些重要的用途：

减少存储需求：由于虚拟列不存储在磁盘上，因此可以减少数据库的存储需求。
提高查询效率：虚拟列在查询时动态生成，可以减少对原始数据的计算，从而提高查询效率。
数据一致性：虚拟列可以确保数据的一致性，因为它们总是基于表中其他列的最新值进行计算。
简化查询：虚拟列可以简化复杂的 sql 查询，使得查询更易于编写和理解。
数据保护：虚拟列可以用于保护敏感数据，例如，通过计算列来显示部分数据，而实际数据存储在其他列中。

在 mysql 中，虚拟列可以是存储的（stored）或非存储的（virtual）。存储的虚拟列实际上会存储在表中，而非存储的虚拟列则不会存储，仅在查询时计算。

创建虚拟列的语法

以下是创建虚拟列的基本语法：

create table table_name (
    column1 datatype,
    column2 datatype,
    virtual_column as (expression),
    ...
);

或者在现有的表中添加虚拟列：

alter table table_name
add column virtual_column as (expression);

2、示例

表结构

假设有一个员工表 employees，包含员工的姓名、年龄和出生日期：

create table `employee` (
  `id` int not null auto_increment,
  `name` varchar(100) default null,
  `age` int default null,
  `birth_date` date default null,
  `full_name` varchar(255) generated always as (concat(`name`,_utf8mb4' (',`age`,_utf8mb4' years old)')) virtual,
  primary key (`id`)
) engine=innodb auto_increment=11 default charset=utf8mb4 collate=utf8mb4_0900_ai_ci comment='员工';

在这个例子中，full_name 是一个虚拟列，它将员工的姓名和年龄结合起来生成一个完整的名称。

初始化数据

insert into `employee` values (1, 'lois simpson', 12, '2022-12-19', default);
insert into `employee` values (2, 'nakagawa sakura', 11, '2021-01-23', default);
insert into `employee` values (3, 'xiang rui', 10, '2013-04-04', default);
insert into `employee` values (4, 'ono momoe', 11, '2002-07-07', default);
insert into `employee` values (5, 'fukuda sara', 11, '2020-12-04', default);
insert into `employee` values (6, 'ryan dunn', 11, '2021-11-01', default);
insert into `employee` values (7, 'ku tsz hin', 11, '2004-10-31', default);
insert into `employee` values (8, 'todd diaz', 11, '2023-05-11', default);
insert into `employee` values (9, 'yamada kenta', 11, '2015-06-15', default);
insert into `employee` values (10, 'leslie anderson', 5, '2010-08-19', default);