mysql中文模糊查找不精確解決辦法

在我的理解中mysql本身就對中文支持不好，如果我們再在操作時有一點小不標准那麼查出來的中文就更亂了，下面我來給大家介紹mysql中文模糊查找不精確解決辦法

例如，通過“標題”對新聞庫進行檢索，關鍵字可能包含是中英文，如下SQL語句：

select id,title,name from achech_com.news where title like '%a%'

返回的結果，某些title字段確定帶了“a”關鍵字，而有些則只有中文，但也隨之返回在檢索結果中。

解決方法，使用 BINARY 屬性進行檢索：

select id,title,name from achech_com.news where binary title like '%a%'

返回的結果較之前正確，但英文字母區分大小寫，故有時在檢索如“Achech”及“achech”的結果是不一樣的。知道了使用 BINARY 屬性可以解決前面這個問題，再看看 MySQL 支持的UCASE 及 CONCAT 函數，其中 UCASE 是將英文全部轉成大寫，而CONCAT函數的作用是對字符進行連接，以下是完全解決後的SQL 語句：

select id,title,name from achech_com.news where binary ucase(title) like concat('%',ucase('a'),'%')

檢索的步驟是先將屬性指定為 BINARY ，以精確檢索結果，而被 like 的 title內容存在大小寫字母的可能，故先使用 ucase 函數將字段內容全部轉換成大寫字母，然後再進行 like 操作，而 like 的操作使用模糊方法，使用 concat的好處是傳進來的可以是直接的關鍵字，不需要帶“%”萬用符，將“'a'”直接換成你的變量，在任何語言下都萬事無憂了。當然你也可以這麼寫：

select id,title,name from achech_com.news where binary ucase(title) like ucase('%a%')

另一種辦法，性能更高，MySQL全文檢索

1. MySQL 4.x版本及以上版本提供了全文檢索支持，但是表的存儲引擎類型必須為MyISAM，以下是建表SQL，注意其中顯式設置了存儲引擎類型

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;   其中FULLTEXT(title, body) 給title和body這兩列建立全文索引，之後檢索的時候注意必須同時指定這兩列。

 

2. 插入測試數據

INSERT INTO articles (title,body) VALUES
    ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ('How To Use MySQL Well','After you went through a ...'),
    ('Optimizing MySQL','In this tutorial we will show ...'),
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ('MySQL vs. YourSQL','In the following database comparison ...'),
    ('MySQL Security','When configured properly, MySQL ...');

3. 全文檢索測試

SELECT * FROM articles
    WHERE MATCH (title,body) AGAINST ('database');   檢索結果如下：

5        MySQL vs. YourSQL        In the following database comparison ...1        MySQL Tutorial               DBMS stands for DataBase ...   說明全文匹配時忽略大小寫。

 

4. 可能遇到的困擾

   到目前為止都很順利，但是如果檢索SQL改為下面會怎樣呢？

SELECT * FROM articles
    WHERE MATCH (title,body) AGAINST ('well');   結果讓人大跌眼鏡，開始我也困惑了許久，後來去網上查了下才知道原來是這麼回事：

mysql指定了最小字符長度，默認是4，必須要匹配大於4的才會有返回結果，可以用SHOW VARIABLES LIKE 'ft_min_word_len' 來查看指定的字符長度，也可以在mysql配置文件my.ini 更改最小字符長度，方法是在my.ini 增加一行 比如：ft_min_word_len = 2，改完後重啟mysql即可。   所以上面不能返回結果。但是我用上面的方法改配置文件並重啟MySQL服務器後，再用show命令查看，並沒有改變。

   另外，MySQL還會計算一個詞的權值，以決定是否出現在結果集中，具體如下：

mysql在集和查詢中的對每個合適的詞都會先計算它們的權重，一個出現在多個文檔中的詞將有較低的權重(可能甚至有一個零權重)，因為在這個特定的集中，它有較低的語義值。否則，如果詞是較少的，它將得到一個較高的權重，mysql默認的閥值是50%，上面‘you’在每個文檔都出現，因此是100%，只有低於50%的才會出現在結果集中。   但是如果不考慮權重，那麼該怎麼辦呢？MySQL提供了布爾全文檢索（BOOLEAN FULLTEXT SEARCH）

   假設well在所有記錄中都出現，並且ft_min_word_len已經改為2，那麼下面的SQL檢索語句得到的結果集將包含所有記錄：

 

SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('well' IN BOOLEAN MODE );

 

5. 布爾全文檢索語法

   上面通過IN BOOLEAN MODE指定全文檢索模式為布爾全文檢索。MySQL還提供了一些類似我們平時使用seo/seo.html" target="_blank">搜索引擎時用到的的語法：邏輯與、邏輯或、邏輯非等。具體通過幾個SQL語句例子來說明

SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('+apple -banana' IN BOOLEAN MODE);   + 表示AND，即必須包含。- 表示NOT，即不包含。

SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('apple banana' IN BOOLEAN MODE);   apple和banana之間是空格，空格表示OR，即至少包含apple、banana中的一個。

SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('+apple banana' IN BOOLEAN MODE);   必須包含apple，但是如果同時也包含banana則會獲得更高的權重。

SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('+apple ~banana' IN BOOLEAN MODE);   ~ 是我們熟悉的異或運算符。返回的記錄必須包含apple，但是如果同時也包含banana會降低權重。但是它沒有 +apple -banana 嚴格，因為後者如果包含banana壓根就不返回。

SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('+apple +(>banana <orange)' IN BOOLEAN MODE);   返回同時包含apple和banana或者同時包含apple和orange的記錄。但是同時包含apple和banana的記錄的權重高於同時包含apple和orange的記錄。