MySQL中使用生成列在选择查询中

MySQL 5.7之后的版本引入了生成列的概念。简单说，生成列是一种虚拟列，它不存储在表中，而是根据表中现有的列使用某种公式计算得出。生成列的计算是在导出数据时进行的，因此不会影响表的存储。使用生成列能够快速方便地执行一些常见操作，例如计算百分比、格式化日期时间、生成html标签等。在本文中，我们将讨论如何在MySQL中使用生成列在选择查询中进行列的计算和操作。

阅读更多：MySQL 教程

基本语法

下面是MySQL中使用生成列的基本语法：

SELECT col1, col2, col3, ..., colN, expression AS column_name
FROM table;

其中，expression是生成列的公式或表达式，column_name是生成列的名称，如果未指定则使用默认名称。expression可以包括表中的现有列、运算符、函数等。

下面是一个简单的示例，在employee表中为每个员工计算每月工资：

SELECT first_name, last_name, salary, salary * 12 AS annual_salary
FROM employee;

在以上代码中，我们首先选择了一个人的名字和工资列，然后计算了年薪（每月工资乘以12），并将其命名为annual_salary。生成列的值将在结果集中返回，但不会影响表的存储。

使用表达式

生成列的公式可以使用各种算术运算符、逻辑运算符、MySQL函数等。以下是一些常用表达式的示例：

使用算术运算符

SELECT name, price, price * 0.8 AS discounted_price
FROM product;

在这个例子中，我们选择了产品名称和价格列，并使用算术运算符*计算了打折价格。生成列的名称是discounted_price。

使用逻辑运算符

SELECT name, category, CASE WHEN category = 'phone' THEN 'mobile' ELSE 'other' END AS device_type
FROM product;

在本例中，我们选择了产品名称和类别列，并使用逻辑运算符CASE WHEN ELSE和END在category列中进行条件判断，如果列值为phone，则生成列的值为mobile，否则生成列的值为other。生成列的名称是device_type。

使用MySQL函数

SELECT name, price, FORMAT(price, 2) AS formatted_price
FROM product;

在这个例子中，我们选择了产品名称和价格列，并使用MySQL函数FORMAT将价格列的值格式化为带两位小数的值。生成列的名称是formatted_price。

使用生成列进行字符串操作

生成列非常适用于字符串操作。以下是一些常见示例：

对字符串进行拼接

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employee;

在本例中，我们选择了employee表中的first_name和last_name列，并将它们拼接为一个字符串，中间用空格隔开。生成列的名称是full_name。

将字符串截断/提取

SELECT name, SUBSTR(description, 1, 30) AS short_description
FROM product;

在这个例子中，我们选择了产品名称和描述列，并使用MySQL函数SUBSTR将描述列的值截取为前30个字符。生成列的名称是short_description。

将字符串转换为小/大写

SELECT name, UPPER(name) AS uppercase_name, LOWER(name) as lowercase_name
FROM product;

在这个例子中，我们选择了产品名称列，并使用MySQL函数UPPER和LOWER将其转换为大写和小写。生成列的名称分别是uppercase_name和lowercase_name。

使用生成列进行日期时间操作

生成列还可以用于日期时间操作。以下是一些示例：

格式化日期和时间

SELECT order_date, DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date
FROM orders;

在这个例子中，我们选择了订单日期列，并使用MySQL函数DATE_FORMAT将其格式化为YYYY-MM-DD的日期字符串。生成列的名称是formatted_date。

计算日期差异

SELECT start_date, end_date, DATEDIFF(end_date, start_date) AS days_between
FROM project;

在这个例子中，我们选择了项目开始日期和结束日期列，并使用MySQL函数DATEDIFF计算两个日期之间的天数。生成列的名称是days_between。

提取日期和时间部分

SELECT order_date, YEAR(order_date) AS order_year, MONTH(order_date) AS order_month, DAY(order_date) AS order_day
FROM orders;

在本例中，我们选择了订单日期列，并使用YEAR、MONTH和DAY函数将其分别提取年、月、日的部分。生成列的名称分别是order_year、order_month和order_day。

使用条件逻辑运算符CASE WHEN

条件逻辑运算符CASE WHEN可以将不同条件的结果组合到一个生成列中。

指定简单的CASE WHEN条件

SELECT name, grade, CASE grade 
  WHEN 'A' THEN 'Excellent' 
  WHEN 'B' THEN Good' 
  WHEN 'C' THEN 'Fair' 
  ELSE 'Fail' END AS status
FROM student;

在本例中，我们选择了学生姓名和成绩列，并使用CASE WHEN条件逻辑运算符将成绩映射到学生状态列中。如果成绩是A，生成列的值为Excellent；如果成绩是B，则为Good；如果成绩是C，则为Fair；否则为Fail。生成列的名称是status。

指定搜索条件

SELECT name, grade, 
  CASE
    WHEN grade >= 90 THEN 'Excellent'
    WHEN grade >= 80 THEN 'Good'
    WHEN grade >= 70 THEN 'Fair'
    ELSE 'Fail'
  END AS status
FROM student;

在这个例子中，我们使用搜索条件将成绩映射到学生状态列中。如果成绩大于等于90，状态为Excellent；如果成绩在80到89之间，则为Good；在70到79之间，则为Fair；否则为Fail。生成列的名称是status。

总结

在本文中，我们介绍了如何在MySQL中使用生成列在选择查询中进行列的计算和操作。我们了解了生成列的基本语法，以及如何使用各种算术运算符、逻辑运算符、MySQL函数和表达式来计算生成列的值。我们还了解了如何使用生成列进行字符串操作、日期时间操作和CASE WHEN条件逻辑运算符。使用生成列可以快速方便地执行常见操作，并为MySQL的用户提供了更多的灵活性和功能。