How to query fields with mysql wildcard

Mysql wildcard query field method: 1. Use [%] to represent any character appearing any number of times, the code is [WHERE title like '%张三']; 2. Use [_] to represent a single character, the code It is [WHERE title like '__Zhang San'].

More related free learning recommendations: mysql tutorial(video)

mysql wildcard query field method:

First of all, let’s understand two concepts, one is the operator and the other is the wildcard.

Operator

like is the operator in the SQL statement. Its function is to indicate that the search pattern behind the SQL statement is to use wildcards instead of direct equal matching. Compare.

Note: If no wildcard is used when using the like operator, 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 the record that Zhang San is a good person.

Wildcard characters

% (percent sign), _ (underscore) are wildcard characters, % means that any character appears any number of times (can be 0 times), _ means a single character, the user is as follows:

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

means matching records starting with Zhang San, 2 means matching records starting with Zhang Records ending in three, 3 means matching records containing Zhang San.

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

means matching records like Zhang Sanhao, 2 means matching records like Hello Zhang San.

Usage Notes

• Pay attention to the case. When using fuzzy matching, that is, when matching text, mysql may distinguish sizes, and It may not be case-sensitive. This result depends on how the user configures MySQL.

• Pay attention to the trailing spaces.

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

Reasonable use

• MySQL wildcards are very useful, but this feature comes at a cost, wildcard search Processing generally takes longer than the other searches discussed previously, so 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 really need to use wildcards, unless absolutely necessary, do not use them at the beginning of the search pattern. Put the wildcard character at the beginning of the search pattern for the best search results. slow.

• Pay careful attention to the position of the wildcard. If you put it in the wrong place, the desired number may not be returned.

The above is the detailed content of How to query fields with mysql wildcard. For more information, please follow other related articles on the PHP Chinese website!