What is the usage of like in mysql statement?

Usage of like in mysql statement: 1. Use it with [%], [%] represents a wildcard character of one or more characters; 2. Use it with [_], [_] represents a wildcard character of only one character.

Like usage in mysql statement:

1. Common usage:

(1) Use with %

% represents a wildcard character of one or more characters, such as querying data starting with big in the field name:

(2) Use with _

_ represents a wildcard character of only one character. If you change % in the above query statement to _, you will find that only one piece of data can be queried.

2. Using like fuzzy query will lead to index failure and performance problems when the amount of data is large

(1) Try to avoid fuzzy queries starting with % or _

By explaining the execution plan, we found that when using like fuzzy query, if the query does not start with % and _, the index is still valid.

(2) Use covering index

When the query conditions and query results are fields in the index, we can call this index a covering index. At this time, use Like fuzzy query index is effective.

The primary key in InnoDB does not need to be added to the index

Note: When using a covering index, the length of the field is limited by requirements. Generally, if the length is exceeded, the index will fail

If the query contains a description field, the covering index will also be invalid.

Extended information

The syntax format of the like statement is: select * from table name where field name like corresponding value (substring ), it is mainly for character fields, and its function is to retrieve the corresponding substring in a character field column.

1. % Any string containing zero or more characters:

1. like'Mc%' will search for all strings starting with the letters Mc (such as McBadden).

2. like'%inger' will search for all strings ending with the letters inger (such as Ringer, Stringer).

3. like'%en%' will search for all strings containing the letter en in any position (such as Bennet, Green, McBadden).

2. :_ (underscore) Any single character:

like'_heryl' will search for all six-letter names ending with the letters heryl (such as Cheryl, Sheryl).

3. [ ] specifies any single character in the range ([a-f]) or set ([abcdef]):

1, like'[CK]ars[eo]n' will Search for the following strings: Carsen, Karsen, Carson, and Karson (like Carson).

2. like'[M-Z]inger' will search for all names ending with the string inger and starting with any single letter from M to Z (such as Ringer).

The above is the detailed content of What is the usage of like in mysql statement?. For more information, please follow other related articles on the PHP Chinese website!