MySQL Fuzzy query

The first of the most Earth-like methods: Using the like statement for the second full-text index

There are two methods, the first one is the most soil method: Using the like statement the second kind of hearing Tao said with full-text index, search online: How to get better full-text search results in MySQL

MySQL provides a solution based on the built-in full-text lookup method for this issue.  Here, developers simply mark fields that require full-text lookups, and then run a search in those fields using a special MySQL method, which not only improves performance and efficiency (because MySQL indexes these fields to refine the search), but also achieves higher-quality searches because MySQL uses natural  Language to intelligently rate results to remove irrelevant items. This article will tell you how to do a 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   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 (%).   such as SELECT * from [user] WHERE the u_name like '% of three '   will be u_name for "Zhang San", "Zhang three", "three-legged Cat", "Tang Sanzang" and so on Have "three" records all find out.   Also, if you need to find records in U_name that have both "three" and "cat", use the AND condition   SELECT * FROM [user] WHERE u_name like '% three ' and u_name like '% cat% ' &nbs P If you use SELECT * from [user] WHERE u_name like '% of cat% '   Although can search out "three-legged cat", but can not search out the eligible "Zhang Cat three."   2,_: Represents any single character. Matches a single arbitrary character, it is commonly used to limit the expression of the character length of the statement:  such as SELECT * from [user] WHERE u_name like ' _ three _ '   only find "Tang Sanzang" such u_name for three words and the middle one word is "three"; nbsp For example, select * from [user] WHERE u_name like ' three __ ';  only find "three-legged cat" so name is three characters 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 that requires matching objects to be any of them.   such as SELECT * from [user] WHERE u_name like ' [Zhang Li Wang] Three '   will find "Zhang San", "Lie Triple", "Wang San" (instead of "Zhangli Kang");  such as [] there are a series of characters (01234, AB CDE and the like) can be slightly written as "0-4", "A-E"   SELECT * from [user] WHERE u_name as ' old [1-9] '   will find "Old 1", "Old 2", "", "Old 9"; &nbsP 4,[^]: Represents a single character that is 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.   such as SELECT * from [user] WHERE u_name like ' [^ Zhang Li Wang] Three '   will find the surname "Zhang", "Li", "Wang", "Zhao three", "Magozo" and other;  select * from [user] W Here u_name like ' old [^1-4] '; 

Wk_ad_begin ({pid:21}); Wk_ad_after (, function () {$ ('. Ad-hidden '). Hide ();}, function () {$ ('. Ad-hidden '). Show ();});  

Will exclude "old 1" to "Old 4", Looking for "old 5", "Old 6", "" "  5, query content contains wildcard characters   because of the wildcard character, resulting in our query special characters"% "," _ "," ["the statement can not be normal implementation, and special characters with" [] "in the normal query. Accordingly we write the following functions:  function Sqlencode (str)   str=replace (str, "[", "[[]") ' This sentence must be at the top   str=replace (str, "_", "[_] ")   Str=replace (str,"% "," [%] ")   sqlencode=str  end function  before the query, the unknown origin string is processed first by the function.    1, set the basic table   start with the Create example table, use the following SQL command:  mysql> CREATE TABLE reviews (ID INT (5) PRIMARY KEY not NULL AUT O_increment, Data TEXT);  the above command to create a simple music album library (mainly the whole paragraph of text), and then add some records to the table:  mysql> INSERT into ' reviews ' (' Id ', ' data ') values  (1, ' Gingerboy have 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.&N Bsp One of the hottest tracks currently playing ... I\ ' ve been listening to it all day ');  mysql> INSERT into ' reviews ' (' id ', ' data ')   VALUES (3, ' has you hear d The new band Hotter Than Hell?&nbsP They five members and they burn their instruments when they play in concerts.  these guys totally rock! Like, awesome, dude! ');   Verify the correct entry of data:  mysql> SELECT * from reviews;  +----+--------------------------------------------+   ID data  +----+--------------------------------------------+  1 Gingerboy have a new single out called. .   2 Hello All, I really like the new Madon ...  3 has you heard the new band hotter than...  +----+ --------------------------------------------+  3 rows in Set (0.00 sec)   2, defining full-Text search fields   Next, Define the fields you want to index as full-text search   mysql> ALTER TABLE Reviews ADD fulltext Index (data);  Query OK, 3 rows affected (0.21 sec) & nbsp 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 index, you can use MySQL full-text search, the simplest full-text search method is with match ... For a select query of the against statement, here is a simple example that can be used to find a record containing the word "single":  mysql> the Select id from reviews WHERE MATCH (data) against (' Single '): +----+  id  +----+  1  2  +----+  2 rows in Set (0.00 sec)   Here, MATCH () is passed as a parameter to the The text in its field is compared to the parameters passed to against (), and if there is a match, it is returned in the normal way. Note You can pass more than one field to view with match ()-just use a comma to split the field list.   When MySQL receives a request for a full-text search, it scores each record internally, the unmatched record score is zero, and the "more relevant" record gets a higher score than the "less relevant" record. Dependencies are determined by a series of criteria for MySQL, and viewing the MySQL user manual gives you more information.   Want to see how each record is scored, just 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, using logical search modifiers (Boolean search modifiers)   You can also use the logical search modifier for a more precise search This is done by adding a special in BOOLEAN mode modifier to the against statement, and in the following example, a record containing the word "single" but no "Madonna" is found:  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 fragments of words (rather than complete words), which can be achieved by the * (asterisk) operator in the in BOOLEAN mode statement, and the following example shows how to find a word that contains "hot" Record: 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 FRO  M Reviews WHERE MATCH (data) against (' Hell Rocks ' in BOOLEAN MODE); +----+ ID +----+ 1 3 +----+ 3 rows in Set (0.00 sec) These 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 cannot be used because only the MyISAM engine supports full-text indexing, Halo.  OK, or search with like ... For more information, refer to: http://www.jb51.net/article/31904.htm

MySQL Fuzzy query