MySQL选择最佳匹配 [英] Mysql select by best match with like

问题描述

我有这个查询:

SELECT `id` FROM `accounts` 
WHERE AES_DECRYPT(`email`, :salt) = CONCAT_WS('@',:mailbox,:host)
OR AES_DECRYPT(`email`, :salt) LIKE CONCAT('%',:host)

我在该表中有2条记录:

I have 2 records in that table:

id     email
1      [e-mail]
2      [e-mail]

当我像这样运行此查询时:

When I'm running this query like this:

SELECT `id` FROM `accounts` 
WHERE AES_DECRYPT(`email`, '123') = CONCAT_WS('@','test','test.com') 
OR AES_DECRYPT(`email`, '123') LIKE CONCAT('%','test.com')

我得到这个结果:

id     email
2      [e-mail]
1      [e-mail]

问题: 我想要的是:我希望拥有最匹配的结果作为第一个结果，而无需使用全文本搜索. 如果可以的话，这可能吗?

Question: What I want is this: I want to have the best match as the first result, without using fulltext search. Is this possible, if so, how can I do this?

推荐答案

您可以按照匹配数轻松地对结果进行排序:

You can readily order the results by the number of matches:

SELECT `id`
FROM `accounts`
WHERE AES_DECRYPT(`email`, '123') = CONCAT_WS('@', 'test', 'test.com') OR 
      AES_DECRYPT(`email`, '123') LIKE CONCAT('%','test.com')
ORDER BY ( (AES_DECRYPT(`email`, '123') = CONCAT_WS('@', 'test', 'test.com')) +
           (AES_DECRYPT(`email`, '123') LIKE CONCAT('%','test.com'))
         );

这将适合您的示例.

这篇关于MySQL选择最佳匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！