多字段匹配情况下怎么优先查询某个字段

[U2FsdGVkX1]

有个表有 title 和 context 字段，搜索的时候使用这个 SQL 匹配

SELECT * FROM post WHERE title LIKE "%google%" OR context LIKE "%google%" LIMIT 10

现在希望先匹配 title 字段，然后再匹配 context 字段……也就是结果 title 匹配在前，context 匹配在后

如果 title 足够 10 个的话就不用再匹配 context 了

搜了搜几乎没啥帮助（也有可能我不知道要怎么描述这个情况……），于是只能来问问了（

[ywcjxf1515]

一个未优化的思路：
select * from(

select *,1 as status from (
select * from (SELECT * FROM post WHERE title like '%Linux%' limit 10) as a
except
select * from (SELECT * FROM post WHERE context like '%算法%' limit 10) as b)
as c

union

select *,2 as status from (
select * from (SELECT * FROM post WHERE context like '%算法%' limit 10) as d
union
select * from (SELECT * FROM post WHERE title like '%Linux%' limit 10) as e)
as f

union

select *,3 as status from (
select * from (SELECT * FROM post WHERE context like '%算法%' limit 10) as g
except
select * from (SELECT * FROM post WHERE title like '%Linux%' limit 10) as i
) as j

)as s
order by status limit 10;

如果你使用 mysql 8.0，会容易很多。我觉得还是读两次数据库在内存中计算吧。