mysql like 后缀,mysql：后缀搜索的有效方法（例如'％text'aka前缀通配符）？

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

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

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%';

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

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

[index_type]

ON tbl_name (index_col_name,...)

[index_option] ...

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]

( { column | ( expression ) } [ opclass ] [, ...] )

...

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');