mysql blob只取一部分,从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.

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.

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.

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

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

解决方案

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

So in your PHP you can do:

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.