MySQL Full-text search: The Writing of SQL

First, everyone to download a copy of the dvbbs.php beta1 code, unpack the PHP code First, find out your MySQL manual, if there is no manual then directly to see the following example operation!

MySQL full-text search, sql writing:

MATCH (Col1,col2,...) Against (expr [in BOOLEAN MODE with QUERY expansion])

Like what:

SELECT * from articles WHERE MATCH (title,body) against (' database ');

The MATCH () function performs a natural language search within a database for a string. A database is 1 sets of 1 or 2 columns contained within Fulltext. The search string is given as a parameter to the against (). For each row in the table, MATCH () returns a correlation value, that is, a similarity metric between the search string and the line literal in the MATCH () table.

The following example is more complex. Ask to return related values and sort the rows in the order in which the dependencies are weaker. To achieve this result, you should specify MATCH () two times: once in the SELECT list and another in the WHERE clause. This does not cause additional housekeeping because the MySQL optimizer notices that two match () calls are the same, enabling only one Full-text search code to be activated.

The following are the referenced contents: mysql> SELECT ID, body, MATCH (Title,body) Against -> (' Security Implications of Running MySQL as Root ') as score -> from articles WHERE MATCH (Title,body) Against -> (' Security Implications of Running MySQL as root ");

So, here you should be MySQL English full-text search.

Please note a question.

Some words are ignored in Full-text search:

* Any word that is too short will be ignored. The default minimum length of a word that a full-text search can find is 4 characters.

* The words in the stop Word will be ignored.

MySQL also has its own query extensions. There is not much discussion here.

The following is an analysis of Chinese full-text search in PHP

There used to be a version of MySQL to support Chinese full-text search (mass MySQL chinese+, said the GPL but eventually no open source)

The key of Chinese full-text search is in participle. mysql itself does not support CJK participle (Cjk:chinese,japanese,korean),

So

!!!! How to use PHP to simulate participle is the key to MySQL full-text indexing * * * *!!!!

Chinese participle is the most difficult word in the language. No one has been able to solve the problem completely (though the search engines are doing all right.)

The following are the referenced contents: FCICQ: Here is a look at the PHP word here how to do. function &dv_chinesewordsegment ($str, $encodingName = ' GBK ') { static $OBJENC = null; if ($OBJENC = = null) { if (!class_exists (' dv_encoding ')) { Require_once root_path. ' inc/dv_encoding.class.php '; } $OBJENC =& dv_encoding::getencoding ($encodingName); } $strLen = $objEnc->strlength ($STR); $returnVal = Array (); if ($strLen < = 1) { return $str; } $arrStopWords =& dv_getstopwordlist (); Print_r ($arrStopWords); Filter all HTML tags $str = Preg_replace (' #<[a-za-z]+?. *?> #is ', ', $str); Filter all Stopword $str = Str_replace ($arrStopWords [' Strrepl '], ', $str '); $str = preg_replace ($arrStopWords [' Pregrepl '], ', $str '); echo "$STR: {$str} “; $arr = Explode (", $str); Fcicq: OK, here is the key to the PHP word ************* foreach ($arr as $tmpStr) { if (Preg_match ("/^[x00-x7f]+$/i", $tmpStr) = = 1) {//FCICQ: All E-wen, it doesn't matter, MySQL can know $returnVal [] = '. $tmpStr; else{//fcicq: Sino-British mixed ... Preg_match_all ("/([a-za-z]+)/I", $tmpStr, $matches); if (!empty ($matches)) {//FCICQ: English part foreach ($matches [0] as $matche) { $returnVal [] = $matche; } } Filtering ASCII characters $TMPSTR = Preg_replace ("/([x00-x7f]+)/I", " , $TMPSTR); FCICQ: You see, the rest is not all Chinese? $strLen = $objEnc->strlength ($tmpStr)-1; for ($i = 0; $i < $strLen; $i + +) { $returnVal [] = $objEnc->substring ($tmpStr, $i, 2) ; FCICQ: Notice the substr here, not in the manual. FCICQ: You look carefully, all the words are divided into two. For example, "database Application", will be divided into the data library of the application ... Full-Text Search: Full text Search This participle of nature is not what kind of But the same is true when searching. For example, searching the database is equivalent to searching the data base. This is a fairly traditional method for Full-text search. } } } return $returnVal; }//end function Dv_chinesewordsegment FCICQ: This is the legendary substr. I believe many people write PHP code that is better than this. Function &substring (& $str, $start, $length =null) { if (!is_numeric ($start)) { return false; } $strLen = StrLen ($STR); if ($strLen < = 0) { return false; } if ($start < 0 $length < 0) { $mbStrLen = $this->strlength ($STR); } else{ $mbStrLen = $strLen; } if (!is_numeric ($length)) { $length = $mbStrLen; } elseif ($length < 0) { $length = $mbStrLen + $length-1; } if ($start < 0) { $start = $mbStrLen + $start; } $returnVal = '; $mbStart = 0; $mbCount = 0; for ($i = 0; $i < $strLen; $i + +) { if ($mbCount >= $length) { Break } $currOrd = Ord ($str {$i}); if ($mbStart >= $start) { $returnVal. = $str {$i}; if ($currOrd > 0x7f) { $returnVal. = $str {$i +1}. $str {$i +2}; $i + 2; } $mbCount + +; } elseif ($currOrd > 0x7f) { $i + 2; } $mbStart + +; } return $returnVal; }//end function SubString Inserts a full-text search word list. Altogether two, a topic_ft, a bbs_ft $arrTopicIndex =& dv_chinesewordsegment ($topic); if (!empty ($arrTopicIndex) && Is_array ($arrTopicIndex)) { $topicindex = $db->escape_string (Implode (', $arrTopicIndex)); if ($topicindex!== ") { $db->query ("UPD ATE {$dv}topic_ft SET topicindex= ') {$topicindex} ' WHERE topicid= ' {$RootID} '); } else{ $db->query ("DEL ete from {$DV}topic_ft WHERE topicid= ' {$RootID} '); } } }

This is called the MySQL full-text search word, MySQL will not participle, and PHP will. It's as simple as that.

Although this is a more outdated method, but very practical.