type
status
date
slug
summary
tags
category
icon
password
從 8.0.13 開始支持建立 Functional index

使用方式

  • 必須使用括號將表達式刮起來，用以和 columns index、 column prefixes index 區分
    • 不能僅由列名組成，此情況應使用一般的columns index
      • 在表達式內不能使用 column prefixed，但可以透過 SUBSTRING() 來變相代替，但需要注意查詢條件必須使用相同的 function 和參數才能吃到 index，如下範例：
        • 不能在 foreign key 上使用

        限制

        Functional indexes是被實現為 hidden virtual generated columns (隱藏的虛擬列)，此處使用同樣是 8.0 的新功能 show extended 顯示處隱藏的欄位，可以看到多了 3 個隨機字符串名稱的 index 其中後面寫上了 VIRTUAL GENERATED
        Functional indexes 被時限為虛擬列這有以下含意：
        • functional indexs 會記入 table 欄位的數量限制
        繼承了 generated columns 的所有限制
        • 不允許 nondeterministic built-in functions ，例如：CONNECTION_ID(), CURRENT_USER(), NOW()
        • 不允許 stored function 和 user-defined function
        • 不允許 stored procedure
        • 不允許使用變量 (包含 system variables , user-defined variables , stored program local variables )
        • 不允許子查詢
        • 不可依賴 AUTO_INCREMENT 欄位
        • 可以依賴 table 定義中較早出現的其他 generated columns ，但不可依賴較晚出現的
        • 雖然虛擬列不佔用儲存空間，但 INDEX 會占用
        除了上述還有其他的限制如下：
        • UNIQUE KEY 允許使用 Functional indexes 。但因為 primary key 必須是實際儲存的列 (stored generated columns 也可以)，所以不允許使用虛擬列實現的 Functional inedexes。
          • 若要刪除的欄位有被用來建立 Functional indexes ，則必須先移除 index
            • SPATIAL(空間) 、 FULLTEXT(全文) Indexes 不能使用 Functional indexes
             
            Functional indexes 可以使用在 JSON 欄位上，但必須使用正確的方式建構，例如以下方式建立將會導致失敗：
            失敗的原因為 JSON_UNQUOTE 返回 LONGTEXT 型態的值，這表示隱藏的虛擬列 也會被建立為相同的型態，該型態是無法建立一般索引的，只能建立為 FULLTEXT index 或 column prefixes index
            可以透過使用 CAST() 函數來轉換成 BLOB、TEXT、JSON... 以外的型態即可建立 index
             

            參考

            WL#1075: Add support for functional indexes
            MySQL 8.0 文檔
            An introduction to Functional indexes in MySQL 8.0, and their gotchas