MySQL搜索相邻记录中的单词距离

Question

我正在MySQL数据库中对静态记录执行关键词文本搜索。是否可能构建一个查询，找到记录中的第一个关键字和相邻记录中的第二个关键字？考虑以下示例数据。

------------------------------------------------------
| id | textstrings                                   |
------------------------------------------------------
|  1 | Every good boy does fine.                     |
|  2 | The quick brown fox jumped over the lazy dog. |
|  3 | I will not eat green eggs and ham.            |
|  4 | There is no time like the present.            |
|  5 | Envy is an ugly shade of green.               |
------------------------------------------------------

搜索词“green brown”应该返回2和3两条记录，因为它们是相邻的记录，但不应该包含第5条记录，因为它与第3条记录不相邻。

我知道可以查询其中一个单词并通过处理结果集来实现这一点，但我想知道是否可能将其构建到查询中。

此字段确实具有FULLTEXT索引。

- Nilpo

Answer 1

您可以将两个查询组合在一起，一个查询将搜索包含第一个搜索字符串 (green 在此示例中) 的id，另一个查询将搜索包含第二个搜索字符串 (brown 在此处) 的id。

SELECT语句的结构是为了消除因WHERE子句而可能出现的重复相邻id对。

SELECT LEAST(t1.id, t2.id) AS id1, GREATEST(t1.id, t2.id) AS id2
FROM
(
    SELECT id
    FROM table
    WHERE textstrings LIKE '%green%'
) t1
INNER JOIN
(
    SELECT id
    FROM table
    WHERE textstrings LIKE '%brown%'
) t2
ON t1.id = t2.id - 1 OR t1.id = t2.id + 1
GROUP BY LEAST(t1.id, t2.id), GREATEST(t1.id, t2.id)

点击下面的链接查看运行演示。

SQLFiddle

- Tim Biegeleisen

---

非常优雅！可以添加距离吗？比如说，相隔2条记录。我现在不需要它，只是好奇。 - Nilpo

如果您想要相隔2个记录（仅相隔2个记录），则可以将WHERE子句更改为：t1.id = t2.id - 2 OR t1.id = t2.id + 2。 - Tim Biegeleisen

我想我现在已经理解了。非常好。谢谢你的帮助！ - Nilpo

对于其他有兴趣的人，您可以通过调整比较来限制到一定的距离。 对于相差不超过3的记录（包括3），您可以使用ON t1.id> = t2.id-3 AND t1.id <= t2.id + 3。 根据需要调整数字“3”以获得记录之间的距离。 - Nilpo