本文目錄一覽：
mysql全文搜索，多個關鍵詞權重排序
自己在寫一個web,希望對資料庫做全文檢索。但是google了解到，由於中文分詞的緣故，mysql只支持英文的全文搜索，想支持中文的，需要各種插件or實現一些比較複雜的機制，而買的虛擬主機並不支持這些複雜的東西。仔細想了下，因為自己需求的功能也比較簡單，主要是2個欄位的搜索，且數據量不大，即便增加幾個欄位，需要多運行幾個select也不會對速度有太大影響，所以通過一些work around實現了需求。Step 1:用locate進行簡單的搜索Locate可以判斷子串是否在子亂 有兩個column,一個name,一個description.所以可以用LOCATE0去判斷是否關鍵字在其中出現了。 其實就是 SELECT * FROM table WHERE LOCATE(key, ‘name’)0 OR LOCATE(key, ‘description);這樣，我們就簡單實現了對某個key在兩個域的搜索Step 2:搜索多個關鍵字 通常，搜索都是有多個關鍵字，所以我們需要對每個關鍵字，執行下Step1的查詢。（當然，也可以合成一個，這裡偷懶每次只查詢1個關鍵字） 然後，我們再將每次查詢出的數組都合併，這樣就得到了一個最終的集合。php代碼如下：
function selectlocate($tarcols,$skey){
$where =””;
$connector = ” “;
global $count;
foreach($tarcols as $tarcol ){
$where .= $connector;
$where .= “LOCATE(‘$skey’, $tarcol) != 0 “;
if($connector == ” “){
$connector = ” OR “;
}
}
$sql = “SELECT * FROM pets_table WHERE $where”;
$result = mysql_query($sql);
$ret = Array();
while($item = mysql_fetch_array($result, MYSQL_ASSOC)){
$count ++;
$ret[] = $item;
}
return $ret;
}
Step 3：匹配的權重 上面Step2的結果，其實是無序的。通常，如果我們搜索一個欄位：1.如果這個欄位和關鍵字完全相同，那麼一般來講，可能這個結果應該是相關度最高的2.如果他只是其其中出現了一次，相關度就最低。3.如果他出現的次數比在其他row中出現的次數高，那麼他的相關度就比2中的結果高 所以，搜索的時候依據這個順序考慮權重，a.如果完全相等，權重為1000 b.如果出現1次，權重為10，出現n次c.權重為n*10每次搜索出來的結果附加上權重—-》然後合併相同項—-》並把權重累加 最後按權重排序，即可得到一個有排序的搜索結果。 以下是兩種1關鍵字對應1個欄位（上面的代碼是1關鍵字多個欄位）查詢的代碼（不包含合併兩個數組的代碼,相關的代碼在Step4中），只需遍歷每個關鍵字和欄位，就能完成搜索
$count = 0;
function selectequal($col,$skey){
$connector = ” “;
global $count;
$sql = “SELECT * FROM pets_table WHERE LOWER($col)=LOWER(‘$skey’)”;
$result = mysql_query($sql);
$ret = Array();
while($item = mysql_fetch_array($result, MYSQL_ASSOC)){
$count ++;
$item[“weight”] = 1000;
$ret[] = $item;
}
return $ret;
}
function selectlocate($col,$skey){
global $count;
$sql = “SELECT *,(LENGTH(description) – LENGTH(REPLACE(description, ‘$skey’, ”)))/LENGTH(‘$skey’) *10 as weight FROM pets_table WHERE LOCATE(LOWER(‘$skey’),LOWER($col))0″;
$result = mysql_query($sql);
$ret = Array();
while($item = mysql_fetch_array($result, MYSQL_ASSOC)){
$count ++;
$ret[] = $item;
}
return $ret;
}
Step 4: 欄位的權重 在我的需求中，顯然name這個欄位比description更重要，所以在匹配時，對name欄位的結果應該有所傾斜，所以，又可以增加一個對欄位的權重係數。1.如果是在name域的匹配，設係數為10；2.如果是在description匹配，設係數為1； 將Step 3每次計算得出的權重，再乘上這個係數，就可以得到一個新的，更有效的權重值。 最後按權重排序，即可得到一個最有相關度排序的搜索結果 其他的細節： 如果一個關鍵字已經滿足了equal條件，那麼再使用locate條件的時候會依然返回一個結果，所以在使用locate條件的時候，過濾掉equal的情況
點擊(此處)摺疊或打開
?php
$count = 0;
function selectequal($col,$val,$skey){
$connector = ” “;
global $count;
$sql = “SELECT * FROM pets_table WHERE LOWER($col)=LOWER(‘$skey’)”;
$result = mysql_query($sql);
$ret = Array();
while($item = mysql_fetch_array($result, MYSQL_ASSOC)){
$count ++;
$item[“weight”] = 1000*$val;
$ret[] = $item;
}
return $ret;
}
function selectlocate($col,$val,$skey){
global $count;
$sql = “SELECT *,(LENGTH(description) – LENGTH(REPLACE(description, ‘$skey’, ”)))/LENGTH(‘$skey’) *10*$val as weight FROM pets_table WHERE LOCATE(LOWER(‘$skey’),LOWER($col))0 AND LOWER($col)!=LOWER(‘$skey’)”;
$result = mysql_query($sql);
$ret = Array();
while($item = mysql_fetch_array($result, MYSQL_ASSOC)){
$count ++;
$ret[] = $item;
}
return $ret;
}
function cleanarr($arr){
global $count;
$tmp = Array();
$tmpall = Array();
foreach($arr as $item){
if(array_key_exists($item[‘uid’], $tmp)){
$tmp[$item[‘uid’]]+=$item[“weight”];
}
else{
$tmp[$item[‘uid’]] = $item[“weight”];
$tmpall[$item[‘uid’]] = $item;
}
}
//sort by weight in descending order
arsort($tmp);
$ret = Array();
//rebuild the return arary
$count = 0;
foreach($tmp as $k=$v){
$count++;
$tmpall[$k][‘weight’]=$v;
$ret[]=$tmpall[$k];
}
return $ret;
}
require_once(“consvr.php”);
$colshash = array(“name”=10,”description”=1);
$ret = Array();
$keywords=explode(” “, $keywords);
$cols = array_keys($colshash);
foreach($keywords as $keyword){
foreach($colshash as $col=$val){
$ret = array_merge($ret,selectequal($col,$val, $keyword));
$ret = array_merge($ret,selectlocate($col,$val, $keyword));
}
}
$ret = cleanarr($ret);
$ret = array(‘msg’ = “Success”, ‘count’=$count,’children’ = $ret, ‘query’=”COMPLEX:NOT READABLE”);
echo json_encode($ret);
mysql_close();
?
全文搜索之MySQL與ElasticSearch搜索引擎
MySQL支持全文索引和搜索功能。在MySQL中可以在CHAR、VARCHAR或TEXT列使用FULLTETXT來創建全文索引。
FULLTEXT索引主要用MATCH()…AGAINST語法來實現搜索：
MySQL的全文搜索存在以下局限：
通常來說MySQL自帶的全文搜索使用起來局限性比較大，性能和功能都不太成熟，主要適用於小項目，大項目還是建議使用elasticsearch來做全文搜索。
ElasticSearch是一個分散式的開源搜索和分析引擎，適用於所有類型的數據，包括文本、數字、地理空間、結構化和非結構化數據，以下簡稱ES。
Elasticsearch 在 Apache Lucene 的基礎上開發而成，Elasticsearch 以其簡單的 REST 風格 API、分散式特性、速度和可擴展性而聞名，是 Elastic Stack 的核心組件。Elastic Stack 是適用於數據採集、充實、存儲、分析和可視化的一組開源工具。
Elasticsearch 的實現原理主要分為以下幾個步驟，首先用戶將數據提交到Elasticsearch 數據中心，再通過分詞控制器去將對應的數據分詞，將其權重和分詞結果一併存入數據，當用戶搜索數據時候，再根據權重將結果排名，打分，再將返回結果呈現給用戶。
由於ES是基於RESTfull Web介面的，因此我們直接按照慣例傳遞JSON參數調用介面即可實現增刪改查，並且不需要我們做額外的管理操作就可以直接索引文檔，ES已經內置了所有的預設操作，可以自動幫我們定義類型。
再次執行PUT，會對庫中已有的id為1的數據進行覆蓋，每修改一次_version欄位的版本號就會加1。
默認搜索會返回前10個結果：
返回的幾個關鍵詞：
查詢字元串搜索，可以像傳遞URL參數一樣傳遞查詢語句。
精確查詢：
全文搜索：
以上兩種方法都需要考慮數據更改後如何與ES進行同步。
怎麼MySql添加全文索引
使用索引是資料庫性能優化的必備技能之一。在MySQL資料庫中，有四種索引：聚集索引(主鍵索引)、普通索引、唯一索引以及我們這裡將要介紹的全文索引(FULLTEXT INDEX)。
全文索引(也稱全文檢索)是目前搜索引擎使用的一種關鍵技術。它能夠利用「分詞技術「等多種演算法智能分析出文本文字中關鍵字詞的頻率及重要性，然後按照一定的演算法規則智能地篩選出我們想要的搜索結果。在這裡，我們就不追根究底其底層實現原理了，現在我們來看看在MySQL中如何創建並使用全文索引。
在MySQL中，創建全文索引相對比較簡單。例如，我們有一個文章表(article)，其中有主鍵ID(id)、文章標題(title)、文章內容(content)三個欄位。現在我們希望能夠在title和content兩個列上創建全文索引，article表及全文索引的創建SQL語句如下：
–創建article表
CREATE TABLE article (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
content TEXT,
FULLTEXT (title, content) –在title和content列上創建全文索引
);
上面就是在創建表的同時建立全文索引的SQL示例。此外，如果我們想要給已經存在的表的指定欄位創建全文索引，同樣以article表為例，我們可以使用如下SQL語句進行創建：
–給現有的article表的title和content欄位創建全文索引
–索引名稱為fulltext_article
ALTER TABLE article
ADD FULLTEXT INDEX fulltext_article (title, content)
在MySQL中創建全文索引之後，現在就該了解如何使用了。眾所周知，在資料庫中進行模糊查詢是使用LIKE關鍵字進行查詢，例如：
SELECT * FROM article WHERE content LIKE ‘%查詢字元串%’
那麼，我們使用全文索引也是這樣用的嗎？當然不是，我們必須使用特有的語法才能使用全文索引進行查詢。例如，我們想要在article表的title和content列中全文檢索指定的查詢字元串，可以如下編寫SQL語句：
SELECT * FROM article WHERE MATCH(title, content) AGAINST(‘查詢字元串’)
強烈注意：MySQL自帶的全文索引只能用於資料庫引擎為MyISAM的數據表，如果是其他數據引擎，則全文索引不會生效。此外，MySQL自帶的全文索引只能對英文進行全文檢索，目前無法對中文進行全文檢索。如果需要對包含中文在內的文本數據進行全文檢索，我們需要採用Sphinx(斯芬克斯)/Coreseek技術來處理中文。本站將會在後續文章中對Sphinx以及Coreseek進行介紹。
備註1：目前，使用MySQL自帶的全文索引時，如果查詢字元串的長度過短將無法得到期望的搜索結果。MySQL全文索引所能找到的詞的默認最小長度為4個字元。另外，如果查詢的字元串包含停止詞，那麼該停止詞將會被忽略。
備註2：如果可能，請盡量先創建表並插入所有數據後再創建全文索引，而不要在創建表時就直接創建全文索引，因為前者比後者的全文索引效率要高。
如何使用mysql的全文索引搜索
你有沒有想過如何使用搜索功能在所有整站中實現！互聯網博客和網站，大多數都採用MySQL資料庫。MySQL提供了一個美妙的方式實施一個小的搜索引擎，在您的網站（全文檢索）。所有您需要做的是擁有的MySQL 4.x及以上。MySQL提供全文檢索功能，我們可以用它來 ??實現搜索功能。
首先，讓我們為我們的例子中設置一個示例表。我們將創建一個名為第一個表。
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
);
在此表中還可以添加一些示例數據。執行後，插入查詢。
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 …’);
一旦樣本數據是準備好，我們可以開始我們的全文檢索功能。
自然語言全文搜索
嘗試我們的示例表上執行下面的SELECT查詢。
SELECT * FROM articles
WHERE MATCH (title,body) AGAINST (‘database’);
你就能看到結果如下：
在下面的資料庫比較5 MySQL與YourSQL的…
MySQL教程DBMS 1代表資料庫…
我們在上面的SQL查詢（標題，正文）反對（「資料庫」）的比賽，選擇所有的記錄，列標題和正文進行全文搜索。
您可以修改該查詢，並創建您自己的版本，以自己的資料庫中執行全文搜索。
布爾全文搜索
它可能發生，你要指定某些關鍵字在您的搜索條件。此外，您可能要忽略某些關鍵字。布爾全文搜索可以用來執行這些要求的全文檢索。
檢查下面的SELECT查詢。
SELECT * FROM articles WHERE MATCH (title,body)
AGAINST (‘+MySQL -YourSQL’ IN BOOLEAN MODE);
如果您發現上述選擇查詢，我們增加了布爾MODE反對（）。這個查詢將獲取MySQL的關鍵字，但不YourSQL關鍵字的所有記錄。請注意+和-我們以前指定的關鍵字！
在執行此功能，MySQL使用什麼有時也被稱為布爾邏輯作為暗示，其中：+代表與-代表不是[無操作員]暗示或
以下是幾個例子布爾搜索條件。
「蘋果香蕉
查找行至少包含兩個詞之一。
「+蘋果+果汁」
尋找包含兩個單詞的行。
「+蘋果Macintosh
查找行包含「蘋果」，但排名的行，如果它們也包含「麥金塔」。
「+蘋果Macintosh的」
查找行包含「蘋果」這個詞，而不是「麥金塔」。
‘+蘋果Macintosh的「
查找包含單詞「蘋果」的行，但如果該行也包含單詞「麥金塔」，速度比如果行不低。這是「軟」比「+蘋果Macintosh電腦」，為「麥金塔」的存在，導致該行不能在所有返回的搜索。
‘+蘋果+（營業額餡餅）「
行包含「蘋果」和「營業額」，或「蘋果」和「餡餅」（任何順序）的話，但排名「蘋果的營業額」比「蘋果餡餅「。
限制
支持全文檢索的MyISAM表只。MySQL 4.1中，使用多個字元設置一個單一的表內的支持。然而，在一個FULLTEXT索引的所有列，必須使用相同的字符集和校對規則。MATCH（）列列表必須匹配完全在一些列清單表的FULLTEXT索引定義，除非這場比賽（）是在布爾模式。布爾模式搜索，可以做非索引列，雖然他們很可能是緩慢的。
原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-tw/n/186302.html
微信掃一掃 
支付寶掃一掃 