MySQL :: MySQL 8.3 Reference Manual :: 10.3.14 Indexed Lookups from TIMESTAMP Columns

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / Indexed Lookups from TIMESTAMP Columns

10.3.14 从 TIMESTAMP 列中的索引查找

时间值存储在 TIMESTAMP 列中作为 UTC 值，并且从 TIMESTAMP 列中插入和检索的值都将在会话时区和 UTC 之间进行转换。（这与 CONVERT_TZ() 函数执行的转换相同。如果会话时区是 UTC，那么基本上没有时区转换。）

由于当地时区的变化，如夏令时（DST），从 UTC 到非 UTC 时区的转换不是双向的一对一的。UTC 值可能在另一个时区中不唯一。下面的示例显示了在非 UTC 时区中变得相同的两个不同的 UTC 值：

mysql> CREATE TABLE tstable (ts TIMESTAMP);
mysql> SET time_zone = 'UTC'; -- insert UTC values
mysql> INSERT INTO tstable VALUES
       ('2018-10-28 00:30:00'),
       ('2018-10-28 01:30:00');
mysql> SELECT ts FROM tstable;
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 00:30:00 |
| 2018-10-28 01:30:00 |
+---------------------+
mysql> SET time_zone = 'MET'; -- retrieve non-UTC values
mysql> SELECT ts FROM tstable;
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+

Note

要使用命名时区，如 'MET' 或 'Europe/Amsterdam'，时区表必须正确设置。有关说明，请参阅第 7.1.15 节，“MySQL 服务器时区支持”。

可以看到，两个不同的 UTC 值在转换为 'MET' 时区时变得相同。这现象可能会导致查询结果的不同，具体取决于优化器是否使用索引来执行查询。

假设查询从表中选择值，使用 WHERE 子句来搜索 ts 列中的单个特定值，例如用户提供的时间戳文字：

SELECT ts FROM tstable
WHERE ts = 'literal';

进一步假设查询在以下条件下执行：

会话时区不是 UTC 并且有 DST 转换。例如：
```
SET time_zone = 'MET';
```
存储在 TIMESTAMP 列中的唯一 UTC 值在会话时区中不唯一，因为 DST 转换。（前面的示例显示了如何发生这种情况。）
查询指定的搜索值在会话时区中的 DST 转换小时内。

在这些条件下，WHERE 子句中的比较以不同的方式进行，导致不同的结果：

如果没有索引或优化器不能使用它，比较将在会话时区中进行。优化器执行表扫描，在其中它检索每个 ts 列值，将其从 UTC 转换为会话时区，然后将其与搜索值（也在会话时区中解释）进行比较：
```
mysql> SELECT ts FROM tstable
       WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+
```
因为存储的 ts 值被转换为会话时区，因此查询可能返回两个时间戳值，它们在 UTC 中是不同的，但在会话时区中是相同的：一个值发生在 DST 转换之前，另一个值发生在 DST 转换之后。
如果有可用的索引，比较将在 UTC 中进行。优化器执行索引扫描，首先将搜索值从会话时区转换为 UTC，然后将其与 UTC 索引条目进行比较：
```
mysql> ALTER TABLE tstable ADD INDEX (ts);
mysql> SELECT ts FROM tstable
       WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
+---------------------+
```
在这种情况下，（转换后的）搜索值将匹配索引条目，因为索引条目中的存储 UTC 值也是不同的，因此搜索值只能匹配其中一个。

由于优化器对非索引和索引查找的操作不同，查询在每种情况下都产生不同的结果。非索引查找返回会话时区中的所有匹配值。索引查找不能这样做：

它是在存储引擎中执行的，该引擎只知道 UTC 值。
对于两个不同的会话时区值，它们映射到同一个 UTC 值，索引查找只能匹配对应的 UTC 索引条目，并返回单行。

在前面的讨论中，存储在 tstable 中的数据集恰好由不同的 UTC 值组成。在这种情况下，所有使用索引的查询形式最多匹配一个索引条目。

如果索引不是 UNIQUE，那么表（和索引）可能存储多个实例的给定 UTC 值。例如，ts 列可能包含多个实例的 UTC 值 '2018-10-28 00:30:00'。在这种情况下，索引使用查询将返回每个实例（将其转换为 MET 值 '2018-10-28 02:30:00' 在结果集中）。仍然是索引使用查询将转换后的搜索值匹配到 UTC 索引条目，而不是匹配多个 UTC 值，它们在会话时区中转换为搜索值。

如果需要返回会话时区中匹配的所有 ts 值，解决方法是使用 IGNORE INDEX 提示来抑制索引的使用：

mysql> SELECT ts FROM tstable
       IGNORE INDEX (ts)
       WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+

在其他上下文中，也存在时间区转换的不一对一映射问题，例如使用 FROM_UNIXTIME() 和 UNIX_TIMESTAMP() 函数进行转换。请参阅第 14.7 节，“日期和时间函数”。

PREV HOME UP NEXT