MySQL fulltext search - workaround for numeric values and ft_min_word_len
I'm using MySQL fulltext search IN BOOLEAN MODE
but running into issues with numeric values within search strings due to the character restriction limit that is imposed by the ft_min_word_len
setting in my.cnf (currently set to 3 on this server). Is there any workaround so that a search like "something 14" would search for "something" and "14"(or even "fourteen") as currently the character restriction means that numbers under 3 digits long are ignored. Due to the fact numeric fragments in search strings are important we would need to reduce ft_min_word
to 1 to make sure all searches return correct results and this would make the indexing extremely slow. A potential solution would be to do a replace on numbers with their equivalent word using PHP and make sure the word is also added to the sea开发者_如何转开发rchable text but this feels hacky.
Any other ideas?
Yes I ran into this problem years ago and have been using this solution for my clients. It has made things work pretty well, in terms of results.
Basically what it does is split anything under 3 characters into a LIKE search and anything over 3 characters into a full text boolean search. I am currently working on releasing my version of a Layered Search. But until I get that finished. I posted the full text part of it so you can use the code. If you have any issues or make it better. I just ask that you post it on my Project Hosted site so it gets better.
The full function is shown at the source link.
$parseQuery = explode(" ",$query);
foreach($parseQuery as $key => $qvalue)
{
if(strlen($qvalue) > 3)
{
$buildQuery .= "$qvalue ";
}
elseif(strlen($qvalue) >= 2)
{
$buildLike[] = "`blog` LIKE '%$qvalue%'";
}
}
if($buildLike != "")
{
$numCountQuery = 0;
$countforbuilding = count($buildLike) - 1;
foreach($buildLike as $key => $queryforLike)
{
if($numCountQuery != $countforbuilding)
{
$finalBuildLike .= "$queryforLike AND";
}
else
{
$finalBuildLike .= "$queryforLike";
}
$numCountQuery++;
}
}
if(($finalBuildLike != "") && ($buildQuery != ""))
{
$finalBuildLike = "AND $finalBuildLike";
}
if($buildQuery != "")
{
$buildDescription = $buildQuery;
$buildQuery = "MATCH(blog) AGAINST ('".trim($buildQuery)."')";
}
http://code.google.com/p/php-mysql-layered-search/
Hope this helps.
精彩评论