MySQL uses built-in functions for fuzzy search (locate (), etc.)

Commonly used in a total of 4 methods, as follows: 1. Using the Locate () method

1.1. General Usage:

SELECT ' column ' from ' Table ' where locate ('keyword', ' condition ') >0

Java-like indexOf (); however locate () as long as the found returned results are greater than 0 (even if the content of the query is the first part), it is not found to return 0;

1.2. Specify the location:

SELECT LOCATE (' Bar ', ' Foobarbar ', 5); -7 (starting from the fifth position in Foobarbar)

2. Use the InStr () function (which is said to be an alias function of locate ())

SELECT ' column ' from ' Table ' where InStr (' condition ', 'keyword') >0

The only difference is that the location of the query content is different, see the keyword in the SQL statement

3. Using the position () method, (also said to be the alias function of the Locate () method, the same function)

SELECT ' column ' from ' table ' where position ('keyword' in ' condition ')

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

4. Using the Find_in_set () function

such as: find_in_set (str,strlist),strlist must be a comma-delimited string

If the string str is a string list of n substrings in the Strlist, the range of return values is 1 to n

SELECT Find_in_set (' B ', 'a,b,c,d');  SELECT find_in_set (' B ', ' a,b,c,d ')                       2                                                      

Summary : The difference between locate, position, and InStr is just the same as the parameter location, and the parameter of locate more than one starting position.

Find_in_set () is a very special existence, but they all return the position of the substring to find in the specified string.

the speed of the first 3 is a little faster than with like. (It's a pity that none of the four functions can use an index.)

MySQL uses built-in functions for fuzzy search (locate (), etc.)