Introduction to mysql fuzzy search

I checked it online. There are two methods: the first is the most earthy method: Use the like statement, and the second is the full-text index. I will search it online: how to get better full-text search results in MySQL

Many Internet applications provide full-text search functions. Users can use a word or word segment as a query item to locate matching records. In the background, these programs use the LIKE statement in a SELECT query to execute this query. Although this method is feasible, it is an extremely inefficient method for full-text search, especially when processing a large amount of data.

Mysql provides a built-in full-text search solution for this problem. Here, developers only need to simply mark the fields that require full-text search, and then use special MySQL methods to run search on those fields, this not only improves the performance and efficiency (because MySQL indexes these fields to optimize the search), but also achieves higher quality search, because MySQL uses natural language to intelligently rate the results to remove irrelevant items.
This article will show you how to perform full-text search in MySQL.

Complete and fuzzy queries are available for database queries.
SELECT field FROM table WHERE a field Like Condition
SQL provides four matching modes for conditions:
1, %: represents any 0 or multiple characters. It can match any type and length of characters. In some cases, if it is Chinese, use two percent signs (%.
For example, SELECT * FROM [user] WHERE u_name LIKE '% 3%'
We will find all records with "3", such as "3 Zhang", "3 Zhang", "3 Zhang Mao", and "3 Tang sanzang.
In addition, if you need to find records with "three" and "cat" in u_name, use the and condition.
SELECT * FROM [user] WHERE u_name LIKE '% 3%' AND u_name LIKE '% cat %'
If SELECT * FROM [user] WHERE u_name LIKE '% 3% cat %' is used'
Although three-legged cats can be searched, three-legged cats cannot be searched ".
2, _: represents any single character. Matches any character. It is often used to limit the character length of an expression:
For example, SELECT * FROM [user] WHERE u_name LIKE '_ 3 _'
Only find that the u_name such as "Tang sanzang" is three characters and the middle word is "three;
For example, SELECT * FROM [user] WHERE u_name LIKE 'three __';
Find out that the name of the "three-legged cat" is three characters and the first word is "three;
3, []: represents one of the characters listed in brackets (similar to a regular expression ). Specifies a character, string, or range. The matching object must be one of them.
For example, SELECT * FROM [user] WHERE u_name LIKE '[Zhang Li Wang] 3'
We will find "Zhang San", "Li San", and "Wang San" (rather than "Zhang Li Wang San ");
For example, if [] contains a series of characters (01234, abcde, etc.), it can be slightly written as "0-4" or "a-e"
SELECT * FROM [user] WHERE u_name LIKE 'Old [1-9]'
Will find "Old 1", "old 2 ",...... , "Old 9 ";
4, [^]: represents a single character not listed in parentheses. The value is the same as [], but it requires that the matched object be any character other than the specified character.
For example, SELECT * FROM [user] WHERE u_name LIKE '[^ Zhang Li Wang] 3'
We will find "Zhao San" and "Sun San" without the surname "Zhang", "Li", and "Wang;
SELECT * FROM [user] WHERE u_name LIKE 'Old [^ 1-4] ';
From "Old 1" to "old 4", search for "old 5", "old 6 ",......
5. When the query content contains wildcards
The special characters "%", "_", and "[" cannot be properly queried due to wildcards, when special characters are included in "[]", they can be queried normally. Then, write the following functions:
Function sqlencode (str)
Str = replace (str, "[", "[]") 'must be at the beginning
Str = replace (str, "_", "[_]")
Str = replace (str, "%", "[%]")
Sqlencode = str
End function
The string to be queried can be processed by this function before query.

1. Set the basic table
Use the following SQL command to create an example table:
Mysql> create table reviews (id INT (5) primary key not null AUTO_INCREMENT, data TEXT );
The above command creates a simple music album database (mainly text in the entire segment), and then adds some records to the table:
Mysql> insert into 'Reviews '('id', 'data') VALUES
(1, 'gingerboy has a new single out called Throwing Rocks. It \'s great! ');
Mysql> insert into 'Reviews '('id', 'data') VALUES
(2, 'Hello all, I really like the new Madonna single.
One of the hottest tracks currently playing... I \ 've been listening to it all day ');
Mysql> insert into 'Reviews '('id', 'data ')
VALUES (3, 'Have you heard the new band Hotter Than Hell?
They have five members and they burn their instruments when they play in concerts.
These guys totally rock! Like, awesome, dude! ');
Verify that the data is entered correctly:
Mysql> SELECT * FROM reviews;
+ ---- + -------------------------------------------- +
Id data
+ ---- + -------------------------------------------- +
1 Gingerboy has a new single out called...

2 Hello all, I really like the new Madon...
3 Have you heard the new band Hotter...
+ ---- + -------------------------------------------- +
3 rows in set (0.00 sec)
2. Define full-text search fields
Next, define the fields to be indexed as full-text search
Mysql> alter table reviews add fulltext index (data );
Query OK, 3 rows affected (0.21 sec)
Records: 3 Duplicates: 0 Warnings: 0
Run the show indexes command to check whether the index has been added:
Mysql> show indexes from reviews;
+ --------- + --------------- + -------- + ------ + ------------ + --------- +
Table Column_name Packed Null Index_type Comment
---------- + --------------- + -------- + ------ + ------------ + --------- +
Reviews id NULL BTREE
Reviews data NULL YES FULLTEXT
+ --------- + --------------- + -------- + ------ + ------------ + --------- +
3. Run full-text search
When you have data and indexes, you can use MySQL full-text search. The simplest full-text search method is with MATCH... the SELECT query of the AGAINST statement. The following is a simple example to find records containing the word "single:
Mysql> SELECT id FROM reviews where match (data) AGAINST ('sing'); + ---- +
Id
+ ---- +
1
2
+ ---- +
2 rows in set (0.00 sec)
Here, MATCH () compares the text in the field passed to it as a parameter with the parameter passed to AGAINST (). If there is a MATCH, it is returned in the normal way. Note: You can pass more than one field and use MATCH () to view the field list. You only need to use commas to separate the field list.
When MySQL receives a full-text search request, it scores each record internally, and the non-matching record score is zero, the "more relevant" record will get a relatively higher score than the "less relevant" record. Relevance is determined by a series of MySQL differentiation standards. You can view the MySQL user manual to obtain more information.
To view the score of each record, you only need to return the MATCH () method as part of the result set, as shown below:
Mysql> SELECT id, MATCH (data) AGAINST ('rock') FROM reviews;

+ ---- + --------------------------------- +
Id MATCH (data) AGAINST ('rock ')
+ ---- + --------------------------------- +
1 0
2 0
3 1.3862514533815
+ ---- + --------------------------------- +
3 rows in set (0.00 sec)
4. Use the logical search modifier (Boolean search modifiers)
You can also use the logical search modifier for more accurate search, which is achieved by adding a special in boolean mode modifier to the AGAINST statement. IN the following example, the record containing the word "single" but not "Madonna" will be searched:
Mysql> SELECT id FROM reviews where match (data) AGAINST ('+ single-Madonna' in boolean mode );
+ ---- +
Id
+ ---- +
1
+ ---- +
1 row in set (0.00 sec)
This search feature is usually used to search for a word segment (rather than a complete word), which can be achieved through the * (asterisk) operator IN the in boolean mode statement, the following example shows how to find records with "hot" In a word:
Mysql> SELECT id FROM reviews where match (data) AGAINST ('hot * 'in boolean mode); + ---- +
Id
+ ---- +
3
2
+ ---- +
2 rows in set (0.00 sec)
You can also use this method to find at least one parameter passed to AGAINST. The following example finds a record that contains at least one of the words "hell" and "rocks:
Mysql> SELECT id FROM reviews where match (data) AGAINST ('hell rocks' in boolean mode );
+ ---- +
Id
+ ---- +
1
3
+ ---- +
3 rows in set (0.00 sec)
The above examples demonstrate the comparison with the traditional SELECT... LIKE statement, a more effective method for full-text search, when you need to write the MySQL database search interface next time, you can try this method. The reason is that only the MyISAM engine supports full-text indexing and is dizzy. Okay, you still need to use like to search...