从MySQL Blob字段中选择一部分。

I have a table containing lots of data and one of them is a blob. I some times needs to look into this blob for data using PHP.

我有一个包含很多数据的表格其中一个是blob。我有时需要使用PHP查看这个blob数据。

I do:

我做的事:

select `desc` from table where `desc` like '%Nam rhoncus%';

this return the entire data but I don't need it

它返回整个数据，但我不需要它

So if my description is like this:

如果我的描述是这样的:

Lorem ipsum dolor sit amet, consectetur adipiscing elit. In a tempor velit. Integer sit amet ligula nibh, eu rutrum ante. Mauris cursus, neque eu ultrices pulvinar, purus purus fermentum libero, in eleifend tortor orci quis lectus. Cras luctus nunc ac tortor laoreet eu iaculis libero consectetur. Maecenas iaculis facilisis libero sodales auctor. Donec gravida interdum vehicula. Suspendisse vitae massa eget arcu condimentum mattis. Fusce ut ligula ante, nec placerat felis. Maecenas vel nunc nibh, ut luctus urna. Nunc eu lectus a orci iaculis volutpat eget a lorem. Nunc lobortis porttitor tempor. Nulla ipsum neque, volutpat in viverra sit amet, pharetra non tortor. Phasellus at leo pellentesque nunc ultrices euismod.

Lorem ipsum dolor坐在amet上，奉献了爱的elit。在一个时间velit。整数坐amet ligula nibh, eu rutrum ante。这是一种非常有意义的语言，它是一种语言，是一种语言，是一种语言。吕克图斯从来都是神甫。我在这里为您服务。Donec孕妇interdum vehicula。悬浮液前韧带拉伤，后外侧韧带拉伤。你可以在这里住上一段时间。从那以后，我再也不能忍受痛苦了。Nunc lobortis porttitor时间。无指，无指。在利奥·佩伦特式双节语euismod的Phasellus。

Nulla ullamcorper scelerisque leo, eu consequat risus fringilla id. Nulla facilisi. Cras sit amet sem a diam molestie dignissim. Duis interdum, sapien quis laoreet bibendum, dui turpis imperdiet magna, id auctor metus velit sollicitudin dolor. Integer blandit, turpis eget interdum commodo, ante nisl laoreet dui, ac congue purus dui quis nisl. Etiam blandit eleifend tortor at egestas. Vestibulum euismod orci ac nibh consectetur feugiat. Praesent ac libero quam. Morbi elit nulla, gravida ac blandit eu, bibendum vitae lacus. In facilisis pellentesque ipsum aliquam auctor. Nam rhoncus, purus eget fringilla ullamcorper, mauris tellus fermentum lectus, ut tempus tellus arcu vel dolor. Suspendisse eros augue, tincidunt sit amet luctus et, auctor id turpis. Praesent consequat velit ut arcu convallis sodales. Proin pulvinar varius erat, id consequat orci varius sed.

这就是我们今天要讲的内容。我们的朋友们都很高兴。2 . Duis interdum, sapien quis laoreet bibendum, dui turpis imperdiet magna, id auctor metus velit sollicitudin dolor。整块空白，turpis eget interdum commodo, ante nisl laoreet dui, ac congue purus dui quis nisl。伊格斯塔斯岛。前庭Praesent ac*人quam。莫比·伊列特·努拉，请您再说一遍。在设施pellentesque ipsum aliquam auctor。红外光，紫外光，紫外光，紫外光，紫外光。悬疑的厄洛斯奥格，叮叮当当的坐着露西特和，拍卖者的身份。结果是，所有的植物都有。这是一种很好的治疗方法。

An I am looking for: Nam rhoncus I want to get the first occurence like:

我在找:我想要第一次出现:

...m auctor. Nam rhoncus, purus eg...

…卖主。南rhoncus,purus如……

2 个解决方案

#1

This query:

这个查询:

SELECT 
  CONCAT(
    '...', 
    SUBSTR(`description`, 
      LOCATE('Nam rhoncus', `description`) - 10, 
      (LENGTH('Nam rhoncus') + 20)), 
    '...') AS `description`
FROM table 
WHERE `description` LIKE '%Nam rhoncus%';

(I broke it down like this so it's easier to read)

(我像这样把它拆开，便于阅读)

this will output:

这将输出:

...m auctor. Nam rhoncus, purus eg...

…卖主。南rhoncus,purus如……

So in your PHP you can do:

在PHP中，你可以这样做:

<?php
define('CHAR_LEFT', 10);
define('CHAR_RIGHT', 10);
// db stuff
$search = mysql_real_escape_string($search_var);
$query = "SELECT CONCAT('...', SUBSTR(`description`, LOCATE('" . $search . "', `description`) - " . CHAR_LEFT . ", (LENGTH('" . $search . "') + " . (CHAR_LEFT + CHAR_RIGHT) . ")), '...') AS `description` FROM table WHERE `description` LIKE '%" . $search . "%';";
// then your request

NOTE: Ill be careful using mysql reversed words, this is why I use description instead.

注意:我会小心使用mysql颠倒的词，这就是为什么我用description代替。

#2

Something like the following should do what you need:

以下内容可以满足您的需求:

SELECT
    SUBSTR(description, INSTR(description, 'Nam rhoncus'), LENGTH('Nam rhoncus')) matchStr
FROM
    testTable
WHERE
    description like '%Nam rhoncus%'
;