MySQL's Fuzzy search

1. Fuzzy Search

The first time I immediately thought of the keyword like

1.1. The field to be queried contains a specific character, but is not determined by its location , using two% package up

SELECT * from phone where provider like '%Apple%'

Can match 'AppleXXX ', ' xxapplexx ', ' ABCDEFGApple'

1.2. Be able to determine the end of the field to be checked , as follows:

SELECT * from Student where name is like '% Cong ';

Can match to ' Huang Cong ', ' Cong ', ' xxx cong ' ( must be the end of the Cong )

1.3. determine the beginning ,

SELECT * from Student where name is like ' yellow ';

Can be matched to ' Huang Cong ', ' Huang XXXXX ' (Must be yellow-beginning)

It can be observed that the use of the keyword like must be used in conjunction with percent percent, for the above three cases

First, be able to determine the beginning: After the determination of the partial semicolon, like ' Tom%'

Second, to be able to determine the end of, in the determination of the part before the percent semicolon, like '%Tom '

Third, only know contains, but can not determine the specific location, like '%Tom%'

Special Note : As the efficiency of like is usually very low, so try to use less, if you have to use, it is best to meet the third case, that is, to determine the initial content,

Because this is able to use the index , and the other two can not use the index, will scan the whole table, so very slow.

2. Using MySQL built-in functions

Please refer to:MySQL uses built-in functions for fuzzy search (locate (), etc.)

3. Full-text indexing using MySQL

MySQL's Fuzzy search