Mysql select by best match with like

Question

我有这个问题:

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

我在该表中有 2 条记录:

id     email
1      test@test.com
2      other@test.com

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

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      other@test.com
1      test@test.com

问题:我想要的是:我希望在不使用全文搜索的情况下将最佳匹配作为第一个结果。这可能吗？如果可以，我该怎么做？

Answer 1

您可以很容易地按匹配次数对结果进行排序:

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

这将适用于您的示例。