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.
-- Create 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:
-- 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 ('xiao Wang ', 22); insert into t_regcustomer (name, age) VALUES ('King 2', 22); insert into t_regcustomer (name, age) VALUES ('Knock not dead ', 23); insert into t_regcustomer (name, age) VALUES ('Mr, 24); insert into t_regcustomer (name, age) VALUES ('Mr, 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 de cyline', 25), ('dapeng ', 20 ), ('dapeng 2', 20), ('dapeng 3', 20), ('twopeng ', 19), ('peng Peng', 18 ), ('peng Peng 1', 18), ('peng Peng ', 17), ('aaa', 17), ('aaa', 17), ('ss ', 17), ('s2', 17), ('ss', 17 );
1. The simplest query:
SELECT *FROM t_regcustomer;
2. Query of specified column names
SELECT c.id, c.name, c.ageFROM t_regcustomer c;
3. Sort query results
SELECT c.id, c.name, c.ageFROM t_regcustomer cORDER BY c.age ASC;
4. like Fuzzy search
% Match any number (0 ~ N) any character
SELECT c. id, c. name, c. ageFROM t_regcustomer cWHERE 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.
SELECT c. id, c. name, c. ageFROM t_regcustomer cWHERE c. name REGEXP '. Peng. 'order BY c. age ASC;
6. Regular start qualifier
SELECT c. id, c. name, c. ageFROM t_regcustomer cWHERE c. name REGEXP '^ Wang 'order BY c. age ASC;
7. Case Sensitive
SELECT c.id, c.name, c.ageFROM t_regcustomer cWHERE c.name REGEXP BINARY '^s'ORDER BY c.age ASC;
8. Regular Expression or operation
SELECT c.id, c.name, c.ageFROM t_regcustomer cWHERE 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.
SELECT c. id, c. name, c. ageFROM t_regcustomer cWHERE c. name regexp binary 'peng [123] 'order BY c. name ASC;
[1-9] match 1, 2, 3,... 8, 9
SELECT c. id, c. name, c. ageFROM t_regcustomer cWHERE 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 《MySQL required68-page Regular Expression