A brief discussion on the implementation of fuzzy query using wildcards in MySQL

In the MySQL database, when we need fuzzy query, we will use wildcards.

First, let's understand two concepts, one is the operator and the other is the wildcard.

Operators

Like is an operator in SQL statements. Its function is to indicate that the search pattern following the SQL statement is to be compared using wildcards instead of direct equality matching.

Note: If you use the like operator without wildcards, the effect is the same as the equal sign.

SELECT id,title FROM table WHERE title like '张三';

This way of writing can only match Zhang San's record, but not records like Zhang San is a good person.

Wildcards

% (percent sign) and _ (underscore) are wildcards. % means any character can appear any number of times (can be 0 times), and _ means a single character. The user is as follows:

1 SELECT id,title FROM table WHERE title like '张三%';
2 SELECT id,title FROM table WHERE title like '%张三';
3 SELECT id,title FROM table WHERE title like '%张三%';

1 means matching records that start with Zhang San, 2 means matching records that end with Zhang San, and 3 means matching records that contain Zhang San.

1 SELECT id,title FROM table WHERE title like '张三_';
2 SELECT id,title FROM table WHERE title like '__张三';

1 means matching records like Hello Zhangsan, and 2 means matching records like Hello Zhangsan.

Usage Notes

Pay attention to case. When using fuzzy matching, that is, matching text, MySQL may be case-sensitive or insensitive. The result depends on how the user configures MySQL.

Note the trailing space.

Note that NULL and % can match any character, but cannot match NULL.

Fair Use

MySQL wildcards are very useful, but this functionality comes at a price. Wildcard searches generally take longer to process than the other searches discussed earlier. Here are some tips to remember when using wildcards.

Do not overuse wildcards; use other operators if they can achieve the same purpose.

When you do need to use wildcards, do not use them at the beginning of the search pattern unless absolutely necessary. Placing wildcards at the beginning of the search pattern makes the search slowest.

Pay careful attention to the placement of the wildcard characters; if they are misplaced, the expected number may not be returned.

This is the end of this article about MySQL wildcard fuzzy query. For more information about MySQL wildcard fuzzy query, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!