Regexp regular is used to match two string groups.

Recently, many similar issues have occurred in the MySQL forum. Summary.

 

For some reason, sometimes we do not put some attributes into the same string field according to the design principles of the paradigm. For example, for personal interests, sometimes we design the table
Create Table members (UID int primary key, uname varchar (20), Hoby varchar (100 ));

The table content is as follows:

Mysql> select * from members;
+ ----- + ------- + --------------------------------- +
| Uid | uname | holobby |
+ ----- + ------- + --------------------------------- +
| 1 | AAAA | music, movie, online, basketball, reading, and table tennis |
| 2 | BBBB | music, reading, table tennis, Daze, go, Shen Chan |
| 3 | CCCC | friend, table tennis |
| 4 | dddd | billiards, network, Reading, traveling |
| 5 | eeee | music, Daze, go, Shen Chan |
+ ----- + ------- + --------------------------------- +
4 rows in SET (0.00 Sec)

 

If we want to find a user X(Reading, dating, go, football, skiing) What if you want to operate the records of members with the same interests?

In other databases, we can break down this "reading, dating, go, football, and skiing" string through programs or stored procedures as a separate hobby project, then perform the like '% XXXX %' query one by one. But in MySQL, we can directly use thisRegexpRegular expressions are used to construct SQL statements.

 

First, we convert 'reading, making friends, go, football, and skey' to the regular expression 'reading | making friends | go | football | skey ',|In the regular expression'Or'

Mysql> select Replace ('reading, dating, go, football, skey', ',' | ');
+ --------------------------------------------- +
| Replace ('reading, dating, go, football, skey', ',' | ') |
+ --------------------------------------------- +
| Reading | dating | go | football | skiing |
+ --------------------------------------------- +
1 row in SET (0.00 Sec)

 

In this way, we can use the following SQL statement.
Mysql>Select * from members where Hober Regexp Replace ('reading, dating, go, soccer, skiing ',', '| ');
+ ----- + ------- + --------------------------------- +
| Uid | uname | holobby |
+ ----- + ------- + --------------------------------- +
| 1 | AAAA | music, movie, online, basketball, reading, and table tennis |
| 2 | BBBB | music, reading, table tennis, Daze, go, Shen Chan |
| 3 | CCCC | friend, table tennis |
| 5 | eeee | music, Daze, go, Shen Chan |
+ ----- + ------- + --------------------------------- +
3 rows in SET (0.00 Sec)

In the preceding statement, we can use an SQL statement to get a record of any items in holobby including 'reading, dating, go, football, and skey.

However, the preceding statement has a small defect, that is, the 'go go 'clause is also selected. If exact match is found, this record should not be selected. To avoid this situation, we make the following improvements to SQL statements.

Change the regular expression to ', (Reading | making friends | go | football | skiing),' that is, there must be a separator before and after the match","

 

Mysql> select Concat (', (', replace ('reading, dating, go, football, skey', ',' | '),'),');
+ --------------------------------------------------------------- +
| Concat (', (', replace ('reading, dating, go, soccer, skey', ',' | '),'), ') |
+ --------------------------------------------------------------- +
|, (Reading | dating | go | football | skiing), |
+ --------------------------------------------------------------- +
1 row in SET (0.00 Sec)

Mysql>Select * from Members
->Where Concat (',', holobby, ',') Regexp
->Concat (', (', replace ('reading, dating, go, soccer, skiing', ',' | '),'),');
+ ----- + ------- + --------------------------------- +
| Uid | uname | holobby |
+ ----- + ------- + --------------------------------- +
| 1 | AAAA | music, movie, online, basketball, reading, and table tennis |
| 2 | BBBB | music, reading, table tennis, Daze, go, Shen Chan |
| 3 | CCCC | friend, table tennis |
+ ----- + ------- + --------------------------------- +
3 rows in SET (0.00 Sec)

This prevents 5th records from being selected.

 

Of course, you can also use this regular expression ', reading, |, dating, |, go, |, football, |, skiing,', but the efficiency is obviously inferior ', (Reading | making friends | go | football | skiing.

 

Reference:

 

MySQL 5.1 reference manual-12.3.1. String comparison function-Regexp (rlike)

Http://dev.mysql.com/doc/refman/5.1/zh/functions.html#string-comparison-functions

 

MySQL 5.1 reference manual-Appendix G: MySQL Regular Expression

Http://dev.mysql.com/doc/refman/5.1/zh/regexp.html