Mysql fuzzy match query and regular match

In mysql, if you use fuzzy search, we can use like directly. Of course, many times like cannot meet our needs. We can use regular expression matching to query, in the afternoon, I will introduce it to you.

The simplest method of fuzzy search

In MySQL, we can use the LIKE or not like operator for comparison. In MySQL, the mode is case-insensitive by default.

Query example: student table

+ -------- + --------- + ------- + --------------- + --------- +
| Studid | name | marks | address | phone |
+ -------- + --------- + ------- + --------------- + --------- +

Mysql> select * from student where name like'm % ';

Lists the names of all students whose names start with M in the student table.

Mysql> select * from student where name like '% E ';

Lists All student names ending with the letter e.

The Code is as follows:	Copy code
Mysql> select * from student where name like '% a % ';

Lists student names that contain any specific letter anywhere. The following query example lists the student names that contain the letter ".

The Code is as follows:	Copy code
Mysql> select * from student where name like '_____';

If the name we want to search for contains five letters, we can use a special letter "_" (underline ). The student table contains the names of five students.

Regular Expression matching query:

Other types of pattern matching provided by MySQL use extended regular expressions. When you perform a match test on this type of pattern, 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 in square brackets. For example, "[abc]" matches "a", "B", or "c ". To name a range of characters, use a hyphen (-). "[A-z]" matches any lowercase letter, and "[0-9]" matches any number.
"*" Matches zero or multiple items before it. For example, "x *" matches any number of "x" characters, "[0-9] *" matches any number of numbers, and ". * "matches any number of things.
Regular Expressions are case-sensitive, but if you want to, you can use one character class matching method. For example, "[aA]" matches lowercase or upper-case "a", and "[a-zA-Z]" matches any letter in either of the two statements.
If it appears anywhere in the tested value, the schema matches (as long as they match the entire value, the SQL schema matches ).
To locate a pattern so that it must match the start or end of the tested value, use "^" at the start of the pattern or "$" at the end of the pattern ".
To demonstrate how the extended regular expression works, the LIKE Query shown above is rewritten using REGEXP below:

To find the name starting with "B", use "^" to match the start of the name and use "[bB]" to match "B" in lower case or upper case ":

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 | | Boane | Diane | dog | m | + --- + -- + ---- +

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 locate the name containing a "w", use "[wW]" to match the "w" in lower case or upper case ":

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 | | Boane | Diane | dog | m | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | + ---- + --- + -- + ---- +

Since a regular expression appears anywhere in the value and its pattern matches, there is no need to place a wildcard in the two aspects of the pattern in the previous query so that it matches the entire value, just like if you use an SQL mode.

To locate a name that contains exactly five characters, use "^" and "$" to match the start and end of the name, and the five "." instances are in the range:

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 use the "{n}" "Repeat n times" operator to rewrite the previous query:

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 characters, we can use the mysql word segmentation plug-in to implement fuzzy query.

I. Source

The Parser for Mysql full-text search is split by space by default and cannot directly support Chinese characters for full-text search. Mysql full-text search parser is provided as a plug-in starting from version 5.1. The Chinese word segmentation module complies with the Mysql plug-in format.

II. Environment and Installation

First download MySQL. 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 plug-in. The default value is/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 in the Data path of Mysql. The default value is/usr/local/mysql/data/Dic/

Go to Mysql:

The Code is as follows:	Copy code
# Mysql -- default-character-set = gbk Install plug-ins: Mysql> install plugin cn_parser SONAME 'seg. so ';

3. Use the Chinese word segmentation plug-in

Create a table:

The Code is as follows:	Copy code
Mysql & gt; 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 ('instructor briefing Day '); Mysql> insert into t VALUES ('purchase cell phone '); Query:

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 | instructor briefing | | 0 | purchase a mobile phone | + ----------------------------------- + ----------------------- + 3 rows in set (0.00 sec)   Mysql> select match (c) AGAINST (''), c FROM t; + -------------------------------- + ----------------------- + | MATCH (c) AGAINST ('') | c | + -------------------------------- + ----------------------- + | 0 | test Chinese | | 0.58370667695999 | instructor briefing | | 0 | purchase a mobile phone | + -------------------------------- + ----------------------- + 3 rows in set (0.00 sec)