PHP fuzzy query implementation method (recommended), php fuzzy query

PHP fuzzy query implementation method (recommended), php fuzzy query

Mode Query

1. SQL matching mode

2. Regular Expression matching mode (generally not recommended)

SQL matching mode

1. Use the SQL matching mode. The operator = or! is not allowed! =, But uses the operator LIKE or not like;

2. Using the SQL matching mode, MYSQL provides two wildcards.

% Indicates any number of arbitrary characters (including 0)

_ Represents any single character

3. Use the SQL match mode. If the match format does not contain any of the above two wildcards, the query effect is equivalent to = or! =

4. Use the SQL matching mode. The matching is case insensitive.

# Query users whose usernames start with a certain character # query users whose usernames start with 'L': l % SELECT * FROM user WHERE username LIKE 'l % '; # query users whose usernames end with a certain character # query users whose usernames end with the character 'E': e % SELECT * FROM user WHERE username LIKE 'e % '; # query a user whose username contains a certain character # query the user whose username contains the character 'O': % o % SELECT * FROM user WHERE username LIKE '% o % '; # query users with three characters: SELECT * FROM user WHERE username LIKE '___'; # query users with the second character "o: _ o % SELECT * FROM user WHERE username LIKE '_ o % ';

Regular Expression matching mode

Wildcard (regular expression)

. Match any single character

* Matches 0 or more characters before it.

X * Indicates matching any number of x characters.

[..] Match any character in brackets
[Abc] matching character AB or c
[A-z] match any letter
[0-9] matching any number
[0-9] * match any number
[A-z] * match any number of letters

^ Indicates starting with a character or string

^ A indicates that it starts with the letter.

$ Indicates ending with a character or string

S $ indicates ending with the letter s

The regular expression matching mode uses the following operators:

REGEXP or not regexp (RLIKE or not rlike)

Note: In the regular expression matching mode, the regular expression appears at any position of the matching field,

Even if the pattern is matched, you do not have to add a wildcard on both sides to make it match;

If only the wildcard. is used for matching, if N are used, the matching mode is greater than or equal to N;

How can we understand the above sentence?

That is to say

... Match data greater than or equal to 3 Characters
... Match data greater than or equal to 4 characters
# Query users whose usernames start with the character l: ^ l;
# Regular Expression writing

SELECT * FROM user WHERE username REGEXP '^ l'; # Statement of SQL matching mode: SELECT * FROM user WHERE username LIKE 'l % '; # query users with exactly three characters: ^... $; # SQL matching mode Syntax: SELECT * FROM user WHERE username LIKE '___'; # regular expression syntax SELECT * FROM user WHERE username REGEXP '^... $ ';

The above PHP fuzzy query implementation method (recommended) is all the content shared by Alibaba Cloud xiaobian. I hope to give you a reference and support for the help house.