MySQL学习笔记——正则表达式查询

匹配修饰符

• ^：用来匹配字符串的开始
• $：用来匹配字符串的结尾。
• []：方括号中的任何字符都可以匹配，例如[0-9a-z其他]可以匹配‘0-9’数字任意一个，小写字母‘a-z’任意一个，‘其’，‘他’。
• -：连接符用来表示字符串的范围，如上面的[0-9]
• +：表示匹配次数出现一个或多个。
• *：表示匹配该字符出现0个或多个，比如[0-9]*表示匹配0个或多个数字。
• ()：在圆括号中的内容将被看做一个整体。（ab）匹配ab。
• String{m}：整数m表示花括号前的字符串出现的次数。比如(ab){2}匹配abab，(ab){1,3}表示匹配1到3个‘ab’。
• |：表示或‘or’，比如’^张|^李’表示匹配张开头，或者李开头。
• 方括号‘[]’中也可以传递标准类名，表示特定的匹配规则，使用格式：[:character_class:]；详情如下：

字符类名称	含义
alnum	字母数字字符
alpha	字母字符
blank	空白字符
cntrl	控制字符
digit	数字字符
graph	图形字符
lower	小写字母字符
upper	大写字母字符
print	图形或空格字符
punct	标点字符
space	空格、制表符、换行符和回车符
xdigit	十六进制数字字符

注意事项：
正则表达式由具有特定含义的特殊字符构成；如果要匹配这些特殊字符，就需要用\为前导；-表示查找-，.表示查找.，\表示查找\；（转义特殊字符MySQL自己解释一个，正则表达式库解释一个）；另外，mysql正则表达式默认是不区分大小写的，如果希望区分大小写，可以使用binary关键字。比如：

select * from student where name regexp binary "^Zhang";	-- 查找名字是‘Zhang’开头的学生信息

代码示例：

CREATE TABLE IF NOT EXISTS student
(
    id       TINYINT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    score    TINYINT UNSIGNED
);

INSERT student(username, score)
VALUES ('king', 95),
       ('king1', 35),
       ('king2', 45),
       ('king3', 55),
       ('king4', 65),
       ('king5', 75),
       ('king6', 80),
       ('king7', 90),
       ('king8', 25);

-- 创建身份表 provinces
CREATE TABLE provinces(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
proName VARCHAR(10) NOT NULL UNIQUE
);
INSERT provinces(proName) VALUES('北京'),
('上海'),
('深圳'),
('广州'),
('重庆');

-- 创建用户表cms_user
CREATE TABLE cms_user(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT 'user@qq.com',
regTime INT UNSIGNED NOT NULL,
face VARCHAR(100) NOT NULL DEFAULT 'user.jpg',
proId TINYINT UNSIGNED NOT NULL COMMENT '用户所属省份'
);

INSERT cms_user(username,password,regTime,proId)

VALUES('张三','zhangsan',1419811708,1),
('张三丰','zhangsanfeng',1419812708,2),
('章子怡','zhangsan',1419813708,3),
('long','long',1419814708,4),
('ring','ring',1419815708,2),
('queen','queen',1419861708,3),
('king','king',1419817708,5),
('blek','blek',1419818708,1),
('rose','rose',1419821708,2),
('lily','lily',1419831708,2),
('john','john',1419841708,2);

-- ^：用来匹配字符串的开始
SELECT * FROM cms_user WHERE username REGEXP '^张';

-- $：用来匹配字符串的结尾
SELECT * FROM cms_user WHERE username REGEXP '丰$';



-- []：方括号中的任何字符都可以匹配，例如[0-9a-z其他]可以匹配‘0-9’数字任意一个，小写字母‘a-z’任意一个，‘其’，‘他’。
SELECT * FROM cms_user WHERE username REGEXP '[f-k]';
SELECT * FROM cms_user WHERE username REGEXP '[f-k0-9]';
SELECT * FROM cms_user WHERE username REGEXP '[f-k0-9张]';


-- -：连接符用来表示字符串的范围，如上面的[0-9]
SELECT * FROM cms_user WHERE username REGEXP '[f-k0-9]';

-- +：表示匹配次数出现一个或多个
SELECT * FROM cms_user WHERE username REGEXP 'e+';

-- *：表示匹配该字符出现0个或多个，比如[0-9]*表示匹配0个或多个数字
SELECT * FROM cms_user WHERE username REGEXP '[0-9]*';

-- ()：在圆括号中的内容将被看做一个整体。（ab）匹配ab
SELECT * FROM cms_user WHERE username REGEXP '(on)';

-- String{m}：整数m表示花括号前的字符串出现的次数。比如(ab){2}匹配abab，(ab){1,3}表示匹配1到3个‘ab’
SELECT * FROM cms_user WHERE username REGEXP '(e){1,3}';

-- |：表示或‘or’，比如’\^张\|\^李’表示匹配张开头，或者李开头。
SELECT * FROM cms_user WHERE username REGEXP 'ng|qu|te';

-- 方括号‘[]’中也可以传递标准类名，表示特定的匹配规则，使用格式：[:character_class:]；

SELECT * FROM cms_user WHERE username REGEXP '[:digit:]';
SELECT * FROM cms_user WHERE username REGEXP '[:alpha:]';