mysql boolean text search tutorial

MySQL supports another form of full-text search, called boolean mode. As a Boolean, you can provide details about:

Words to match;

1.Words to exclude (if a row contains this word, it is not returned row, even if it contains other specified words);

2. Arrangement hints (specify certain words as more important than others, with more important words ranking higher);

3 .Expression grouping;

4.Some other content.

Can be used even without a FULLTEXT index The Boolean approach differs from the full-text search syntax used so far in that it can be used even if a FULLTEXT index is not defined. But this is a very slow operation (its performance will decrease as the amount of data increases).

To demonstrate the function of IN BOOLEAN MODE, give a simple example:

Input:

select note_text from productnotes where match(note_text) against('anvils' in boolean mode);

Output:

Analysis: This full-text search retrieves all lines (there are two lines) that contain the word heavy. The keyword IN BOOLEAN MODE is used, but no Boolean operator is actually specified, so the result is the same as if no Boolean mode was specified.

DIFFERENCE IN BEHAVIOR WITH IN BOOLEAN MODE Although the results of this example are the same as without IN BOOLEAN MODE, there is an important difference in behavior (even though it is not shown in this particular example).

To match lines that contain heavy but do not contain any words starting with rope, use the following query:

Input:

select note_text from productnotes where match(note_text) against('heavy -rope' in boolean mode);

Output:

Analysis: Only one row is returned this time. This time the word heavy is still matched, but -rope* explicitly instructs MySQL to exclude rows containing rope* (any word starting with rope , including ropes ), which is why the first row in the previous example was excluded.

Code changes required in MySQL 4.x If you are using MySQL 4.x, the above example may not return any rows. This is a bug in the handling of the * operator. To use this example with MySQL 4.x, use -ropes instead of -rope* (to exclude ropes rather than exclude any word starting with rope ).

We've seen two full-text search Boolean operators - and * , - exclude a word, while * is the truncation operator (think of it as a wildcard for the end of a word). The following table lists all supported Boolean operators.

Here are a few examples to illustrate how certain operators are used:

Input:

select note_text from productnotes where match(note_text) against('+rabbit +bait' in boolean mode);

Analysis: This search match contains Row with words rabbit and bait.

Input:

select note_text from productnotes where match(note_text) agains('rabbit bait' in boolean mode);

Analysis: Without an operator specified, this search matches lines that contain at least one of the words rabbit and bait.

Input:

select note_text from productnotes where match(note_text) agains('"rabbit bait"' in boolean mode);

Analysis: This search matches the phrase rabbit bait instead of matching the two words rabbit and bait.

Input:

select note_text from productnotes where match(note_text) agains(&#39;>rabbit <bcarrot&#39; in boolean mode);

Analysis: Match rabbit and carrot, increase the level of the former and reduce the level of the latter.

Input:

select note_text from productnotes where match(note_text) agains(&#39;+safe +(<combination)&#39; in boolean mode);

Analysis: This search matches the words safe and combination, lowering the rank of the latter.

Arrange without sorting In Boolean mode, do not sort the returned rows in descending order by rank value.

The above is the detailed content of mysql boolean text search tutorial. For more information, please follow other related articles on the PHP Chinese website!