在上下文中提取搜索字符串

I'm trying to do a MySQL query where I extract the search string in context. So if the search is "mysql" I'd like to return something like this from the 'body' column

我正在尝试进行MySQL查询，我在上下文中提取搜索字符串。因此，如果搜索是“mysql”，我想从“body”列返回这样的内容

"It only takes minutes from downloading the MySQL Installer to having a ready to use"

“从下载MySQL安装程序到准备使用只需几分钟”

This is what I've got now but it doesn't work because it just grabs the first 20 characters from the body field. While I'd like it to grab 20 chars in front of and behind the searched term so that the user can see what there term looks like in context:

这就是我现在所拥有的，但它不起作用，因为它只是从身体字段中抓取前20个字符。虽然我希望在搜索词的前面和后面抓取20个字符，以便用户可以在上下文中查看该术语的含义：

SELECT id, title, substring(body, 0, 20)  FROM content WHERE body LIKE '%mysql%' OR title LIKE '%mysql%';

Thanks

谢谢

2 个解决方案

#1

Here's the SQL you need:

这是您需要的SQL：

SELECT
    id,
    title,
    substring(body,  
        case
             when locate('mysql', lower(body)) <= 20 then 1
             else locate('mysql', lower(body)) - 20
        end,
        case
            when locate('mysql', lower(body)) + 20 > length(body) then length(body)
            else locate('mysql', lower(body)) + 20
        end)
FROM content
WHERE lower(body) LIKE '%mysql%'
    OR title LIKE '%mysql%'
limit 8;

FYI: Tested and works.

仅供参考：经过测试和工作。

#2

You could just pull the whole body value out and just extract the string in your code. If you're using php, you could do something like this, given you've already queried the body string and stored it in a var $body

您可以将整个身体值拉出来，只需在代码中提取字符串即可。如果你正在使用php，你可以做这样的事情，因为你已经查询了正文字符串并将其存储在var $ body中

$index = strpos($body, $searchStr);
$context = substr($body, $index-20, strlen($searchStr)+40);

#1