mysql部分索引，反向索引 [英] mysql partial indexing, reverse indexing

问题描述

我有一个包含单词列表的表格。

I have a table with a list of words.

单词VARCHAR（16）

word VARCHAR(16)

以创建反向索引。即。字apple将索引为elppa，字banana为ananab等。

I need to create a reverse index. ie. word "apple" would index as "elppa", word "banana" as "ananab" and so on.

此外，可以索引，的话？例如，跳过前/后1或2个字符：

Also, is it possible to index, say, a part of the word? Such as, skip the first/last 1 or 2 characters:

pple（苹果）
anana（香蕉）

pple (apple) anana (banana)

这些可能吗？

推荐答案

您可以在前缀长度限制的字段上创建索引，这意味着只有前n个字符将被考虑，你不能用任意的开始和结束位置做它。有关详情，请参阅mysql的 CREATE INDEX 文档页。

You can create an index on a field that's limited by prefix-length, meaning that only the first n characters will be considered, you can't do it with an arbitrary start and end position though. Read more about it on mysql's CREATE INDEX documentation page.

在这种情况下，我只需要创建另一个列，使用mysql的 REVERSE 函数来填充它并在它上面创建一个索引，这样你得到一个字段来搜索原始单词的反向。

In this case I'd just make another column, use mysql's REVERSE function to fill it in and create an index on it, that way you get a field to search on for the reverse of the original word.

其他数据库（如Postgresql）允许您 index a expression ，这将有效地允许您在不创建额外列的情况下索引 reverse（col_name）。所以它的可能，只是不是与mysql现在。 （从版本9 potgresql有反向（）本机我相信）

Other databases such as Postgresql allow you to index an expression, which would effectively allow you to index reverse(col_name) without creating the extra column. So it it's possible, just not with mysql right now. (since version 9 potgresql has reverse() native I believe)

这篇关于mysql部分索引，反向索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！