Getting started with MySQL Regular Expression

We know that in SQL, we can use the like predicate (expression) for fuzzy search, and support % ,?, _ And Other placeholders.
However, the fuzzy search function has many restrictions, which are too vague.
In MySQL, The REGEXP keyword is provided to support regular expressions. Of course, it is just some simple regular expressions.
First, we construct some test data.
Copy codeThe Code is as follows:
-- Create a 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 some test data:

Copy codeThe Code is as follows:
-- Insert 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 da', 21 );
Insert into t_regcustomer (name, age) VALUES ('John, 22 );
Insert into t_regcustomer (name, age) VALUES ('John 2', 22 );
Insert into t_regcustomer (name, age) VALUES ('Don't die, 23 );
Insert into t_regcustomer (name, age) VALUES ('taobao', 24 );
Insert into t_regcustomer (name, age) VALUES ('example 2', 24 );
Insert into t_regcustomer (name, age) VALUES ('Guo Jing name', 25 );
Insert into t_regcustomer (name, age) VALUES ('Guo Jing 2', 25 );
Insert into t_regcustomer (name, age) VALUES ('Guo Jing 3', 25 );
Insert into t_regcustomer (name, age) VALUES
('Guo dicyline', 25)
, ('Dapeng ', 20)
, ('Dapeng 2', 20)
, ('Dapeng 3', 20)
, ('Erpeng', 19)
, ('Peng Peng ', 18)
, ('Peng Peng 1 ', 18)
, ('Peng ', 17)
, ('Aaa', 17)
, ('Aaa', 17)
, ('Ss', 17)
, ('S2', 17)
, ('Ss', 17)

1. The simplest query:
Copy codeThe Code is as follows:
SELECT *
FROM t_regcustomer;
2. Query of specified column names
Copy codeThe Code is as follows:
SELECT c. id, c. name, c. age
FROM t_regcustomer c
;
3. Sort query results
Copy codeThe Code is as follows:
SELECT c. id, c. name, c. age
FROM t_regcustomer c
Order by c. age ASC
;
4. like Fuzzy search
% Match any number (0 ~ N) any character
Copy codeThe Code is as follows:
SELECT c. id, c. name, c. age
FROM t_regcustomer c
WHERE c. name LIKE '% Peng %'
Order by c. age ASC
;
5. regexp keyword
. Match any character
Note that there are no start (^) and end ($) delimiters, so as long as the rows in the column are retrieved.
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
SELECT c. id, c. name, c. age
FROM t_regcustomer c
WHERE c. name REGEXP '^ Wang'
Order by c. age ASC
;
7. Case Sensitive
Copy codeThe Code is as follows:
SELECT c. id, c. name, c. age
FROM t_regcustomer c
WHERE c. name regexp binary '^ s'
Order by c. age ASC
;
8. Regular Expression or operation
Copy codeThe Code is as follows:
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 regular
[123] indicates that one of the three numbers 1, 2, and 3 appears.
Copy codeThe Code is as follows:
SELECT c. id, c. name, c. age
FROM t_regcustomer c
WHERE c. name regexp binary 'peng [123]'
Order by c. name ASC
;
[1-9] match 1, 2, 3,... 8, 9
Copy codeThe Code is as follows:
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 Escape \. [] ()? -| And paging and line feed characters

11. More

Please refer to MySQL required knowledge page 68 regular expression, PDF: http://www.jb51.net/books/67331.html