MySQL Advanced (6) Introduction to Four Usage of Fuzzy Query

Introduction to the four usages of fuzzy query in mysql

This article mainly introduces the four usages of fuzzy query in MySQL. Friends in need can refer to it.

The following introduces four uses of fuzzy query in mysql:

1 %:

represents any 0 or more characters. Can match characters of any type and length. In some cases, if it is Chinese, please use two percent signs (%%) to express it.

For example, SELECT * FROM [user] WHERE u_name LIKE '%三%'

will treat u_name as "Zhang San", "Zhang Cat San", "Three-legged Cat", "Tang Tripitaka" and so on, find all the records with "three" in them.

In addition, if you need to find the records with both "三" and "cat" in u_name, please use the and condition

SELECT * FROM [user] WHERE u_name LIKE '%三%' AND u_name LIKE '%猫%'

If you use SELECT * FROM [user] WHERE u_name LIKE ' %三%cat%'

Although the search for "three-legged cat" can be found, the search for "Zhang Cat San" that meets the conditions cannot be found.

2 _:

represents any single character. Matches a single arbitrary character, which is often used to limit the character length of expressions:

For example, SELECT * FROM [user] WHERE u_name LIKE '_三_'

Only find "Tang Sanzang" In this way u_name has three characters and one character in the middle is "三";

Another example is SELECT * FROM [user] WHERE u_name LIKE '三__'; Only find "三级" Cat" has a name of three characters and the first character is "三";

3 [ ]:

represents one of the characters listed in brackets (similar to a regular expression) . Specify a character, string, or range, requiring the matched object to be any one of them.

For example, SELECT * FROM [user] WHERE u_name LIKE '[Zhang Li Wang] San' will find "Zhang San", "Li San", "Wang San" (instead of "Zhang Li Wang San" );

If [ ] contains a series of characters (01234, abcde, etc.), it can be abbreviated as "0-4", "a-e"

SELECT * FROM [user] WHERE u_name LIKE '老[1-9]' 将找出“老1”、“老2”、……、“老9”；

4 [^ ]:

represents a single character not listed in brackets. Its value is the same as [], but it requires that the matched object is any character other than the specified character.

For example, SELECT * FROM [user] WHERE u_name LIKE '[^Zhang Li Wang] San' will find "Zhao San" and "Sun San" whose surnames are not "Zhang", "Li" and "Wang" " etc.;

SELECT * FROM [user] WHERE u_name LIKE '老[^1-4]'; 将排除“老1”到“老4”，寻找“老5”、“老6”、……

5 When the query content contains wildcards

Due to wildcards, we query the special characters "%", "_", " The statement of [" cannot be implemented normally, but the special characters can be queried normally if enclosed with "[]". Based on this, we write the following function:

function sqlencode(str) str=replace(str,"';","';';")

str=replace(str,"[","[[]") ';This sentence must come first str=replace(str,"_", "[_]") str=replace(str,"%","[%]") sqlencode=str end function

The above are the four usages of mysql advanced (6) fuzzy query The content of the introduction, for more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!