获取 2 个单词匹配的 MySQL 行
我正在尝试基于 2 个 MySQL 表构建一个简单的搜索。一种称为关键字(单词),另一种称为keyword2data(将单词绑定到数据源的映射)。
keywords 保存 id 和 keywords,而 keywords2data 保存 keywords_id 和 data_id。
data_id 它本身是对第三个表的引用,但在本例中并不重要。
我想要的是能够搜索例如“狗拉雪橇”并获取所有绑定了这些关键字的 data_id。
SELECT k2d.`data_id` , k2d.`keyword_id`
FROM keywords2data as k2d, keywords as k
WHERE k2d.`keyword_id` = k.`id`
&& (k.`keyword` = 'dog' || k.`keyword` = 'sled')
LIMIT 10
给我所有绑定了狗或雪橇的 data_id,不需要两者都绑定,这就是我想要的。
SELECT k2d.`data_id` , k2d.`keyword_id`
FROM keywords2data as k2d, keywords as k
WHERE k2d.`keyword_id` = k.`id`
&& (k.`keyword` = 'dog' && k.`keyword` = 'sled')
LIMIT 10
没有给我任何东西,因为 keywords2data 中没有单行包含 2 个关键字。
这样做的正确方法是什么?
I am trying to build an easy search based on 2 MySQL
tables. One called keywords (words) and another called keyword2data (map which binds words to datasource).
Keywords holds id and keyword whilst keywords2data holds keyword_id and data_id.
data_id it self is a reference to a 3rd but in this case unimportant table.
What i want is to be able to search for example "dog sled" and get all data_id's which has those keywords bound to it.
SELECT k2d.`data_id` , k2d.`keyword_id`
FROM keywords2data as k2d, keywords as k
WHERE k2d.`keyword_id` = k.`id`
&& (k.`keyword` = 'dog' || k.`keyword` = 'sled')
LIMIT 10
Gives me all data_id which has either dog or sled bound to it, not necessary both, which is what i want.
SELECT k2d.`data_id` , k2d.`keyword_id`
FROM keywords2data as k2d, keywords as k
WHERE k2d.`keyword_id` = k.`id`
&& (k.`keyword` = 'dog' && k.`keyword` = 'sled')
LIMIT 10
Gives me nothing since no single row in keywords2data holds 2 keywords.
What is the right way to do this?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
怎么样
How about something like
这个怎么样?
How about this?
可能是这个?
要扩展以匹配更多关键字,您只需向子查询中的
OR
语句添加更多单词,然后更改=2
即可。这假设每个数据项都使用
kerywords2data
链接到关键字一次且仅一次。这是一个不返回重复的
data_id
的版本(根据评论),而且根本不返回任何关键字:Possibly, this?
To extend to match more keywords you'd just add more words to the
OR
statement in the subquery and change the=2
afterwards.This assumes that each data item is linked to a keyword using
kerywords2data
once and only once.Here's a version that doesn't return the
data_id
repeated (as per comments), but also doesn't return any keywords at all: