Introduction to MySQL Fuzzy search method _mysql

Online check, there are two methods, the first one of the most Earth method: Use like sentence the second to listen to Brother Tao said using Full-text index, online search: How to get better full-text search results in MySQL

Many Internet applications provide full-text search capabilities, and users can use a word or phrase as a query item to locate matching records. In the background, these programs use the LIKE statement in a select query to execute such a query, although this method is feasible, but for Full-text lookup, this is an extremely inefficient approach, especially when dealing with large amounts of data.

MySQL provides a solution for this problem based on the built-in Full-text lookup.　　Here, developers simply mark out fields that require Full-text lookup, and then run the search in those fields using a special MySQL method, which not only improves performance and efficiency (because MySQL indexes these fields to optimize the search), but also achieves a higher quality search because MySQL uses natural Language to intelligently rank results to get rid of irrelevant items.
This article will tell you how to do full-text search in MySQL.

In the database query, there is a complete query and fuzzy query points.
SELECT field from table WHERE a field like condition
With regard to conditions, SQL provides four matching modes:
1,%: Represents any 0 or more characters. Can match any type and length of characters, in some cases in Chinese, please use two percent sign (%).
For example, SELECT * from [user] WHERE u_name like '% III '
will be u_name as "John", "Zhang Cat Three", "three-legged Cat", "Tang Sanzang" and so on Have "three" records all find out.
Also, if you need to find a record in u_name that has "three" and "cat", use the and condition
SELECT * FROM [user] WHERE u_name like '% III ' and u_name like '% Cat '
If you use SELECT * from [user] WHERE u_name like '% cat% '
Although can search out "three-legged cat", but can not search out the conditions of the "cat three".
2,_: Represents any single character. Matches a single arbitrary character, which is used to restrict the character-length statements of an expression:
For example, SELECT * from [user] WHERE u_name like ' _ Three _ '
Only find "Tang Sanzang" so U_name is three words and the middle one word is "three";
Another example is SELECT * from [user] WHERE u_name like ' three __ ';
Only find "three-legged cat" so name is three words and the first word is "three";
3,[]: Represents one of the characters listed in parentheses (similar to a regular expression). Specifies a character, string, or range, which requires that the matched objects be any of them.
For example, SELECT * from [user] WHERE u_name like ' [Zhang Li Wang] three '
Will find "John", "Lie Triple Systems", "Wang San" (rather than "Zhangli Kang");
If [] There are a series of characters (01234, ABCDE, etc.) can be slightly written as "0-4", "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 within the parentheses. The value and [] are the same, 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] three '
Will find not the surname "Zhang", "Li", "Wang" "Zhao Three", "Sun San" and so on;
SELECT * FROM [user] WHERE u_name like ' old [^1-4] ';
Will exclude "old 1" to "Old 4", Looking for "old 5", "Old 6" 、......
5, the query content contains the characters
Because of the wildcard character, we query special characters "%", "_", "[" statements can not be implemented normally, and special characters in "[]" around the normal query. Thus we write the following function:
function Sqlencode (str)
Str=replace (str, "[", "[[]") ' This sentence must be at the front
Str=replace (str, "_", "[_]")
Str=replace (str, "%", "[%]")
Sqlencode=str
End Function
The query string is processed first by this function before querying.

1. Set the basic form
Starting with the example table, use 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 music album database (mainly the entire paragraph text), 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 The instruments when They the play in concerts.
These guys totally rock! Like, awesome, dude! ');
Verify the 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 index 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
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 the data and index, you can use the MySQL Full-text search, the simplest Full-text search method is with match ... Against a select query for a statement, the following is a simple example to find records that contain 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 as a parameter to the argument passed to against (), and if there is a match, returns in the normal way. Note You can pass more than one field to view with match ()-Just separate the field list with commas.
When MySQL receives a request for a full-text search, it scores each record internally, with unmatched records scoring zero, and more relevant records getting a higher score than "less relevant" records. Dependencies are determined by a series of MySQL criteria, and you can get more information by looking at MySQL's user manual.
To see how each record is scored, just return to the match () method as part of the result set, as follows:
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 modifiers)
You can also use the logical search modifier for more precise searches, which are implemented by adding a special in BOOLEAN mode modifier in the against statement, in the following example, to find records that contain the word "single" but do not have "Madonna":
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 typically used to search for word fragments (rather than full words), which can be achieved by the * (asterisk) operator in the in BOOLEAN mode statement, and the following example shows how to find a record in a word that contains "hot":
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, and the following example looks for records that contain 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 relative to the traditional select ... Like statement, a more efficient way to do full-text search, you can try this method the next time you need to write a MySQL database search interface. The final discovery is not used because only the MyISAM engine supports Full-text indexing, halo. Well, search with like ...