MySQL Regular Expressions Tutorial

We know that in SQL, we can use the predicate (expression) like for fuzzy retrieval and support %,? ,_ equal placeholder.
However, there are many limitations to this fuzzy search function, which is simply too fuzzy.
The REGEXP keyword is provided in MySQL to support regular expressions, of course, just a few very simple regular expressions.
First, we construct some test data.

--  Build table 
USE test;
DROP TABLE IF EXISTS t_regcustomer;
CREATE TABLE t_regcustomer (
 id INT(10) AUTO_INCREMENT
 ,name VARCHAR(256)
 ,age INT(10)
 , PRIMARY KEY(id)
) COLLATE='utf8_general_ci' ENGINE=InnoDB;

Add 1 more test data:

--  insert 1 Some test data :
TRUNCATE TABLE t_regcustomer;
INSERT INTO t_regcustomer(name, age) VALUES (' Wang Ming ',20);
INSERT INTO t_regcustomer(name, age) VALUES (' Wang. ',21);
INSERT INTO t_regcustomer(name, age) VALUES (' wang ',22);
INSERT INTO t_regcustomer(name, age) VALUES (' wang 2',22);
INSERT INTO t_regcustomer(name, age) VALUES (' Knock the undead ',23);
INSERT INTO t_regcustomer(name, age) VALUES (' Han han ',24);
INSERT INTO t_regcustomer(name, age) VALUES (' Han han 2',24);
INSERT INTO t_regcustomer(name, age) VALUES (' Guo jing, ',25);
INSERT INTO t_regcustomer(name, age) VALUES (' The guo jing 2',25);
INSERT INTO t_regcustomer(name, age) VALUES (' The guo jing 3',25);
INSERT INTO t_regcustomer(name, age) VALUES 
(' Guo to cylinder ',25)
,(' dapeng ',20)
,(' dapeng 2',20)
,(' dapeng 3',20)
,('2 peng ',19)
,(' Peng peng ',18)
,(' Peng peng 1',18)
,(' Xiao peng ',17)
,('AAA',17)
,('aaa',17)
,('SS',17)
,('s2',17)
,('ss',17)

1. The simplest query:

SELECT *  
FROM t_regcustomer;  

2. Specify the column name query

SELECT c.id, c.name, c.age  
FROM t_regcustomer c  
;  

3. Sort the query results

SELECT c.id, c.name, c.age  
FROM t_regcustomer c  
ORDER BY c.age ASC  
;  

4. like fuzzy retrieval
% matches any number (0~n) of any character

SELECT c.id, c.name, c.age  
FROM t_regcustomer c  
WHERE c.name LIKE '% peng %'  
ORDER BY c.age ASC  
;  

5. regexp keyword
. Matches any 1 character
Note that since there are no start (^) and end ($) qualifiers here, any rows that appear in the column will be retrieved.

SELECT c.id, c.name, c.age  
FROM t_regcustomer c  
WHERE c.name REGEXP '. peng .'  
ORDER BY c.age ASC  
;  

6. Regular start qualifier

SELECT c.id, c.name, c.age  
FROM t_regcustomer c  
WHERE c.name REGEXP '^ The king '  
ORDER BY c.age ASC  
;  

7. Case sensitive

SELECT c.id, c.name, c.age  
FROM t_regcustomer c  
WHERE c.name REGEXP BINARY '^s'  
ORDER BY c.age ASC  
;  

8. Regular or operation

SELECT c.id, c.name, c.age  
FROM t_regcustomer c  
WHERE c.name REGEXP BINARY 'a|s'  
ORDER BY c.name ASC  
;  

9. Group operation regularization
[123] means that 1 of the three Numbers 1, 2, and 3 appear

--  insert 1 Some test data :
TRUNCATE TABLE t_regcustomer;
INSERT INTO t_regcustomer(name, age) VALUES (' Wang Ming ',20);
INSERT INTO t_regcustomer(name, age) VALUES (' Wang. ',21);
INSERT INTO t_regcustomer(name, age) VALUES (' wang ',22);
INSERT INTO t_regcustomer(name, age) VALUES (' wang 2',22);
INSERT INTO t_regcustomer(name, age) VALUES (' Knock the undead ',23);
INSERT INTO t_regcustomer(name, age) VALUES (' Han han ',24);
INSERT INTO t_regcustomer(name, age) VALUES (' Han han 2',24);
INSERT INTO t_regcustomer(name, age) VALUES (' Guo jing, ',25);
INSERT INTO t_regcustomer(name, age) VALUES (' The guo jing 2',25);
INSERT INTO t_regcustomer(name, age) VALUES (' The guo jing 3',25);
INSERT INTO t_regcustomer(name, age) VALUES 
(' Guo to cylinder ',25)
,(' dapeng ',20)
,(' dapeng 2',20)
,(' dapeng 3',20)
,('2 peng ',19)
,(' Peng peng ',18)
,(' Peng peng 1',18)
,(' Xiao peng ',17)
,('AAA',17)
,('aaa',17)
,('SS',17)
,('s2',17)
,('ss',17)

0
[1-9] Match 1, 2, 3... Eight or nine

SELECT c.id, c.name, c.age  
FROM t_regcustomer c  
WHERE c.name REGEXP BINARY ' peng [1-9]'  
ORDER BY c.name ASC  
;  

10. Escape
Use \ \
Can you escape \.[]()? -| and pagination, newline symbols, etc

11. More

Please refer to "MySQL will know will be" on page 68 regular expressions, PDF download address: https: / / www ofstack. com books / 67331. html