mysql - 全文 MySQL 搜索 - 返回片段

Question

我有一个包含书籍章节的 MySQL 表。

Table: book_chapter
--------------------------
| id | book_id | content |
--------------------------

我目前可以像这样使用全文搜索来搜索内容:

SELECT * FROM book_chapter WHERE book_chapter.book_id="2" AND
MATCH (book_chapter.content) AGAINST ("unicorn hair" IN BOOLEAN MODE)

但是，我想知道是否可以搜索内容并以 30 个字符片段的形式返回结果，以便用户能够感受到要点。例如，如果我搜索“unicorn hair”，我会得到这样的结果:

-------------------------------------------------
| id | book_id | content                        |
-------------------------------------------------
| 15 |    2    | it isn't unicorn hair. You kno |
 -------------------------------------------------
| 15 |    2    | chup and unicorn hair in soup  |
 -------------------------------------------------
| 27 |    2    | , should unicorn hair be used  |
 -------------------------------------------------
| 31 |    2    | eware of unicorn hair for bal  |

请注意，同一条记录有两个结果。这也可以吗？

Answer 1

Mike Bryant 对查询的改进

如果匹配在字段的开头，则 SUBSTRING 将从末尾开始。

我只是添加了一个 IF 语句来修复它

SELECT 
    id, 
    book_id, 
    SUBSTRING(
        content, 
        IF(LOCATE("unicorn hair", content) > 10, LOCATE("unicorn hair", content) - 10, 1),
        10 + LENGTH("unicorn hair") + 10
    ) 
FROM 
    book_chapter 
WHERE book_chapter.book_id="2" 
AND MATCH (book_chapter.content) AGAINST ("unicorn hair" IN BOOLEAN MODE)