We know that in the SQL, you can use like this predicate (expression) for fuzzy retrieval, and support for the%,?, _ and other placeholders.
However, the function of this fuzzy search has many limitations, simply too vague.
The REGEXP keyword is provided in MySQL to support regular expressions, of course, just some simple regular.
First, we construct some test data.
Copy Code code as follows:
--Build the table
Use test;
DROP TABLE IF EXISTS T_regcustomer;
CREATE TABLE T_regcustomer (
ID INT (a) auto_increment
, name VARCHAR (256)
, Age INT (10)
, PRIMARY KEY (ID)
) collate= ' Utf8_general_ci ' engine=innodb;
Add some test data:
Copy Code code 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 (' Wanda ', 21);
INSERT into T_regcustomer (name, age) VALUES (' Xiao Wang ', 22);
INSERT into T_regcustomer (name, age) VALUES (' Xiao Wang 2 ', 22);
INSERT into T_regcustomer (name, age) VALUES (' Knock Not Dead ', 23);
INSERT into T_regcustomer (name, age) VALUES (' Han ', 24);
INSERT into T_regcustomer (name, age) VALUES (' Han 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 Jar ', 25)
, (' Dapeng ', 20)
, (' Dapeng 2 ', 20)
, (' Dapeng 3 ', 20)
, (' Er Peng ', 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 Code code as follows:
SELECT *
From T_regcustomer;
2. Specify Column name query
Copy Code code as follows:
SELECT c.id, C.name, C.age
From T_regcustomer C
;
3. Sort the query results
Copy Code code 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 of characters (0~n)
Copy Code code as follows:
SELECT c.id, C.name, C.age
From T_regcustomer C
WHERE c.name like '% ROC% '
ORDER BY C.age ASC
;
5. regexp keyword
. Match any one character
Note that there is no starting (^) and ending ($) qualifier, so as long as the rows that appear in the column are retrieved.
Copy Code code 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 Code code as follows:
SELECT c.id, C.name, C.age
From T_regcustomer C
WHERE c.name REGEXP ' ^ King '
ORDER BY C.age ASC
;
7. Case sensitive
Copy Code code 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 or operational
Copy Code code 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. The group operation is regular
[123] indicates that 1, 2, 3 of these 3 digits appear
Copy Code code 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] matches 1, 2, 3 、.... 8, 9
Copy Code code 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 \. []()?-| As well as pagination, line-changing symbols, etc.
11. More Content
Please check "MySQL must know" 68-page Regular expression, pdf download Address: http://www.jb51.net/books/67331.html
