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

There are four commonly used methods, as follows:

1. Use the locate () method

Common usage:

Select `column`from`table`condielocate ('keyword', `condition`) > 0

Similar to indexOf () of java

However, locate () returns 0 as long as the returned result is greater than 0 (even if the content of the query is at the beginning of the query).

Specify the starting position:

SELECT LOCATE ('bar','foobarbar',5); (start from the fifth location of foobarbar)

two。 Use the instr () function (which is said to be an alias function for locate ())

SELECT `column` from `table` where instr (`condition`, 'keyword') > 0

The only difference is the location of the query content.

3. Use the position () method (which is also said to be an alias function for the locate () method, with the same function)

SELECT `column`from `table` where position ('keyword' IN `condition`)

However, instead of judging by the return value, it uses the keyword in

4. Use the find_in_set () function

For example: find_in_set (str,strlist), strlist must be a comma-separated string

If the string str is a list of strings consisting of N substrings in strlist, the return value ranges from 1 to N.

SQL > SELECTFIND_IN_SET ('bachelors, page1, pr, pas, c, d') -+ | SELECTFIND_IN_SET ('baccalaure.' D') |-- + | 2 |-+ 1rowinset (0.00sec)

Summary: the difference among locate, position and instr is only the difference in the position of the parameters, while the locate has one more parameter in the starting position, and the two are the same.

Find_in_set () is special, but they all return the position of the substring you are looking for in the specified string.

The first three are slightly faster than using like. (however, none of these four functions can use indexes)

Next, I would like to introduce how to use Like in Mysql.

Wildcards in MySQL's like statements: percent sign, underscore, and escape

%: represents any or more characters. Characters of any type and length can be matched.

Sql code

Select * from user where username like'% huxiao'

Select * from user where username like 'huxiao%'

Select * from user where username like'% huxiao%'

In addition, if you need to find out that there are both "three" and "cat" records in u_name, use the and condition

SELECT * FROM [user] WHERE u_name LIKE'% 3% 'AND u_name LIKE'% cat%'

If you use SELECT * FROM [user] WHERE u_name LIKE'% 3% cat%'

Although you can search for "three-legged cat", you can't find a qualified "Zhang Cat three".

_: represents any single character. Matches a single arbitrary character, which is often used to limit the character length of an expression: (can represent a Chinese character)

Sql code

Select * from user where username like'_'

Select * from user where username like 'huxia_'

Select * from user where username like 'hogxiao'

What if I really want to check% or _? With escape, the% or _ after the escape character is not used as a wildcard. Note that the% and _ without the escape character before them still play the role of wildcard.

Sql code

Select username from gg_user where username like'% xiao/_%' escape'/'

Select username from gg_user where username like'% xiao/%%' escape'/'

MySQL wildcard

SQL pattern matching allows you to use "_" to match any single character, while "%" matches any number of characters (including zero characters). In MySQL, the mode of SQL ignores case by default. Some examples are shown below.

Note that when you use SQL mode, you cannot use = or! =; instead, use the LIKE or NOT LIKE comparison operator.

To find out the name that starts with "b":

Mysql > SELECT * FROM pet WHERE name LIKE "b" +-+ | name | owner | species | sex | birth | death | +- -+-+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +-+-+

To find out the name that ends with "fy":

Mysql > SELECT * FROM pet WHERE name LIKE "fy" +-+ | name | owner | species | sex | birth | death | +-+-- -+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-+-+

To find a name that contains a "w":

Mysql > SELECT * FROM pet WHERE name LIKE "w" +-+ | name | owner | species | sex | birth | death | +- -+-+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +-+-+

To find a name that contains exactly 5 characters, use the "_" mode character:

Mysql > SELECT * FROM pet WHERE name LIKE "_" +-+ | name | owner | species | sex | birth | death | +-+- -- + | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-+-+

Other types of pattern matching provided by MySQL are using extended regular expressions. When you do matching tests on such patterns, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

Some of the characters that extend regular expressions are:

"." Matches any single character.

A character class "[.]" Matches any character in square brackets.

For example, "[abc]" matches "a", "b", or "c". To name a range of characters, use a "-". "[amurz]" matches any lowercase letter, while "[0-9]" matches any number.

"*" matches zero or more things in front of it.

For example, "x*" matches any number of "x" characters, "[0-9] *" matches any number of numbers, and ". *" matches any number of anything.

Regular expressions are case-sensitive, but if you want, you can use one character class to match both.

For example, "[aA]" matches lowercase or uppercase "a" and "[a-zA-Z]" matches any of the two letters.

If it appears anywhere in the value being tested, the pattern matches (as long as they match the entire value, the SQL pattern matches).

To locate a pattern so that it must match the beginning or end of the value being tested, use "^" at the beginning of the pattern or "$" at the end of the pattern.

To illustrate how extended regular expressions work, the LIKE query shown above is rewritten using REGEXP below:

To find names that start with "b", use "^" to match the beginning of the name and "[bB]" to match lowercase or uppercase "b":

Mysql > SELECT * FROM pet WHERE name REGEXP "^ [bB]" +-+ | name | owner | species | sex | birth | death | +- -+-+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +-+-+

To find the name that ends with "fy", use "$" to match the end of the name:

Mysql > SELECT * FROM pet WHERE name REGEXP "fy$" +-+ | name | owner | species | sex | birth | death | +-+-- -+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-+-+

To find a name that contains a "w", use "[wW]" to match a lowercase or uppercase "w":

Mysql > SELECT * FROM pet WHERE name REGEXP "[wW]" +-+ | name | owner | species | sex | birth | death | +- -+-+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +-+-+

Since a regular expression appears anywhere in the value and its pattern matches, it is no longer necessary to place a wildcard on both sides of the pattern in the previous query to make it match the entire value, just as if you used an SQL schema.

To find a name that contains exactly five characters, use "^" and "$" to match the beginning and end of the name, and 5 "." The instance is between the two:

Mysql > SELECT * FROM pet WHERE name REGEXP "^. $" +-+ | name | owner | species | sex | birth | death | +-+- -- + | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-+-+

You can also rewrite the previous query using the "{n}"repeat n times" operator:

Mysql > SELECT * FROM pet WHERE name REGEXP "^. {5} $" +-+ | name | owner | species | sex | birth | death | +-+- -- + | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-+-+

The above is the use of Mysql fuzzy search method Like, the performance is slower than the previous three built-in functions of locate instr position, no matter which kind of fuzzy search will consume a lot of server resources when querying, so we should try to use less fuzzy search in practical work.

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.