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

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.

You may also be interested in:
  • MySQL fuzzy query usage (regular, wildcard, built-in function)
  • Summary of MySQL database like statement wildcard fuzzy query
  • A brief discussion on the implementation of fuzzy query using wildcards in MySQL
  • A brief discussion on wildcard escape in MySQL fuzzy query
  • MYSQL Must-know Reading Notes Chapter 8: Using Wildcards for Filtering
  • Things to note when using wildcards in MySQL
  • Detailed introduction to the use of MySql like fuzzy query wildcards
  • mysql regular expression LIKE wildcard
  • mysql wildcard (sql advanced filtering)

<<:  Use of Linux sed command

>>:  Practical record of Vue3 combined with TypeScript project development

Recommend

Detailed analysis of compiling and installing vsFTP 3.0.3

Vulnerability Details VSFTP is a set of FTP serve...

Docker container data volume named mount and anonymous mount issues

Table of contents What is a container data volume...

Briefly describe the difference between MySQL and Oracle

1. Oracle is a large database while MySQL is a sm...

Using MySQL in Windows: Implementing Automatic Scheduled Backups

1. Write a backup script rem auther:www.yumi-info...

Difference between var and let in JavaScript

Table of contents 1. Scopes are expressed in diff...

Detailed explanation of the working principle and solution of Js modularization

Table of contents 1. Modular concept 2. Modulariz...

Detailed explanation of Navicat's slow remote connection to MySQL

The final solution is in the last picture If you ...

Detailed explanation of Vue filters

<body> <div id="root"> <...

HTML tags list and usage instructions

List of HTML tags mark type Name or meaning effec...

Comparison of several examples of insertion efficiency in Mysql

Preface Recently, due to work needs, I need to in...

nginx solves the problem of slow image display and incomplete download

Written in front Recently, a reader told me that ...

How to open port 8080 on Alibaba Cloud ECS server

For security reasons, Alibaba Cloud Server ECS co...

Unity connects to MySQL and reads table data implementation code

The table is as follows: Code when Unity reads an...

Will CSS3 really replace SCSS?

When it comes to styling our web pages, we have t...