mysql - 如何根据字段中的字符串位置确定 LIKE 查询选择的优先级？

Question

我正在尝试查询一个表以获得有限的结果集，以便在 javascript 中填充一个自动完成字段。因此，我在输入部分字符串的情况下使用 LIKE 运算符。

例如，如果我有一个表，例如:

tblPlaces
id     country
1      Balanca
2      Cameroon
3      Canada
4      Cape Verde
5      Denmark

为了这个例子，假设我想要返回两行 - 是的，对于这个例子，我在那里组成了一个国家;)我想优先考虑在国家开头匹配部分字符串的任何实例.因此，我开始使用的查询是:

SELECT id, country FROM tblPlaces WHERE country LIKE 'ca%' LIMIT 2

这按预期返回了“喀麦隆”和“加拿大”。但是，在没有两个名称的字符串在单词开头匹配的情况下(例如“de”)，我希望它在单词的其他地方查找。所以我将查询修改为

SELECT id, country FROM tblPlaces WHERE country LIKE '%ca%' LIMIT 2

这随后返回了“佛得角”和“丹麦”，但这样做打破了我最初对“ca”的搜索，现在返回“Balanca”和“喀麦隆”。

所以，我的问题是，如何使用单个查询来解决这个问题，该查询将在单词开头优先匹配(也许我需要使用 REGEXP？)我还假设如果“国家/地区”列是索引后，这些匹配项至少会按字母顺序返回(即喀麦隆在加拿大之前等)。

Answer 1

如果您要优先考虑恰好在开头的匹配...

SELECT id, country
FROM tblPlaces
WHERE country LIKE '%ca%'
ORDER BY CASE WHEN country LIKE 'ca%' THEN 0 ELSE 1 END, country
LIMIT 2

编辑

更通用，可能更快(假设“越接近开始，匹配‘越好’”)...

SELECT id, country
FROM tblPlaces
WHERE country LIKE '%ca%'
ORDER BY INSTR(country, 'ca'), country
LIMIT 2