How to query fields with mysql wildcards-1024programmer

Mysql wildcard query field method: 1. Use [%] to indicate that any character appears any number of times, the code is [WHERE title like '%Zhang San']; 2. Use [_ 】Indicates a single character, and the code is 【WHERE title like & # 39;__Zhang San & # 39;】.

More related free learning recommendation: mysql tutorial (video)

mysql wildcard query Field method:

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

The operator

like is the operator in the SQL statement. Its function is to indicate that the search pattern behind the SQL statement uses wildcards instead of direct Equality matches for comparison.

Note: If no wildcard is used when using the like operator, the effect is consistent with the equal sign.

SELECT id,title FROM table WHERE title like 'Zhang San';

This kind of writing is only It can match the record of Zhang San, but not the record like Zhang San is a good person.

Wildcard

% (percent sign), _ (underscore) is a wildcard, % 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 'Zhang San%';
 SELECT id, title FROM table WHERE title like & # 39;% Zhang San & # 39;;
 SELECT id, title FROM table WHERE title like & # 39; % Zhang San% & # 39;;

means to match records starting with Zhang San, 2 means matching records ending with Zhang San, 3 means Match records containing Johnny.

SELECT id,title FROM table WHERE title like 'Zhang San_';
 SELECT id, title FROM table WHERE title like & # 39;__ Zhang San & # 39;;

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

Note

Pay attention to capitalization when using fuzzy matching , that is, when matching text, mysql may be case-sensitive or case-insensitive. This result depends on the user’s configuration method for MySQL.
Pay attention to the trailing spaces.
Note NULL, % can match any character, but cannot match NULL.

Fair use

MySQL Wildcards are 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 serve the same purpose.
When you do need to use wildcards, unless absolutely necessary, do not use them at the beginning of the search pattern, put the wildcard at the beginning of the search pattern, search up is the slowest.
Pay careful attention to the placement of wildcards, if you put them in the wrong place, it may not return the desired number.

The above is the detailed content of how to query fields with mysql wildcards. For more information, please pay attention to other related articles on 1024programmer.com!