如何使用汉字执行Mysql全文搜索？ [英] How to perform Mysql fulltext search with Chinese characters?

问题描述

我发现了这个问题的各种解决方案。一种解决方案是安装名为mysqlcft的MYSQL插件。但是，这个解决方案将不起作用，因为我的当前小项目的网络托管不支持MYSQL插件。任何其他解决方案？解决方案试图通过RandomSeed的建议FULLTEXT和亚洲语言与MySQL 5.0来解决这个问题。但问题是，除非您将ft_min_word_len设置为2，否则您无法执行2个字符的搜索。同样，$ 1 /月的Web托管服务不允许您这样做。

<好吧，花了一个晚上找出解决方法。这个概念是将一个UTF-8字符串的所有非字母字符转换为一些唯一的代码。

这是魔术功能。

 函数UTF2UCS（$ str，$ s）{
 $ str =用strtolower（$海峡）; 
 $ char ='UTF-8'; 
 $ arr = array（）; 
 $ out =; 
 $ c = mb_strlen（$ str，$ char）; 
 $ t = false; （$ i = 0; $ i <$ c; $ i ++）{
 $ arr [] = mb_substr（$ str，$ i，1，$ char）
 
; 
} 
 
 foreach（$ arr as $ i => $ v）{
 if（preg_match（'/ \w / i'，$ v，$ match） ）{
 $ out。= $ v; 
 $ t = true; 
} else {
 if（$ t）$ out。=; 
 if（isset（$ s）&& $ s）$ out。=+; 
 $ out。= bin2hex（iconv（UTF-8，UCS-2，$ v））。; 
 $ t = false; 
} 
} 
返回$ out; 
 
 $ / code> 



< pre $ echo UTF2UCS（测试haha）


将会6e2c 8a66哈哈

假设您有一个名为song_name的字段。您只需要通过UTF2UCS函数转换所有歌曲名称，然后将这些加密的字符串保存在全文索引字段中，例如song_name_ucs。

下次需要搜索时你需要做的只是：

$ p $ $ $ $ c $ $ temp_string = UTF2UCS（'测试'，true）;
SELECT * FROM歌曲WHERE MATCH（song_name_ucs）AGAINST（'$ temp_string'）


在搜索结果中同时需要'测'和'试'出现在UTF2UCS的第二个参数中。

这可能不是最好的解决方案，但它不需要任何插件或更改系统。纯PHP代码。

I have found various of solutions for this problem. One solutions is to install a MYSQL plugin called mysqlcft. However, this solution will not work since the web hosting of my current tiny project do not support MYSQL plugin. Any alternative solutions?

解决方案

Tried to solve the problem by RandomSeed's suggestion "FULLTEXT and Asian Languages with MySQL 5.0". But the problem is that you cannot perform a 2 characters search unless you set "ft_min_word_len" to 2. Again, $1/month web hosting service do not allow you to do that.

Alright, spent 1 night to work out a work-around solution. The concept is to convert all non-alphabet characters of a UTF-8 string into some unique codes.

Here is the magic function. Borrowed from CSDN forum and made some changes.

function UTF2UCS($str, $s) {
    $str = strtolower($str);
    $char = 'UTF-8';
    $arr = array();
    $out = "";
    $c = mb_strlen($str,$char);
    $t = false;

    for($i =0;$i<$c;$i++){
        $arr[]=mb_substr($str,$i,1,$char);
    }

    foreach($arr as $i=>$v){
        if(preg_match('/\w/i',$v,$match)){
            $out .= $v;
            $t = true;
        }else{
            if($t) $out .= " ";
            if(isset($s) && $s) $out .= "+";
            $out .= bin2hex(iconv("UTF-8","UCS-2",$v))." ";
            $t = false;
        }
    }
    return $out;
}

The result of

echo UTF2UCS("測試haha")

will be "6e2c 8a66 haha"

Say you have a field called "song_name". You just need to convert all song names by UTF2UCS function, then save those encrypted strings in a fulltext index field eg."song_name_ucs".

The next time you need to search something, all you need to do is:

$temp_string = UTF2UCS('測試', true);
SELECT * FROM song WHERE MATCH (song_name_ucs) AGAINST ('$temp_string') 

Remember to put a true in UTF2UCS's second parameter when you need both '測' and '試' appears in the search result.

This might not be the best solution, but it does not requires any plugin or changes to the system. Pure PHP code.

这篇关于如何使用汉字执行Mysql全文搜索？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！