mysql timestamp 搜索_mysql8 参考手册--从TIMESTAMP列进行索引查找

最新推荐文章于 2024-05-25 17:19:05 发布

椎名深雪

最新推荐文章于 2024-05-25 17:19:05 发布

阅读量1.1k

点赞数

文章标签： mysql timestamp 搜索

本文链接：https://blog.csdn.net/weixin_36369656/article/details/113236584

版权

本文探讨了MySQL8中TIMESTAMP列的存储和检索机制，特别是时区转换的影响。当在非UTC时区执行查询时，由于夏令时等因素，可能导致未建立索引和建立索引的查询返回不同结果。介绍了在不同场景下，如何通过索引使用和查询优化来处理时间值的不一致性。

摘要由CSDN通过智能技术生成

时间值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 |

+---------------------+注意

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

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

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

SELECT ts FROM tstable

WHERE ts = 'literal';

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

1、会话时区不是UTC，并且具有DST偏移。例如：

SET time_zone = 'MET';

2、TIMESTAMP由于DST偏移，该列中存储的唯一UTC值在会话时区中不是唯一的。(前面显示的示例说明了这种情况的发生。)

3、查询指定了在会话时区中输入DST小时内的搜索值。

在这种情况下，WHERE对于未建立索引和建立索引的查找，子句中的比较以不同的方式发生，并导致不同的结果：

如果没有索引或优化器无法使用它，则会在会话时区中进行比较。优化器执行表扫描，在其中检索每个 ts列值，将其从UTC转换为会话时区，然后将其与搜索值(也在会话时区中解释)进行比较：

mysql> SELECT ts FROM tstable

WHERE ts = '2018-10-28 02:30:00';

+---------------------+

| ts |

+---------------------+

| 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 value的多个实例 '2018-10-28 00:30:00'。在这种情况下，使用索引的查询将返回它们中的每一个(转换为'2018-10-28 02:30:00'结果集中的MET值)。仍然使用索引的查询将转换后的搜索值与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 |

+---------------------+

在其他情况下，例如使用FROM_UNIXTIME()和 UNIX_TIMESTAMP()功能执行的转换，在两个方向上也存在相同的一对一的时区转换缺少一对一映射的情况。请参见第12.6节“日期和时间函数”。