The fuzzy query in MySQL

Reprinted from: http://www.letuknowit.com/archives/90/

There are 2 ways to implement fuzzy queries in MySQL: One is to use like/not like, and the other is to use Regexp/not REGEXP (or Rlike/not rlike, which are synonyms).
The first is the standard SQL pattern match. It has 2 wildcard characters: "_" and "%". "_" matches any single character, while "%" matches any number of characters (including 0). Examples are as follows:

SELECT * FROM table_name WHERE column_name like ' m% '; #查询某字段中以m或M开头的所有记录
SELECT * FROM table_name WHERE column_name like '%m% '; #查询某字段中包含m或M的所有记录
SELECT * FROM table_name WHERE column_name like '%m '; #查询某字段中以m或M结尾的所有记录
SELECT * FROM table_name WHERE column_name like ' _m_ '; #查询某字段中3个字符且m或M在中间的所有记录

What if we want to query a string that contains wildcards? For example, 50% or _get. The answer is: escape. You can either escape directly with \, or escape by defining an escape character with escape, simply escaping one of the following characters, for example:

SELECT * FROM table_name WHERE column_name like '%50\%% '; /* 2nd% is escaped, querying a field that contains 50% of all records */
SELECT * FROM table_name WHERE column_name like '%50/%% ' ESCAPE '/'; #第2个% is escaped
SELECT * FROM table_name WHERE column_name like '%\_get% ' ESCAPE '/'; /* "_" is escaped, querying a field containing all records of _get */

The second is to use pattern matching with an extended regular expression. Let's take a look at the meaning of some characters of an extended regular expression:
“.” : Matches any single character
“?” : matches the preceding subexpression 0 or 1 times.
"+": matches the preceding subexpression 1 or more times.
"*": matches the preceding subexpression 0 or more times. x*, which represents 0 or more x characters; [0-9]*, matches any number of numbers.
"^": Indicates the start position of the match.
"$": Represents the end of the match position.
"[]": Represents a collection. [Hi], which means match H or i;[a-d], which matches any of a, B, C, D.
' {} ': Indicates the number of repetitions. 8{5}, which indicates a match of 5 8, or 88888;[0-9]{5,11}, that matches 5 to 11 digits.
Let's look at an example:

SELECT * FROM table_name WHERE column_name REGEXP ' ^50%{1,3} '; /* Query all records starting with 50%, 50%, or 50%%% in a field */

The fuzzy query in MySQL