Mysql full-text search SQL command writing

Source: Internet
Author: User
Tags mysql manual
First, download a copy of dvbbs. phpbeta1 code, decompress the package, and put aside the php code to find out your mysql manual. If there is no manual, you can directly view the following instance operations!

First, download a dvbbs. php beta1 code. decompress the code and leave the php code aside to find out your mysql manual. If there is no manual, check the following instance operations!

Mysql full-text search:
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.
The Code is as follows:
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 .)
The Code is as follows:
// 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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.