mysql - 仅在 MySQL 列的右侧部分建立索引

Question

在 InnoDB 表中，有一个 varchar 列，我只想对最后 10 个字符进行搜索查询。当我在整个列上创建索引并执行搜索 where right(mycol, 10) = p_search 时，未使用索引。有没有办法创建索引，以便对最后 10 个字符的搜索查询使用索引？我知道 MySQL 提供在左侧部分创建索引，但在这种情况下不起作用。

Answer 1

如果您使用的是 MySQL 5.7，则可以添加一个包含该列右侧的生成列并对其进行索引。

ALTER TABLE yourTable 
    ADD newCol CHAR(10) AS (RIGHT(oldCol, 10)) STORED,
    ADD INDEX (newCol);

请注意，您不必更改查询即可使用虚拟列。 documentation说:

If a generated column is indexed, the optimizer recognizes query expressions that match the column definition and uses indexes from the column as appropriate during query execution, even if a query does not refer to the column directly by name.

因此，如果您编写 WHERE RIGHT(oldCol, 10) = "abcdefghij"，优化器会将其视为 WHERE newCol = "abcdefghij" 并使用索引。

如果您使用的是旧版本，您可以将其添加为真实列，并在列发生变化时使用触发器对其进行更新(这实际上是 STORED 虚拟列自动执行的操作).但在这种情况下，您需要更改查询以测试新列。