全文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个字符的片段返回结果,这样用户就可以感受到这一点.所以,例如,如果我搜索“独角兽头发”,我会得到这样的结果：

-------------------------------------------------
| 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  |

请注意,同一记录有两个结果.那可能吗？

如果匹配位于字段的开头,则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)