Fuzzy matching query and regular matching in MySQL

The simplest method of fuzzy query

In MySQL we can compare using the like or not like operators. Schema defaults are case-insensitive in MySQL.

Query example, Student table

+--------+---------+-------+-----------------+---------+
| Studid | name | Marks | Address | Phone |
+--------+---------+-------+-----------------+---------+

Mysql> SELECT * FROM student where name like ' m% ';

Lists all student names that begin with the letter M in the table student.

Mysql> SELECT * FROM student where name like '%e ';

List all student names ending with the letter E.

The code is as follows	Copy Code
Mysql> SELECT * FROM student where name like '%a% ';

List student names that contain any particular letter anywhere. The following query example lists the student names that contain the letter "a".

The code is as follows	Copy Code
Mysql> SELECT * FROM student where name like ' _____ ';

If we are looking for a name that includes 5 letters, we can use the special letter "_" (underline). The names of the 5-letter students will be listed in the table student.

Regular matching query:

Other types of pattern matching provided by MySQL are the use of extended regular expressions. When you match this pattern to a test, use the regexp and not regexp operators (or rlike and not rlike, which are synonyms).

Some characters of the extended regular expression 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 "-". "[A-z]" matches any lowercase letter, and "[0-9]" matches any number.
"*" matches 0 or more things in front of it. For example, "x*" matches any number of "X" characters, "[0-9]*" matches any number of numbers, and ". *" matches any number of anything.
Regular expressions are case-sensitive, but if you want to, you can use a character class to match two ways of writing. For example, "[AA]" matches either lowercase or uppercase "a" and "[A-za-z]" matches any of the letters in both ways.
If it appears anywhere in the tested value, the pattern matches (as long as they match the entire value and the SQL pattern matches).
To locate a pattern so that it must match the start or end of the tested value, use "^" at the beginning of the pattern or at the end of the pattern with "$".
To illustrate how extended regular expressions work, the like query shown below uses RegExp overrides:

To find the name that begins with "B", use "^" to match the start of the first name and "[BB]" to match lowercase or uppercase "B":

The code is as follows	Copy Code
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 the name ending with "FY", use "$" to match the end of the name:

The code is as follows	Copy Code
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 the name containing a "W", use "[WW]" to match the lowercase or uppercase "W":

The code is as follows	Copy Code
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 if a regular expression appears anywhere in the value and its pattern matches, there is no need to place a wildcard in the previous query on both sides of the pattern so that it matches the entire value, as if you were using an SQL schema.

To find names containing exactly 5 characters, use "^" and "$" to match the start and end of the first name, and the 5 "." Instances in between:

The code is as follows	Copy Code
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 by using the "{n}" "Repeat N-times" operator:

The code is as follows	Copy Code
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 | + ——-+--–+ ——— +--+ ———— + ——-+

If you use Chinese we can use the MySQL word breaker to implement fuzzy query

One, source

MySQL Full-text search parser default is by Space segmentation, can not directly support Full-text search Chinese. Starting with version 5.1, MySQL Full-text search parser is provided as a plugin. Rabbit Hunter provides Chinese word segmentation module in accordance with MySQL plug-in format.

Second, the environment and installation

First download the Mysql5.1 version. Then start the MySQL service in the gb2312 or GBK environment.

The code is as follows	Copy Code
# Cd/usr/local/mysql/bin #./mysqld-max--user=mysql--DEFAULT-CHARACTER-SET=GBK

Copy seg.so to the path specified by the plugin by default/usr/local/mysql/lib/mysql

The code is as follows	Copy Code
# CP./seg.so/usr/local/mysql/lib/mysql/seg.so

Copy the dictionary to the Dic subdirectory under the MySQL data path, by default/usr/local/mysql/data/dic/

Enter MySQL:

The code is as follows	Copy Code
# MySQL--DEFAULT-CHARACTER-SET=GBK Install plugins: Mysql>install PLUGIN cn_parser soname ' seg.so ';

Third, the use of Chinese participle plug-in

To create a table:

The code is as follows	Copy Code
Mysql>create TABLE T (c VARCHAR (255), Fulltext (c) with PARSER Cn_parser) default CharSet GBK; Mysql>insert into T VALUES (' Test Chinese '); Mysql>insert into T VALUES (' teacher says tomorrow meeting '); Mysql> INSERT into T VALUES (' Buy mobile phone '); Inquire:

Mysql> SELECT MATCH (c) against (' description '), C from T;

The code is as follows

Copy Code

+-----------------------------------+-----------------------+ | MATCH (c) against (' description ') | C | +-----------------------------------+-----------------------+ | 0 | Test Chinese | | 0 | The teacher said the meeting tomorrow | | 0 | Buy Mobile Phone | +-----------------------------------+-----------------------+ 3 Rows in Set (0.00 sec) Mysql> SELECT MATCH (c) against (' say '), C from T; +--------------------------------+-----------------------+ | MATCH (c) against (' say ') | C | +--------------------------------+-----------------------+ | 0 | Test Chinese | | 0.58370667695999 | The teacher said the meeting tomorrow | | 0 | Buy Mobile Phone | +--------------------------------+-----------------------+ 3 Rows in Set (0.00 sec)