在MySQL(InnoDB)中找到最匹配的行 [英] Find best matching row in 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)中找到最匹配的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！