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

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 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 Server Time Zone Support”。

可以看到，两个distinct的UTC值在'MET'时间区中变为相同。这一现象可能会导致对给定的TIMESTAMP列查询的不同结果，取决于优化器是否使用索引执行查询。

假设查询从前面示例中的表中选择值，使用TIMESTAMP列中的单个特定值进行搜索，如用户提供的时间戳字面量：

SELECT ts FROM tstable
WHERE ts = 'literal';

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

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

在这些条件下，WHERE子句中的比较在非索引和索引查找中发生 differently，导致不同的结果：

如果没有索引或优化器不能使用它，比较在会话时区中发生。优化器执行表扫描，首先将每个TIMESTAMP列值从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 |
+---------------------+
```
由于存储的TIMESTAMP值被转换到会话时区，因此查询可能返回两个时间戳值，它们在UTC值中是不同的，但在会话时区中是相同的：一个值在时钟更改前发生的 DST shift，另一个值在时钟更改后发生的 DST shift。
如果有可用的索引，比较在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 |
+---------------------+
```
在这种情况下，转换后的搜索值仅与索引条目匹配，因为索引条目中的distinct存储UTC值也都是distinct，因此搜索值只能匹配其中的一个。

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

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

在前面的讨论中，存储在tstable中的数据集恰好包含distinct的UTC值。在这种情况下，所有使用索引的查询都将最多匹配一个索引条目。

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

如果需要返回在会话时间区匹配的所有ts值，可以使用IGNORE INDEXhint来抑制索引使用：

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