Mysql | Detailed explanation of fuzzy query using wildcards (like,%,_)

Wildcard categories:

%Percent wildcard: indicates any character can appear any number of times (can be 0 times).

_Underline wildcard: It means that only a single character can be matched, no more and no less, just one character.

Like Operator:

The role of LIKE is to instruct MySQL that the subsequent search pattern is to use wildcards instead of direct equality matching for comparison.

Note: If you use the like operator without a universal matcher, the result is the same as =. SELECT * FROM products WHERE products.prod_name like '1000'; can only match the result 1000, not JetPack 1000.

1)% Wildcard usage:

Matches records starting with "yves": (including records "yves")

SELECT * FROM products WHERE products.prod_name like 'yves%';

Matches records containing "yves" (including records with "yves")

SELECT * FROM products WHERE products.prod_name like '%yves%';

Matches records ending with "yves" (including records "yves", excluding records "yves", that is, records with spaces after yves, please note)

SELECT * FROM products WHERE products.prod_name like '%yves';

2)_Wildcard usage:

SELECT * FROM products WHERE products.prod_name like '_yves';

The matching result is: records like "yyves".

SELECT * FROM products WHERE products.prod_name like 'yves__';

The matching result is: records like "yvesHe". (An underscore can only match one character, no more and no less)

Note:

Pay attention to case. When using fuzzy matching, that is, matching text, MySQL may be case-sensitive or insensitive. This result depends on how the user configures MySQL. If it is case-sensitive, then a record like YvesHe cannot be matched by a matching condition like "yves__".

Note the trailing space, "%yves" cannot match a record like "heyves".

Note that NULL and % wildcards can match any character, but not NULL. That is, SELECT * FROM products WHERE products.prod_name like '%'; will not match records where products.prod_name is NULL.

Tips and suggestions:

As you can see, MySQL wildcards are very useful. This functionality comes at a cost, however: wildcard searches generally take longer to process than the other searches discussed previously. Here are some tips to remember when using wildcards.

Don't overuse wildcards. If other operators can achieve the same purpose, you should use other operators.

When you do need to use wildcards, don't use them at the beginning of a search pattern unless absolutely necessary. Placing the wildcard at the beginning of the search pattern is the slowest search.

Note carefully the placement of the wildcard characters. If placed in the wrong place, it may not return the expected number.

Additional knowledge: MySQL LIKE fuzzy query % _ wildcard will query all data

I recently wrote a batch of paging query tests. The query results showed that using the two special characters _ % would result in all data being queried. The query conditions did not work and the results were not what the test wanted.

Later I found that these two special characters are wildcards in SQL LIKE queries and can represent any characters.

Finally came up with 2 solutions

1. Escape these two special characters to achieve

2. Use the internal function INSTR to replace the traditional LIKE query method, which is faster.

The INSTR() function returns the position of the first occurrence of a substring within a string. If the substring is not found in str, the INSTR() function returns zero (0).

Below explains the syntax of the INSTR function.

select * from user where INSTR(name,'Xiao Ming');

The above article Mysql | Detailed explanation of fuzzy query using wildcards (like,%,_) is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.