在MySQL(InnoDB)中找到最匹配的行

问题描述：

我有以下测试字符串engine/mail/key和一个看起来像这样的表:

I have the following test string engine/mail/key and a table which looks like this:

+-------------+
| query       |
+-------------+
| engine      |
| engine/pdf  |
| engine/mail |
+-------------+

我想找到最匹配的行.最佳匹配由字符串/行开头的最匹配字符指定.

I want to find the best matching row. Best match is specified by the most matching characters from the start of the string/row.

我已经构建了一个RegExp，但是它当然会匹配所有行，并且不会提供任何有关匹配哪个字符/部分最多的信息.
正则表达式:^engine(/mail(/key)?)?

I have constructed a RegExp, but it of course matches all rows and won't give me any info about which matched the most chars/parts.
Regexp: ^engine(/mail(/key)?)?

关于使用MySQL的FIND_IN_SET函数，我还有其他想法:

I had an other idea about using MySQL's FIND_IN_SET function like this:

`FIND_IN_SET(query,'engine,engine/mail,engine/mail/key')`

并根据其输出对结果进行排序.

And order the result by it's output.

那行得通，但这绝不是一个很好的解决方案.有人对此有更好的主意吗?

That would work, but it's not in any way a nice solution. Does anybody have an better idea about this?

答

只使用LIKE，但反之则可能是以前的习惯.

Just use LIKE, but the other way around to what your probably used to.

select query
from table1
where 'engine/mail/key' like concat(query,'%')
order by length(query) desc
limit 1

结果:

mysql> select * from query;
+-------------+
| query       |
+-------------+
| engine      | 
| engine/pdf  | 
| engine/mail | 
+-------------+
3 rows in set (0.00 sec)

mysql> select query from query 
       where 'engine/mail/key' like concat(query,'%') 
       order by length(query) desc 
       limit 1;
+-------------+
| query       |
+-------------+
| engine/mail | 
+-------------+
1 row in set (0.01 sec)

在MySQL(InnoDB)中找到最匹配的行

相关推荐