MySQL LIKE...结果优先排序

Question

所以我决定使用...在 MySQL 中进行搜索

SELECT * 
FROM items AS i 
INNER JOIN tags AS t ON i.id = t.item_id 
WHERE item LIKE ('%s%' OR '%s2%' ... OR '%sn%') 
  AND tag LIKE ('%s%' OR '%s2%' ... OR '%sn%');

不是全文，item是VARCHAR(32)，tag是VARCHAR(16)

我在排序结果时遇到问题...当涉及到一个搜索字符串时，我可以直接确定优先级，但是当我有更多搜索字符串时，它有点胡说八道，我最终得到(搜索字符串数量)+7 个查询我觉得用它真的很脏。我觉得比用它洗澡还脏……这就是它的样子……

SELECT 
    t1.item_id, t1.item, t1.created, t1.owner, t1.type, t1.fld, t2.tag
FROM 
    items AS t1
INNER JOIN 
    tagged AS t2 ON t1.item_id = t2.item_id
WHERE 
    (item LIKE '%red%' OR '%apple%')
GROUP BY 
    item_id
ORDER BY 
    CASE 
       WHEN item = 'red'
         THEN 0 
       WHEN item = 'apple'
         THEN 1 
       WHEN (item LIKE 'red%' OR 'apple%')
         THEN 2 
       WHEN (item LIKE '%red%' AND '%apple%') 
        AND (tag LIKE '%red%' AND '%apple%')
        THEN 3 
      WHEN (item LIKE '%red%' AND '%apple%')
       AND (tag LIKE '%red%' OR '%apple%')
        THEN 4 
      WHEN (item LIKE '%red%' AND '%apple%')
        THEN 5 
      WHEN (item LIKE '%red%' OR '%apple%')
       AND (tag LIKE '%red%' OR '%apple%')
        THEN 6 
      WHEN (item LIKE '%red%' OR '%apple%')
        THEN 7 
      WHEN (tag LIKE '%red%' OR '%apple%')
        THEN 8 
      ELSE 9 
    END ASC , 
    created DESC 
LIMIT 0 , 30

是啊，太脏了，我怎么能把它剪下来呢？我实际上使用从搜索字符串生成该查询的 PHP 脚本。那个查询只比没有排序的查询慢一点，而且我们没有得到高负载，但对我来说感觉很脏。有没有更优雅的方法来做到这一点？

Answer 1

我更愿意插入案例...何时选择子句。我的查询看起来像这样:

SELECT field1, field2,
case 
 when field1 like 's1%' then 1
 when field1 like 's2%' then 2
end as ordering_field
from table_name
group by field1
order by ordering_field