The use of the MySql like fuzzy query wildcard is described in detail

1. SQL mode

SQL's pattern matching allows you to use "_" to match any single character, and "%" to match any number of characters (including zero characters). In MySQL, the schema of SQL is case-insensitive by default. Some examples are shown below. Note that when you use SQL mode, you cannot use = or! =; The LIKE or NOT LIKE comparison operators are used instead.

SELECT field FROM table WHERE certain field Like condition

In terms of conditions, SQL provides four matching patterns:

1, % : represents any number of characters. Matches characters of any type and length.

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

Records with "3" will be found for "u_name", "cat 3", "cat 3", "tang 3 hide", etc.

Also, if you need to find records with both "3" and "cat" in u_name, use the and condition

SELECT * FROM [user] WHERE u_name LIKE '%3%' AND u_name LIKE' % cat %'

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

Although it can search for "three-legged cat", it cannot search for "zhang cat 3".

2, _ : represents any single character. Matches a single arbitrary character, often used to limit the character length of an expression :(can represent 1 Chinese character)

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

Only find out "tang 3 Tibet" such that u_name is 3 words and the middle 1 word is "3";

SELECT * FROM [user] WHERE u_name LIKE '3__';

Find only "three-legged cat" so that name is 3 words and the first word is "3";

2. Regular patterns

The other type of pattern matching provided by MySQL is using extended regular expressions. When you match this pattern, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

The characters that extend the regular expression are:

". "matches any single character. (single byte character)

1 character class "[...] "Matches any character in square brackets. For example, "[abc]" matches "a", "b", or "c". To name a range of characters, use a "-". "[a-z]" matches any lowercase letter, and "[0-9]" matches any number.

"*" matches zero or more things in front of it. For example, "x*" matches any number of "x" characters, "[0-9] *" matches any number of Numbers, and ".* "matches any number of anything.

Regular expressions are case sensitive, but if you wish, you can use the 1 character class to match both ways. For example, "[aA]" matches the lowercase or uppercase "a" and "[a-zA-Z]" matches any letter written in both ways.

If it appears anywhere in the value being tested, the pattern matches (as long as they match the entire value, SQL pattern matches).

To locate a pattern so that it must match the beginning or end of the value being tested, use "^" at the beginning of the pattern or "$" at the end of the pattern.

To show how extended regular expressions work, the LIKE query shown above is rewritten below using REGEXP:

To find names that start with "3", use "^" to match the beginning of the name.

FROM [user] WHERE u_name REGEXP '^3';

All records starting with "3" will be found if u_name is "3-legged cat" and so on.

To find names ending in "3", use "$" to match the end of the name.

FROM [user] WHERE u_name REGEXP '3$';

Records ending in "3", "cat 3", etc., u_name will be found.

You can also override the previous query using the '{n}' 'n times' operator:

FROM [user] WHERE u_name REGEXP 'b{2}$';

Note: if it is a Chinese character, you may need to pay attention to 1.