极客笔记MySQL 如何使用内置函数在MySQL虚拟GENERATED COLUMNS中工作？
MySQL是目前全球最为流行的开源关系型数据库管理系统，它提供了各种内置函数来满足不同开发需求。在MySQL 5.7版本之后，新增了虚拟列——GENERATED COLUMNS，其值可以是使用其他列计算出来的值或者使用内置函数生成的值。虚拟列的值不会像普通列一样被存储在表中，而是在查询时根据需要计算得出。本文将详细介绍如何利用内置函数在MySQL虚拟GENERATED COLUMNS中工作。
阅读更多：MySQL 教程
什么是虚拟列——GENERATED COLUMNS？
虚拟列（VIRTUAL COLUMN）是MySQL 5.7版本中的一项新特性，也称为计算列或衍生列，可以根据其他列的值或特定的算法生成一个值，并在查询时被调用。 它们会被保存在信息schema表中，而不是数据表中。虚拟列是可以通过SELECT语句查询，但不能被用于UPDATE或INSERT操作。
为何要使用GENERATED COLUMNS？
虚拟列——GENERATED COLUMNS 的设计初衷是减少数据库的存储压力和提高性能。例如我们可以在表格中计算一个员工的年龄，然后将其存储为虚拟列，这样数据库不再需要每次计算每个员工的年龄并且可以提高查询的速度。此外，利用虚拟列可以实现更为复杂的计算，简化数据分析工作。
如何使用内置函数在虚拟列中工作？
使用内置函数来计算虚拟列的值是非常方便和易用的。以下是一些常见的内置函数：
- ABS: 绝对值
- CONCAT: 合并字符串
- DATE_FORMAT: 日期格式化
- DAY: 获取日期中的天数
- HOUR: 获取日期中的小时数
- MINUTE: 获取日期中的分钟数
- MONTH: 获取日期中的月份
- ROUND: 四舍五入
- TRIM: 移除字符串两端的空格
以ABS为例，以下是如何在MySQL虚拟GENERATED COLUMNS中使用该函数的语法：
-- 定义虚拟列，用ABS函数计算salary的绝对值
ALTER TABLE employee ADD COLUMN abs_salary INT AS (ABS(salary));
同样的，以CONCAT为例，以下是如何在MySQL虚拟GENERATED COLUMNS中使用该函数的语法：
-- 定义虚拟列，将first_name, middle_name和last_name拼接起来
ALTER TABLE employee ADD COLUMN full_name VARCHAR(100) AS (CONCAT(first_name, ' ', middle_name, ' ', last_name));
我们也可以在虚拟列中使用聚合函数，例如SUM、COUNT和AVG等。以下是如何使用AVG函数求解salary的平均值：
-- 定义虚拟列，用AVG函数计算salary的平均值
ALTER TABLE employee ADD COLUMN avg_salary INT AS (SELECT AVG(salary) FROM employee);
使用虚拟列——GENERATED COLUMNS 的另一个好处是可以在数据表中添加虚拟列，而不会对原始数据表的结构进行更改。
简单实践
假设我们有一个名为 employees 的数据表，其中包含以下列：
- id
- first_name
- last_name
- salary
- start_date
我们希望计算一些虚拟列，其中包含与日期相关的计算，例如员工工作了多少年或多少个月，以及他们的薪水是否达到了该公司的平均水平。以下是代码示例：
-- 添加虚拟列，计算员工工作年限
ALTER TABLE employees ADD COLUMN work_years INT AS (YEAR(CURRENT_DATE()) - YEAR(start_date));
-- 添加虚拟列，计算员工工作月数
ALTER TABLE employees ADD COLUMN work_months INT AS (YEAR(CURRENT_DATE()) - YEAR(start_date)) * 12 + MONTH(CURRENT_DATE()) - MONTH(start_date);
-- 添加虚拟列，判断员工薪水是否高于公司平均薪水
ALTER TABLE employees ADD COLUMN salary_above_average BOOLEAN AS (salary > (SELECT AVG(salary) FROM employees));
以上代码利用内置函数YEAR和MONTH计算员工的工作年限和工作月数，并使用AVG函数计算员工薪水的平均值来判断员工薪水是否高于公司平均薪水。
我们也可以通过SELECT语句查询虚拟列，例如：
-- 查询虚拟列
SELECT id, first_name, last_name, salary, work_years, work_months, salary_above_average FROM employees;
结论
虚拟列——GENERATED COLUMNS 是MySQL 5.7版本中一项非常实用的特性，它可以通过内置函数计算值来减少数据库的存储压力和提高查询性能。在利用虚拟列时，我们可以简化复杂的计算和数据分析工作，同时也可以保持数据表的完整性和结构不变。希望本文可以帮助您更好地使用MySQL虚拟GENERATED COLUMNS，提升MySQL应用中的效率和灵活性。