MySQL :: MySQL 8.4 參考手冊 :: 10.3.14 從 TIMESTAMP 欄位進行索引查找

版本 8.4

8.0 日語

MySQL 8.4 參考手冊 / ... / 從 TIMESTAMP 欄位進行索引查找

10.3.14 從 TIMESTAMP 欄位進行索引查找

時間值以 UTC 值形式儲存在 TIMESTAMP 欄位中，而插入和從 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 |
+---------------------+

注意

若要使用命名時區 (例如 'MET' 或 'Europe/Amsterdam')，必須正確設定時區表。如需指示，請參閱章節 7.1.15，「MySQL 伺服器時區支援」。

您可以看到，當轉換為 'MET' 時區時，兩個不同的 UTC 值相同。這種現象可能會導致給定的 TIMESTAMP 欄位查詢產生不同的結果，具體取決於最佳化器是否使用索引來執行查詢。

假設查詢使用 WHERE 子句從先前顯示的表格中選取值，以便在 ts 欄位中搜尋單一特定值，例如使用者提供的時間戳記常值

SELECT ts FROM tstable
WHERE ts = 'literal';

進一步假設查詢在下列條件下執行

工作階段時區不是 UTC，並且有 DST 移位。例如
```
SET time_zone = 'MET';
```
由於 DST 移位，儲存在 TIMESTAMP 欄位中的唯一 UTC 值在工作階段時區中不是唯一的。（先前顯示的範例說明了如何發生這種情況。）
查詢指定在工作階段時區中進入 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 節「日期和時間函數」。