MySQL Fuzzy query like/regexp

Adding and deleting is the most basic function of MySQL, which is the most frequent operation, Fuzzy Lookup is very common in the query operation, so fuzzy search has become a compulsory course.

Like mode

Like means look, there are two modes: _ and%

_ denotes a single character, usually used to query a fixed length of data, such as the names of all the three characters of the king, assuming the name column named name, note that "Wang" followed by two _

Select  from where  like ' Wang __ ';

% represents 0 or more arbitrary characters, such as the name of all Wang

Select  from where  like ' Wang% ';

Find out all the names that contain the word "Hua"

Select  from where  like ' % Hua% ';

Regular Mode

^, match the starting position of the string, or the above example, query all the names of the king

Select  from where ' ^ Wang ';

$, matches the end of the string, such as querying all names at the end of the name "Ming"

Select  from where ' Ming $ ';

., match any single character except \ n, like _, without writing the SQL statement

[...], matching any one of the characters contained in [], ABCDEF......XYZ can be abbreviated as [a-z],0123456789] [0-9], such as the name of the person who w/z/s the beginning of the query.

Select  from where ' ^[wzs] ';

[^ ...], matching characters not included in [], such as querying for names other than the beginning of w/z/s

Select  from where ' ^[^wzs] ';

A|b|c, match A or B or C, if the employee who performance is a-or a or a + is identified, assuming the performance column name performance

Select  from where ' a-| a| A +';

*, repeat 0 or more times, familiar with JavaScript regular students know

' str* ' can match st/str/strr/strrr ...

?, repeat 0 or 1 times

' Str? ' Can match St/str

+, repeat 1 or more times

' str+ ' can match str/strr/strrr/strrrr ...

Compared to the regular in JavaScript, here the regular is a simplified version, there is no lazy match/greedy match, [] \w\s\d this syntax is not supported, also does not support Chinese, relatively simple.

one point to note is that the two modes do not mix, like mode is not supported regular expression, RegExp mode does not know _ and%

Finally, by the way, Kobe Bryant retired yesterday, although I am lindane fans, but Kobe and Lindane played every game I remember, goodbye Kobe Bryant, Goodbye Youth!

MySQL fuzzy query like/regexp