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

JavaScript to achieve magnifying glass effect

This article shares the specific code for JavaScr...

Singleton design pattern in JavaScript

Table of contents 1. What is a design pattern? 2....

Tutorial on installing Ubuntu 20.04 and NVIDIA drivers

Install Ubuntu 20.04 Install NVIDIA drivers Confi...

How to set a fixed IP in Linux (tested and effective)

First, open the virtual machine Open xshell5 to c...

Implementation of socket options in Linux network programming

Socket option function Function: Methods used to ...

vue3 timestamp conversion (without using filters)

When vue2 converts timestamps, it generally uses ...

Nginx access control and parameter tuning methods

Nginx global variables There are many global vari...

Tutorial on installing and configuring remote login to MySQL under Ubuntu

This article shares the MySQL installation and co...

How to uninstall MySQL cleanly (tested and effective)

How to uninstall Mysql perfectly? Follow the step...

Detailed tutorial on deploying Hadoop cluster using Docker

Recently, I want to build a hadoop test cluster i...