mysql使用的单词,MySQL用单词$搜索确切的单词

I've been trying to find exact words and my research isn't helping me.

Solutions I've found:

$query .= "WHERE text REGEXP '[[:<:>:]]'";

// OR

$query .= "WHERE MATCH(text) AGAINST('$word') ";

but neither are returning my matches.

I'm searching for stock symbols in my db (eg $aapl). And using LIKE '%$word%' will return $bac if you search for $ba.

解决方案

MATCH AGAINST doesn't work exactly how you'd think it would.

This can be summarized by Casey Fulton as "...FULLTEXT searches only [return] anything if the number of results is less than 50% of the total table size..."

So instead, I'm searching for all the LIKEs and then filtering out exact words by a REGEX. I'm doing this to cut down on the process load for REGEX.

The following is my solution:

$likerows = "(SELECT * FROM `tweets` WHERE text LIKE '%$q%') AS likerows ";

$regexrows = "(SELECT * FROM $likerows WHERE text REGEXP('^.* $q .*$')) AS regexrows ";

$query = "SELECT * FROM $regexrows ";

I put spaces around the $q because I want to match a sentence-structured word (which is preceeded and followed by a space).

Also, I'm searching for "words" that start with $. This was an issue and here's what I did to solve that problem:

if(substr($q, 0, 1) == '$') $q = '\\\\' . $q; // you have to escape the backslash that escapes the $ -- it's nuts.

Best of luck.