Home > Article > Backend Development > How to implement fuzzy query in php
How to implement fuzzy query in php: 1. Use SQL matching mode, the operator must use LIKE or NOT LIKE, when matching, it is not case sensitive; 2. Use regular expression matching mode, its regular expression The formula appears anywhere in the matching field.
[Related learning recommendations: php programming (video)]
php achieves blurring Query method:
Method 1: SQL matching pattern
1. Use sql matching pattern. You cannot use the operator =
or ! =
, but use the operator LIKE or NOT LIKE;
2. Using sql matching mode, MYSQL provides 2 kinds of wildcard characters.
%
represents any number of any characters (including 0)
_
Represents any single character
3. Use sql matching mode. If the matching format does not contain any of the above two wildcard characters, the query effect is equivalent to = or! =
4. Use sql matching pattern. When matching, it is case-insensitive
#查询用户名以某个字符开头的用户 #查询用户名以字符'l'开头的用户: l% SELECT * FROM user WHERE username LIKE 'l%'; #查询用户名以某个字符结尾的用户 #查询用户名以字符'e'结尾的用户:e% SELECT * FROM user WHERE username LIKE 'e%'; #查询用户名包含某个字符的用户 #查询用户名包含字符'o'的用户:%o% SELECT * FROM user WHERE username LIKE '%o%'; #查询包含三个字符的用户 SELECT * FROM user WHERE username LIKE '___'; #查询用户名第二个字符为o的用户:_o% SELECT * FROM user WHERE username LIKE '_o%';
Method 2: Regular expression matching pattern
Wildcard ( Regular expression)
.
matches any single character
*
matches 0 or more characters before it
x*
means matching any number of x characters
[..] Matches any character in square brackets
[abc] Matches the characters ab or c
[a-z] Matches any letter
[0-9] Matches any number
[0-9]*Match any number of any digits
[a-z]*Match any number of letters
^
means starting with a certain character or string
^a
means starting with the letter a
$
means ending with a certain character or string
s$
means ending with the letter s
The operator used to match the pattern using regular expressions is:
REGEXP
or NOT REGEXP
(RLIKE or NOT RLIKE)
Note: Regular expression matching pattern, its regular expression appears anywhere in the matching field ,
Even if the pattern matches, there is no need to put a wildcard character on both sides to make it match;
If only the wildcard character . is used to match, assuming N, then the matching pattern means that it is greater than Equal to N;
How do you understand the above sentence?
That is to say
... Matches data that is greater than or equal to 3 characters
.... Matches data that is greater than or equal to 4 characters
# Query users whose username starts with the character l: ^l;
#Regular expression writing
SELECT * FROM user WHERE username REGEXP '^l'; #sql匹配模式写法: SELECT * FROM user WHERE username LIKE 'l%'; #查询用户名正好是三个字符的用户:^...$; #sql匹配模式写法: SELECT * FROM user WHERE username LIKE '___'; #正则表达式写法 SELECT * FROM user WHERE username REGEXP '^...$';
Related recommendations:Programming video course
The above is the detailed content of How to implement fuzzy query in php. For more information, please follow other related articles on the PHP Chinese website!