Introduction to mysql fuzzy search methods
- 2020-05-13 03:41:29
- OfStack
There are two methods: the first is the most soil method: using like statement, the second is using full text index, on the Internet search 1: how to get better full text search results in MySQL
Many Internet applications offer full-text search, where users can use a single word or a fragment of a word as a query item to locate a matching record. Behind the scenes, these programs use LIKE statements in an SELECT query to execute this query, and while this is possible, it is an extremely inefficient method for full-text lookup, especially when dealing with large amounts of data.
mysql provides a built-in full-text lookup solution to this problem. Here, developers simply marked need full-text search field, and then use special MySQL method in the search field operation, this not only improves the performance and efficiency to these fields (because MySQL index to optimize the search), and achieve a higher quality of the search, because MySQL using natural language to intelligently rating, and the results to get rid of the project.
This article will show you how to do a full-text search in MySQL.
In the database query, there are complete query and fuzzy query.
SELECT field FROM table WHERE certain field Like condition
In terms of conditions, SQL provides four matching patterns:
1, % : represents any 0 or more characters. Can match any type and length of the character, in some cases if Chinese, please use two percent sign (%%).
For example, SELECT * FROM [user] WHERE u_name LIKE '%3%'
Records with "3" will be found for "u_name", "cat 3", "cat 3", "tang 3 hide", etc.
Also, if you need to find records with both "3" and "cat" in u_name, use the and condition
SELECT * FROM [user] WHERE u_LIKE '%3%' AND u_name LIKE '% cat %'
If you use SELECT * FROM [user] WHERE u_name LIKE '%3% cat %'
Although it can search for "three-legged cat", it cannot search for "zhang cat 3".
2, _ : represents any single character. Matches a single arbitrary character, which is often used to limit the character length of an expression:
For example, SELECT * FROM [user] WHERE u_name LIKE '_3_'
Only find out "tang 3 Tibet" such that u_name is 3 words and the middle one is "3";
SELECT * FROM [user] WHERE u_name LIKE '3__';
Find only "three-legged cat", where name is 3 words and the first word is "3";
3, [] : represents one of the characters listed in parentheses (similar to a regular expression). Specify a character, string, or range that matches any of them.
For example, SELECT * FROM [user] WHERE u_name LIKE '[zhang li wang]3'
Will find "zhang 3", "li 3", "wang 3" (instead of "zhang li wang 3");
If [] contains 1 series of characters (01234, abcde, etc.), it can be abbreviated as "0-4" and "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 that is not listed in parentheses. It has the same value as [], but it requires any character other than the specified character to be matched.
For example, SELECT * FROM [user] WHERE u_name LIKE '[^ zhang li]3'
Will find out "zhang", "li", "wang" "zhao 3", "sun 3" and so on;
SELECT * FROM [user] WHERE u_name LIKE 'old [^1-4]';
Exclude "old 1" to "old 4" and look for "old 5", "old 6",...
5. When the query content contains wildcards
Because of the wildcard character, the query of the special characters "%", "_" and "[" cannot be normally implemented, but the special characters can be quashed by" [] ". Therefore, the following functions can be written:
function sqlencode(str)
str=replace(str,"[","[[]")) '
str=replace(str,"_","[_]")
str=replace(str,"%","[%]")
sqlencode=str
end function
The pending string is processed by this function before the query.
1. Set the basic table
Start by creating the example table using the following SQL command:
mysql > CREATE TABLE reviews (id INT(5) PRIMARY KEY NOT NULL AUTO_INCREMENT, data TEXT);
The above command creates a simple database of music albums (mostly whole text), and then adds a few notes 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 correct data entry:
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 Than...
+----+--------------------------------------------+
3 rows in set (0.00 sec)
2. Define full-text search fields
Next, define the fields that you want to use as a full-text search index
mysql > ALTER TABLE reviews ADD FULLTEXT INDEX (data);
Query OK, 3 rows affected (0.21 sec)
Records: 3 Duplicates: 0 Warnings: 0
Use the SHOW INDEXES command to check that 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's full-text search. The easiest way to do full-text search is with MATCH... The SELECT query for the AGAINST statement, here is a simple example to find a record containing the word "single" :
mysql > SELECT id FROM reviews WHERE MATCH (data) AGAINST ('single');+----+
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(), and if there is a match, returns it the normal way. Note that you can pass more than one field to view with MATCH() - simply use a comma to separate the list of fields.
When MySQL receives a request for a full-text search, it internally scores each record, with a score of zero for the mismatched record and a relatively higher score for the "more relevant" record than for the "less relevant" record. Relevance is determined by the 1 series differentiation criteria of MySQL, which can be found in the MySQL user manual.
To see how each record is rated, simply return the MATCH() method as part 1 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 to perform a more precise search by adding a special IN BOOLEAN MODE modifier to the AGAINST statement. In the following example, a record containing the word "single" but not "Madonna" will be looked for:
mysql > SELECT id FROM reviews WHERE MATCH (data) AGAINST ('+single -madonna' IN BOOLEAN MODE);
+----+
id
+----+
1
+----+
1 row in set (0.00 sec)
This 1 search feature is usually used to search for word fragments (rather than full words), and this can be done by the * (asterisk) operator in the IN BOOLEAN MODE statement. The following example shows how to find records containing "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 looks for records containing 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)
These examples above illustrate the contrast between the traditional SELECT... LIKE statement, a more efficient way to do full-text search, which you can try the next time you need to write the MySQL database search interface. It turned out that it didn't work because only the MyISAM engine supports full-text indexing, halo. Well, let's search with like...
Many Internet applications offer full-text search, where users can use a single word or a fragment of a word as a query item to locate a matching record. Behind the scenes, these programs use LIKE statements in an SELECT query to execute this query, and while this is possible, it is an extremely inefficient method for full-text lookup, especially when dealing with large amounts of data.
mysql provides a built-in full-text lookup solution to this problem. Here, developers simply marked need full-text search field, and then use special MySQL method in the search field operation, this not only improves the performance and efficiency to these fields (because MySQL index to optimize the search), and achieve a higher quality of the search, because MySQL using natural language to intelligently rating, and the results to get rid of the project.
This article will show you how to do a full-text search in MySQL.
In the database query, there are complete query and fuzzy query.
SELECT field FROM table WHERE certain field Like condition
In terms of conditions, SQL provides four matching patterns:
1, % : represents any 0 or more characters. Can match any type and length of the character, in some cases if Chinese, please use two percent sign (%%).
For example, SELECT * FROM [user] WHERE u_name LIKE '%3%'
Records with "3" will be found for "u_name", "cat 3", "cat 3", "tang 3 hide", etc.
Also, if you need to find records with both "3" and "cat" in u_name, use the and condition
SELECT * FROM [user] WHERE u_LIKE '%3%' AND u_name LIKE '% cat %'
If you use SELECT * FROM [user] WHERE u_name LIKE '%3% cat %'
Although it can search for "three-legged cat", it cannot search for "zhang cat 3".
2, _ : represents any single character. Matches a single arbitrary character, which is often used to limit the character length of an expression:
For example, SELECT * FROM [user] WHERE u_name LIKE '_3_'
Only find out "tang 3 Tibet" such that u_name is 3 words and the middle one is "3";
SELECT * FROM [user] WHERE u_name LIKE '3__';
Find only "three-legged cat", where name is 3 words and the first word is "3";
3, [] : represents one of the characters listed in parentheses (similar to a regular expression). Specify a character, string, or range that matches any of them.
For example, SELECT * FROM [user] WHERE u_name LIKE '[zhang li wang]3'
Will find "zhang 3", "li 3", "wang 3" (instead of "zhang li wang 3");
If [] contains 1 series of characters (01234, abcde, etc.), it can be abbreviated as "0-4" and "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 that is not listed in parentheses. It has the same value as [], but it requires any character other than the specified character to be matched.
For example, SELECT * FROM [user] WHERE u_name LIKE '[^ zhang li]3'
Will find out "zhang", "li", "wang" "zhao 3", "sun 3" and so on;
SELECT * FROM [user] WHERE u_name LIKE 'old [^1-4]';
Exclude "old 1" to "old 4" and look for "old 5", "old 6",...
5. When the query content contains wildcards
Because of the wildcard character, the query of the special characters "%", "_" and "[" cannot be normally implemented, but the special characters can be quashed by" [] ". Therefore, the following functions can be written:
function sqlencode(str)
str=replace(str,"[","[[]")) '
str=replace(str,"_","[_]")
str=replace(str,"%","[%]")
sqlencode=str
end function
The pending string is processed by this function before the query.
1. Set the basic table
Start by creating the example table using the following SQL command:
mysql > CREATE TABLE reviews (id INT(5) PRIMARY KEY NOT NULL AUTO_INCREMENT, data TEXT);
The above command creates a simple database of music albums (mostly whole text), and then adds a few notes 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 correct data entry:
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 Than...
+----+--------------------------------------------+
3 rows in set (0.00 sec)
2. Define full-text search fields
Next, define the fields that you want to use as a full-text search index
mysql > ALTER TABLE reviews ADD FULLTEXT INDEX (data);
Query OK, 3 rows affected (0.21 sec)
Records: 3 Duplicates: 0 Warnings: 0
Use the SHOW INDEXES command to check that 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's full-text search. The easiest way to do full-text search is with MATCH... The SELECT query for the AGAINST statement, here is a simple example to find a record containing the word "single" :
mysql > SELECT id FROM reviews WHERE MATCH (data) AGAINST ('single');+----+
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(), and if there is a match, returns it the normal way. Note that you can pass more than one field to view with MATCH() - simply use a comma to separate the list of fields.
When MySQL receives a request for a full-text search, it internally scores each record, with a score of zero for the mismatched record and a relatively higher score for the "more relevant" record than for the "less relevant" record. Relevance is determined by the 1 series differentiation criteria of MySQL, which can be found in the MySQL user manual.
To see how each record is rated, simply return the MATCH() method as part 1 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 to perform a more precise search by adding a special IN BOOLEAN MODE modifier to the AGAINST statement. In the following example, a record containing the word "single" but not "Madonna" will be looked for:
mysql > SELECT id FROM reviews WHERE MATCH (data) AGAINST ('+single -madonna' IN BOOLEAN MODE);
+----+
id
+----+
1
+----+
1 row in set (0.00 sec)
This 1 search feature is usually used to search for word fragments (rather than full words), and this can be done by the * (asterisk) operator in the IN BOOLEAN MODE statement. The following example shows how to find records containing "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 looks for records containing 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)
These examples above illustrate the contrast between the traditional SELECT... LIKE statement, a more efficient way to do full-text search, which you can try the next time you need to write the MySQL database search interface. It turned out that it didn't work because only the MyISAM engine supports full-text indexing, halo. Well, let's search with like...