What is mysql fuzzy query statement?

What is mysql fuzzy query statement?

The fuzzy query statement is as follows: "SELECT field FROM table WHERE certain field Like condition".

mysql provides four matching modes:

1, % represents any 0 or more characters.

The following statement:

SELECT * FROM user WHERE name LIKE ';%三%';

will duname as "Zhang San", "Sanjiao" "Cat", "Tang Sanzang", etc. Find out all the characters with "three";

2, _ represents any single character. Statement:

SELECT * FROM user WHERE name LIKE ';_三_';

Only find "Tang Sanzang" so that the name has three characters and the middle character is " Three";

SELECT * FROM user WHERE name LIKE ';三__';

Only find "three-legged cat" so that the name has three characters and the first character is " Three";

3, [ ] represents one of the characters listed in brackets (similar to regular expressions).

Statement:

SELECT * FROM user WHERE name LIKE ';[张李王]三';

will find "Zhang San", "李王" "Three", "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 name LIKE ';老[1-9]';

will find "old 1", "old 2",..., "老9";

If you are looking for the "-" character, please put it first: ';Zhang San[-1-9]';

4,[^ ] represents a single character not listed in brackets. Statement:

SELECT * FROM user WHERE name LIKE ';[^Zhang Liwang]三';

will find out the names of those whose surname is not "Zhang". "Zhao San", "Sun San" of "Li", "Wang", etc.;

SELECT * FROM user WHERE name LIKE ';老[^1-4]';

will Exclude "Old 1" to "Old 4" to find "Old 5", "Old 6", ..., "Old 9".

The last thing is the point!

Due to wildcards, our query statements for special characters "%", "_", "[", and "';" cannot be implemented normally, and it is convenient to enclose special characters with "[ ]" Can be queried normally. Based on this, we write the following function:

function sqlencode(str)
str=replace(str,"';","';';")
str=replace(str,"[","[[]") ';此句一定要在最先
str=replace(str,"_","[_]")
str=replace(str,"%","[%]")
sqlencode=str
end function

Recommended tutorial: "

MySQL Tutorial

"

The above is the detailed content of What is mysql fuzzy query statement?. For more information, please follow other related articles on the PHP Chinese website!