Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to use like in mysql sentences, which can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

Mysql sentence like usage: 1, collocation [%] use, [%] represents one or more character wildcards; 2, collocation [_] use, [_] represents only one character wildcard.

Like usage in mysql statements:

1. Common usage:

(1) use with%

% represents a wildcard of one or more characters, such as data that begins with a large character in the query field name:

(2) collocation _ use

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

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

(1) make fuzzy queries starting with% or _ as little as possible

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

(2) use overlay index

When the conditions of the query and the results of the query are the fields in the index, the index can be called an overlay index. In this case, the use of like fuzzy query index is valid.

The primary key in InnoDB can not be added to the index

Note: when using an overlay index, the length of the field is limited by the requirements. If the field exceeds the length, the index will fail.

Here, if there is a descripition field in the query, the overridden index will also fail.

Extended data

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, its function is to retrieve the corresponding substrings in a character field column.

Any string containing zero or more characters:

1. Like'Mc%' will search for all strings that begin with the letter Mc, such as McBadden.

2. Like'%inger' will search all strings that end with the letter inger (such as Ringer, Stringer).

3. Like'%en%' will search for all strings (such as Bennet, Green, McBadden) that contain the letter en anywhere.

2.: _ (underscore) any single character:

Like'_heryl' searches for the names of all six letters ending with the letter heryl (for example, Cheryl, Sheryl).

[] any single character in the specified range ([amurf]) or collection ([abcdef]):

1like'[CK] ars [EO] n 'will search for the following strings: Carsen, Karsen, Carson and Karson (such as Carson).

2. Like' [Mmurz] inger' will search for all names (such as Ringer) that end with the string inger and begin with any single letter from M to Z.

Thank you for reading this article carefully. I hope it is helpful for everyone to share how to use like content in mysql sentences. At the same time, I also hope you can support us, pay attention to the industry information channel, and find out if you have any problems. Detailed solutions are waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.