Mysql full-text search SQL command syntax _ MySQL

Mysql full-text search SQL command syntax: bitsCN. commysql full-text search; SQL syntax:
MATCH (col1, col2 ,...) AGAINST (expr [in boolean mode | with query expansion])
For example:
SELECT * FROM articles where match (title, body) AGAINST ('database ');
The MATCH () function performs a natural language search in the database for a string. A database contains one or two columns in FULLTEXT. The search string is given as a parameter to AGAINST. For each row in the table, MATCH () returns a correlation value, that is, a similarity measurement between the text of the row in the specified column in the search string and MATCH () table.
The example below is more complex. Query the returned correlation values, and sort the rows in the order of weak correlation. To achieve this, you should specify MATCH () twice: one in the SELECT list and the other in the WHERE clause. This will not cause additional internal operations because the MySQL Optimizer notices that the two MATCH () calls are the same, and only one full-text search code is activated.

Mysql> SELECT id, body, MATCH
(Title, body) AGAINST
-> ('Security implications
Running MySQL as root ') AS score
-> FROM articles WHERE MATCH
(Title, body) AGAINST
-> ('Security implications
Running MySQL as root ');

Therefore, you should search mysql full text here.
Please pay attention to one problem.
Some words are ignored in full-text search:
* Any word that is too short will be ignored. The default minimum length of words that can be found in full-text search is 4 characters.
* Words in the stopword are ignored.
Mysql also comes with the query extension function. we will not discuss it too much here.
The following is an analysis of php full-text search.
One version of mysql once supported full-text chinese search (massive mysql chinese +, indicating GPL but not open source)
The key to chinese full-text search is word segmentation. mysql itself does not support cjk word segmentation (cjk: chinese, japanese, korean ),
So
!!!! ***** How to use php to simulate word segmentation is the key to mysql full-text index ****!!!!
Chinese word segmentation is the most difficult language word segmentation, and no one can solve it completely (although these search engines are doing well .)

// Fcicq: Let's take a look at the php word segmentation here.
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: Well, this is the key to php word segmentation *************
Foreach ($ arr as $ tmpStr ){
If (preg_match ("/^ [x00-x7f] + $/I", $ tmpStr) = 1)
{// Fcicq: It's all E files. it's okay. mysql can understand it.
$ ReturnVal [] = ''. $ tmpStr;
} Else {// fcicq: English-Chinese mixture...
Preg_match_all ("/([a-zA-Z] +)/I", $ tmpStr, $ matches );
If (! Empty ($ matches) {// fcicq: English part
Foreach ($ matches [0] as $ matche ){
$ ReturnVal [] = $ matche;
}
}
// Filter 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: note that the substr here is not in the manual.
// Fcicq: take a closer look, all the words are divided into two.
// For example, "database applications" will be divided into applications of the data warehouse...
// Full-text search: full-text search
// This word segmentation is naturally not good
// However, this is also done during search.
// For example, searching for a database is equivalent to searching for a data database.
// This is a traditional full-text search word segmentation method.
}
}
}
Return $ returnVal;
} // End function DV_ChineseWordSegment
// Fcicq: this is the legendary substr. I believe many people write better php code than this one.
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> 0 × 7f ){
$ ReturnVal. = $ str {$ I + 1}. $ str {$ I + 2 };
$ I + = 2;
}
$ MbCount ++;
} Elseif ($ currOrd> 0 × 7f ){
$ I + = 2;
}
$ MbStart ++;
}
Return $ returnVal;
} // End function SubString
// Insert the full-text search word segmentation TABLE. There are two in total, one topic_ft and the other 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 the so-called mysql full-text search word segmentation, mysql will not word segmentation, but php will. That's simple.
This is a relatively outdated method, but it is very practical. BitsCN.com