mysql:后缀搜索的有效方法(例如'％text'aka前缀通配符)?

问题描述

MySQL中是否有任何内置功能可以执行上述操作而不扫描整个表?

Is there any build-in functionality in MySQL to do the above without scanning the entire table?

我发现的唯一解决方案是存储要比较的列的镜像版本并执行like 'txet%'.

Only solution I found is to store a mirrored version of the column I want to compare and do a like 'txet%'.

我也看不到绕过postfix搜索的方法.它是用于德语词典.许多字词都有带前缀的版本，例如用户搜索"Gericht"(英语:curt)，但是知道单词"Amtsgericht"(英语，区域curt)也很有价值.不幸的是，德语中的单词的两个部分通常没有空格.实际上，大约有15％的查询使用前缀搜索.

I also don't see a way of getting around the postfix-search. It is for a German dictionary. Many words have a version with prefix, e.g. the user searches for "Gericht" (Engl. curt), but it is also valuable to know that there is a word "Amtsgericht" (Engl. district curt). Unfortunately often there is no space separating the two parts of the word in German language. Approx 15% of queries actually use the prefix-search.

推荐答案

反向字段上的索引将是解决方案，有人认为像这样:

A index over reverse field will be the solution, some think like:

create index idx_reverse on table ( reverse( field ) );
select * from table where reverse(field) like 'txet%';

但是MySQL不允许在表达式上索引，而不能在列上索引:

but MySQL don't alow index over expressions, only over columns:

这是 MySQL创建索引语法:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] ...

这是 postgres创建索引语法:

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
    ( { column | ( expression ) } [ opclass ] [, ...] )
    ...

可以创建一个解决方法，该索引创建第二个索引字段(field-> dleif)和一个 mysql触发器保留反向字段:

A work around may be create indexed second field (field -> dleif) and a mysql trigger to keep reversed field:

alter table my_table add column dleif ...;
create index idx_reverse on my_table ( dleif );
Create Trigger `reverse_field` Before Update on `my_table` for each row BEGIN
    set new.dleif = reverse( new.field );
END;
select * from table where dleif like reverse('%text');

这篇关于mysql:后缀搜索的有效方法(例如'％text'aka前缀通配符)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！