MYSQL通过搜索令牌问题自定义查询顺序
现在我正在使用php和mySql构建自定义查询。说我有一个字符串你好,这是我。为此MYSQL通过搜索令牌问题自定义查询顺序
select distinct(user_primary.id) as id
from user_primary,
user_sec,
user_location,
user_ind_cat
where
(
user_primary.status != 3
and
(
user_primary.id = user_sec.job_id
)
and
(
user_primary.id = user_ind_cat.job_id
)
)
and
(
(
user_primary.name like "%Hello%"
)
or
(
user_primary.name like "%this%"
)
or
(
user_primary.name like "%is%"
)
or
(
user_primary.name like "%me%"
)
and
(
user_primary.name like "%Hello this is me%"
)
)
and
(
user_primary.login <= 1415426357
)
limit 0, 150
到目前为止,它工作正常,直到最近我发现一个问题。每当我运行这种查询时,它生成的结果都包含用户的全名,如搜索标记和从搜索标记生成的其他匹配标记。但与提供的标记“你好,这就是我”完美匹配的实际行并未显示在顶部。
让我解释的结果,如果我跑了当前查询,
- “你好你怎么样”
- “这是尼克松”
- “你好,这是我的”
- “哦,你好有”
- “我就是我”
- “你好,你有”
我想显示实际结果的顶部,这样的结果会是这样,
- “你好,这是我的”
- “你好你怎么样”
- “这是尼克松“
- ”哦,你好“
- ”我就是我“
- ”你好,你有“
任何人都可以告诉我这里有什么问题吗?或者我应该删除或添加查询?
由于提前,
尼克松
这听起来像一个全文索引工作!
ALTER TABLE `user_primary` ADD FULLTEXT INDEX (`name`);
MySQL现在创建了比您的OR链更模糊的文本搜索索引。所以,您的查询的部分看起来像:
SELECT name, MATCH(name) AGAINST ('Hello this is me') as confidence
FROM user_primary
WHERE MATCH(name) AGAINST ('Hello this is me')
ORDER BY confidence DESC
匹配越好,越高confidence
会,所以“你好,这是我”应该是在上面。
这可能是您的查询,清理和未经考验:
select distinct(user_primary.id) as id , MATCH(name) AGAINST ('Hello this is me') as confidence
from user_primary, user_sec, user_location, user_ind_cat
WHERE
user_primary.status != 3
and user_primary.id = user_sec.job_id
and user_primary.id = user_ind_cat.job_id
and MATCH(name) AGAINST ('Hello this is me')
and user_primary.login <= 1415426357
ORDER BY confidence DESC
limit 0, 150
不错。非常好的解决方案 – Benvorth 2014-11-08 09:24:22
谢谢。如果OP回答了问题,我希望OP回来并标记为“回答”。 – mainstreetmark 2014-11-08 13:59:40
@mainstreetmark我忙于其他问题,这就是为什么我没有看到这一点。 – nixon1333 2014-11-12 09:23:05
添加比赛得分和排序是:
select distinct(user_primary.id) as id,
CASE user_primary.name
WHEN user_primary.name like "%Hello this is me%" THEN 100
WHEN user_primary.name like "%Hello%" THEN 50
WHEN user_primary.name like "%this%" THEN 40
WHEN user_primary.name like "%is%" THEN 30
WHEN user_primary.name like "%me%" THEN 10
ELSE 0
END as sortScore
from user_primary,
user_sec,
-- user_location,
user_ind_cat
where
user_primary.status != 3
and user_primary.id = user_sec.job_id
and user_primary.id = user_ind_cat.job_id
and
(
user_primary.name like "%Hello this is me%"
or user_primary.name like "%Hello%"
or user_primary.name like "%this%"
or user_primary.name like "%is%"
or user_primary.name like "%me%"
)
and user_primary.login <= 1415426357
order by sortScore
limit 0, 150
是您的搜索令牌并不表示或只是不是在结果列表的顶部显示? – Benvorth 2014-11-08 08:58:35
@Benni它没有显示在结果列表的顶部。 – nixon1333 2014-11-08 08:59:53
从您的查询中删除表'user_Location',它不被使用或加入... – Benvorth 2014-11-08 09:00:02