Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve full-text search operation in MySQL, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Full-text search is done through the MATCH () function.

> CREATE TABLE articles (

-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY

-> title VARCHAR

-> body TEXT

-> FULLTEXT (title,body)

->)

Query OK, 0 rows affected (0.00 sec)

Mysql > INSERT INTO articles VALUES

-> (NULL,MySQL Tutorial, DBMS stands for DataBase...)

-> (NULL,How To Use MySQL Efficiently, After you went through a...)

> (NULL,Optimising MySQL,In this tutorial we will show...)

(NULL,1001 MySQL Tricks,1. Never run mysqld as root. 2.)

(NULL,MySQL vs. YourSQL, In the following database comparison...)

-> (NULL,MySQL Security, When configured properly, MySQL...)

Query OK, 6 rows affected (0.00 sec)

Records: 6 Duplicates: 0 Warnings: 0

Mysql > SELECT * FROM articles

-> WHERE MATCH (title,body) AGAINST (database)

+-+-+

| | id | title | body | |

+-+-+

| | 5 | MySQL vs. YourSQL | In the following database comparison. | |

| | 1 | MySQL Tutorial | DBMS stands for DataBase. | |

+-+-+

The 2 rows in set (0.00 sec) function MATCH () performs a natural language search for a string against a text set (the column set of one or more columns contained in an FULLTEXT index). The search string is given as an argument to AGAINST (). The search is performed by ignoring the case of letters. MATCH () returns a correlation value for each record row in the table. That is, the measure of similarity between the search string and the text of the column specified by the record row in the MATCH () list.

When MATCH () is used in a WHERE clause (see the example above), the returned rows of records are automatically sorted in the order of correlation from highest to lowest. The correlation value is a non-negative floating-point number. Zero correlation means dissimilarity. The calculation of correlation is based on the number of words in the record line, the number of unique words in the line, the total number of words in the set, and the number of documents containing a particular word (record line).

It can also perform a logical pattern search. This is described in the following chapters.

The previous example is some basic instructions on the use of the function MATCH (). Record rows are returned in descending order of similarity.

The next example shows how to retrieve an explicit similarity value. If there is neither WHERE nor ORDER BY clause, the returned rows are not sorted.

Mysql > SELECT id,MATCH (title,body) AGAINST (Tutorial) FROM articles

+-+-

| | id | MATCH (title,body) AGAINST (Tutorial) |

+-+-

| | 1 | 0.64840710366884 |

| | 2 | 0 |

| | 3 | 0.66266459031789 |

| | 4 | 0 |

| | 5 | 0 |

| | 6 | 0 |

+-+-

6 rows in set (0. 00 sec) the following example is a little more complex. The query returns similarity and still returns record rows in descending order. To accomplish this, you should specify MATCH () twice. This does not incur additional overhead because the MySQL optimizer notices the same MATCH () calls twice and invokes the full-text search code only once.

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)

+-+-- +-+

| | id | body | score | |

+-+-- +-+

| 4 | 1. Never run mysqld as root. 2. | 1.5055546709332 |

| | 6 | When configured properly, MySQL... | 1.31140957288 |

+-+-- +-+

2 rows in set (0. 00 sec) MySQL uses a very simple parser to separate text into words. A "word" is any sequence of characters consisting of text, data, "" and "_". Any "word" that appears on the stopword list, or that is too short (3 characters or less) will be ignored.

Each appropriate word in the set and query is measured by its importance in the set and query. In this way, a word that appears in multiple documents will have a lower weight (maybe even a zero weight) because it has a lower semantic value in this particular set. Otherwise, if the word is less, it will get a higher weight. The weight of the word is then combined to calculate the similarity of the record rows.

Such a technical job works well with a large set (in fact, it will be carefully tuned to it). For very small tables, word classification is not enough to fully reflect their semantic values, and sometimes this pattern can produce strange results.

Mysql > SELECT * FROM articles WHERE MATCH (title,body) AGAINST (MySQL)

Empty set (0. 00 sec) in the above example, the search term MySQL did not get any results, because the word appears in more than half of the record lines. Similarly, it is effectively treated as a stopword (that is, a word with zero semantic value). This is the ideal behavior-- a natural language query should not return every second row from a 1GB table.

Words that match half of the rows recorded in the table are rarely likely to find the relevant document. In fact, it may find a lot of irrelevant documents. We all know that this often happens when we try to search for something on the Internet through search engines. For this reason, such a row is set to a low semantic value in this particular dataset.

By 4.0.1, MySQL can also use the IN BOOLEAN MODE modifier to perform a logical full-text search.

Mysql > SELECT * FROM articles WHERE MATCH (title,body)

-> AGAINST (+ MySQL-YourSQL IN BOOLEAN MODE)

+-- +

| | id | title | body | |

+-- +

The above content is how to implement full-text search operation in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.