MYSQL fuzzy Search Overview

MYSQL fuzzy search can be divided into full query and fuzzy query during database query. The general fuzzy statement is as follows: www.2cto.com SELECT field FROM table WHERE a field Like condition. For the condition, SQL provides four matching modes: 1, %: indicates 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, if SELECT * FROM [user] WHERE u_name LIKE '% 3%', u_name is set to "Zhang San", "Zhang Mao San", and "three-legged cats ", "Tang sanzang" and so on have all records of "3. 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 you can search for "three-legged cats ", however, you cannot find the qualified "Zhang maosan ". 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 finds "Tang sanzang". In this way, u_name is three characters and the middle word is "three; for example, SELECT * FROM [user] WHERE u_name LIKE 'three _ '. Only the three-legged cat is found. The 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. The matching object must be one of them. For example, SELECT * FROM [user] WHERE u_name LIKE '[Zhang Li Wang] san' will find "Zhang San", "Li San", and "Wang San" (rather than "Zhang Li Wang San ");
For example, [] contains a series of characters (01234, abcde, and so on) it can be slightly written as "0-4", "a-e" SELECT * FROM [user] WHERE u_name LIKE 'Old [1-9] '. 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] san' will find "Zhao San" without the surname "Zhang", "Li", and "Wang ", sun San and others; SELECT * FROM [user] WHERE u_name LIKE 'Old [^ 1-4] '; will exclude "Old 1" to "old 4 ", search for "old 5", "old 6 ",...... 5. When the query content contains wildcards, the special characters "%", "_", and "[" cannot be queried, when special characters are included in "[]", they can be queried normally. Based on this, we write the following function: function sqlencode (str) str = replace (str, "[", "[]") ', which must be at the beginning of str = replace (str, "_", "[_]") str = replace (str, "%", "[%]") sqlencode = strend function: process the string to be queried by this function before query.