MySQL like usage

MySQL like syntax

The LIKE operator is used in the where expression to search for the specified content in the matching field, with the following syntax:

WHERE column like pattern

WHERE column not as pattern

When you add the not operator to like, it means the opposite of what you mean by selecting column data records that do not contain pattern

Like is usually used in conjunction with the wildcard character%, which means that the content appearing in the wildcard pattern, rather than the wildcard% of the like syntax, represents an exact match, whose actual effect equals the = equals operator

Example of like use

Here is an example of data that uses the like query:

SELECT * FROM user WHERE username like ' small% ';

Results of the query returned:

The above example is to find all the records that username start with "small"

Note: A small% is indicated by a small start, and the following can be any character, the same,% small, indicating the end of "small", and% small% is the character containing "small" (and together with "% small" and "small%" of the two cases)

MySQL like case

When MySQL like matches characters, it is not case-sensitive by default, and if you need to be case-sensitive, you can add binary operators:

SELECT * from username WHERE like BINARY '%azz% '

SELECT * from username WHERE like BINARY '%azz% '

MySQL like Chinese match

Due to the problem of data storage coding, in some cases, the data returned by the like search in MySQL, in addition to conforming to the required data, will often return many irrelevant data, it is also necessary to add binary operators to the like after the binary comparison

SELECT * from username WHERE like BINARY '% small% '

Attention:

When the like match with the binary operator, it will be strictly distinguish between the English case, so the contents of the search if there are mixed in English and Chinese and need to ignore the English case, you will encounter problems, this time can be introduced in MySQL Upper () and count () function:

UPPER (): convert English characters to uppercase, same as UCase ()

CONCAT (): Concatenate multiple strings into a single string

So, when we want to do mixed match search in Chinese and English, and to ignore the English case, you can use the following statement:

SELECT * from username WHERE UPPER (username) like BINARY CONCAT ('% ', UPPER (' a Chinese B '), '% ');

The efficiency of like

The LIKE operator performs a scan-by-sweep match on the field data, and the actual execution is less efficient

MySQL like usage