MySQL存储的GENERATED COLUMNS如何与内置函数一起使用？

MySQL存储的GENERATED COLUMNS如何与内置函数一起使用？

1. 什么是GENERATED COLUMNS？

在MySQL 5.7中，引入了一种称为GENERATED COLUMNS的新功能。 GENERATED COLUMNS允许使用计算值建立新的列。它与虚拟列非常相似，但是GENERATED COLUMNS只能在表格中执行，不能被视为单独的存储引擎对象。

2. GENERATED COLUMNS的特点

- GENERATED COLUMNS的值会根据指定的表达式计算得到，而不是存储一个具体的值；

- GENERATED COLUMNS可以使用内置函数进行计算；

- GENERATED COLUMNS是只读的，不能被更新；

- GENERATED COLUMNS会占用存储空间，但是当表格被查询时，它们的值是即时计算得到的，而不是从磁盘读取的。

3. 使用内置函数计算GENERATED COLUMNS

计算GENERATED COLUMNS的表达式可以使用内置函数。这使得您可以轻松地对表格字段执行数学运算、字符串操作等。

使用内置函数计算生成列

例如，假设我们有一个包含温度读数和相对湿度读数的读数表。我们可以通过使用生成列来创建一个列，该生成列将温度从华氏度转换为摄氏度：

CREATE TABLE `reading` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `temperature` decimal(10,2) NOT NULL,
  `humidity` decimal(10,2) NOT NULL,
  `celsius` decimal(10,2) GENERATED ALWAYS AS (ROUND(((temperature-32)*5/9),2))) STORED,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在此示例中，我们使用ROUND()函数将华氏度转换为摄氏度，并使用GENERATED ALWAYS关键字创建了一个名为celsius的新列。注意，我们在GENERATED ALWAYS子句中使用了括号来定义我们要计算的表达式。

该表格被创建后，我们可以向其中添加一些读数：

INSERT INTO `reading` (`temperature`, `humidity`)
VALUES (68.00, 50.00);

现在，当我们查询此表格时，将返回所有原始字段及其相应的摄氏度值：

SELECT * FROM `reading`;
+----+-------------+----------+---------+
| id | temperature | humidity | celsius |
+----+-------------+----------+---------+
|  1 |       68.00 |    50.00 |   20.00 |
+----+-------------+----------+---------+

注意，celsius列包含的值是即时计算得到的，而不是从磁盘读取的。这种方法可以减少不必要的I / O，从而提高性能。

使用Math函数计算生成列

您可以使用各种内置函数计算生成列。例如，您可以使用MySQL的Math函数通过应用三角函数计算距离。下面是一个例子：

假设我们有一个存储纬度和经度的位置表，我们想要创建一个名为dist的新列，该新列将每个位置与带有指定经度和纬度的原始代理商位置进行比较，并返回两个位置之间的距离（以千米为单位）。

首先，我们需要创建一个名为shop_location的具有代理商位置的行：

INSERT INTO `location` (`latitude`, `longitude`)
VALUES (37.7749, -122.4194); -- San Francisco, CA

然后，我们可以创建包含dist生成列的新行，该生成列将使用MySQL的Math函数计算两个位置之间的距离。

ALTER TABLE `location` ADD (`dist` DECIMAL(10,2) AS (ROUND(6371.01 * ACOS(
    SIN(RADIANS(37.7749)) * SIN(RADIANS(`latitude`)) +
    COS(RADIANS(37.7749)) * COS(RADIANS(`latitude`)) *
    COS(RADIANS(-122.4194 - `longitude`))
),2))) STORED;

在此示例中，我们使用ROUND()函数四舍五入最终结果，并使用STORED将其存储在磁盘上。此外，我们使用MySQL的Math函数计算ACOS（反余弦）和SIN（正弦），以便计算距离。

现在，我们可以查询位置表格，以查看与代理商位置之间的距离：

SELECT *, `dist` AS `Distance (km)` FROM `location`;

结果将显示所有原始字段及其相应的距离值：

总结

GENERATED COLUMNS提供了一种方便的方法，可以在查询表格时自动计算值。您可以使用内置函数执行各种数学运算、字符串操作和其他操作，以便在表格中使用生成列。这有助于减少不必要的I / O，并提高查询性能。