Detailed explanation of the use of the built-in function locate instr position find_in_set in MySQL efficient fuzzy search

There are 4 commonly used methods, as follows:

1. Use the locate() method

Common usage:

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

Similar to java's indexOf();

However, locate() will return a result greater than 0 as long as it finds the result (even if the query content is the very beginning), and will return 0 if it is not found.

Specify the starting position:

SELECT LOCATE('bar','foobarbar',5); (search starts at the fifth position of foobarbar)

2. Use the instr() function (said to be an alias function of 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 of the locate() method, with the same function)

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

However, it is no longer judged by the return value, but by the keyword in

4. Use find_in_set() function

For example: find_in_set(str,strlist), strlist must be a string separated by commas.

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

SQL>SELECT FIND_IN_SET('b','a,b,c,d');
---------------------------------------------------------+
|SELECTFIND_IN_SET('b','a,b,c,d')|
---------------------------------------------------------+
|2|
---------------------------------------------------------+
1rowinset(0.00sec)

Summary: The only difference between locate, position and instr is the position of the parameters. Apart from the fact that locate has an additional parameter for the starting position, the two are the same.

find_in_set() is special, but they all return the position of the substring to be found in the specified string.

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

The following is an introduction to the use of Like in MySQL

Wildcards in MySQL's LIKE statement: percent sign, underscore, and escape

%: represents any one or more characters. Matches characters of any type and length.

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 records that contain both "三" and "猫" in u_name, use the and condition

SELECT * FROM ［user］ WHERE u_name LIKE '%三%' AND u_name LIKE '%貓%'

If you use SELECT * FROM ［user］ WHERE u_name LIKE '%三%貓%'

Although you can search for "三脚猫", you cannot search for "张猫三" which meets the criteria.

_: represents any single character. Matches a single arbitrary character, which is often used to limit the character length of the expression statement: (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 'h_xiao';

What if I really want to check % or _? When using escape, the % or _ after the escape character will not be used as a wildcard. Note that the % and _ without the escape character in front still act as wildcards.

Sql code

select username from gg_user where username like '%xiao/_%' escape '/';

select username from gg_user where username like '%xiao/%%' escape '/';

MySQL Wildcards

SQL pattern matching allows you to use "_" to match any single character, and "%" to match any number of characters (including zero characters). In MySQL, SQL modes are case-insensitive by default. Some examples are shown below.

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

To find names starting 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 names ending 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 names containing 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 names containing exactly 5 characters, use the "_" pattern 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 |
+-------+--------+---------+------+------------+-------+

Another type of pattern matching provided by MySQL is using extended regular expressions. When you test for a match on such patterns, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

Some characters for extended regular expressions are:

"." matches any single character.

A character class "[...]" matches any character within the square brackets.

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

" * " matches zero or more of what precedes it.

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

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

For example, "[aA]" matches lowercase or uppercase "a" and "[a-zA-Z]" matches any letter in either case.

The pattern matches if it appears anywhere in the value being tested (SQL patterns match as long as they match the entire value).

To position 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 below using REGEXP:

To find names that begin with "b", use "^" to match the start of the name and "[bB]" to match a 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 names ending 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 names containing a "w", use "[wW]" to match either 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 the pattern matches if a regular expression appears anywhere in the value, it is not necessary to put a wildcard on both sides of the pattern in the previous query to make it match the entire value, as you would if you used an SQL pattern.

To find names that contain exactly 5 characters, use "^" and "$" to match the beginning and end of the name, and 5 instances of "." in between:

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 usage of the MySQL fuzzy search method Like. Compared with the three built-in functions of locate, instr and position, the performance is slower. No matter which fuzzy search is used, it will consume a lot of server resources when querying. Therefore, we should try to use fuzzy search as little as possible in actual work.