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

版本 9.0

8.4 目前
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 從 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 值上不同但在會話時區中相等的 timestamp 值：一個值發生在時鐘變更的 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 節「日期和時間函數」。

上一步首頁上一層下一步