MySQL 关键字通过 LIKE 和正则查询。
SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;匹配模式
-- ------------------------------ Table structure for `test`-- ----------------------------DROP TABLE IF EXISTS `test`;CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键 ID', `key` varchar(45) NOT NULL COMMENT '键名', `val` varchar(45) NOT NULL COMMENT '键值', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8 COMMENT='测试表';
-- ------------------------------ Records of `test`-- ----------------------------BEGIN;INSERT INTO `test` VALUES ('1', 'chenyulong', '56'), ('2', 'maxiaoming', '33'), ('3', 'huxiaofeng', '23'), ('4', 'huangyi', '38'), ('5', 'MAYUN', '58'), ('6', 'CHENLONG', '64'), ('7', 'HUZHEN', '27'), ('8', 'LeiXiaoFei', '32'), ('9', 'Sunli', '42'), ('10', 'sunhong', '30'), ('11', 'Maoyouming', '33'), ('12', 'Maming', '43'), ('13', 'HUANGyu', '38'), ('14', 'maJINren', '34'), ('15', 'Lixiao', '33'), ('16', 'LIXIAOLI', '49'), ('17', 'sunYU', '50'), ('18', 'Liaihua', '54'), ('19', 'huXIaoYang', '44'), ('20', 'chenxiaoXIAO', '34'), ('21', 'wangjun', '90%'), ('22', 'zhaoqian', '_mysql'), ('23', 'wangwu', '%88'), ('24', 'lixiang', 'mysql_'), ('25', 'lianxiang', '_php'), ('26', 'lianyue', 'php_'), ('27', 'houxiaojun', '80%'), ('28', 'suNxiaOhua', '_phper'), ('29', 'jieXiaoguo', 'mysqler_'), ('30', 'sunming', '%66'), ('31', 'yingzheng', '%888'), ('32', 'piaojinhui', '900%'), ('33', 'jinzhengnan', '800%'), ('34', 'quanzhilong', '%666'), ('35', 'zhangquan', '__php'), ('36', 'zhangyu', '__mysql'), ('37', 'liyifeng', 'mysqler__');COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
SQL 的模式缺省是忽略大小写的。
SQL 匹配模式
-
%: 表示任意个（包括 0 个）或多个字符，可匹配任意类型和长度的字符
-
_: 表示任意单个字符，匹配单个任意字符，它常用来限制表达式的字符长度语句
字符转义
// 查询以 90% 结尾的数据SELECT a.`id`, a.`key`, a.`val`FROM test.test AS aWHERE TRUE AND a.`val` LIKE '%90/%' ESCAPE '/';
// 查询以 80 开头匹配任意字符串并以 % 结尾的数据SELECT a.`id`, a.`key`, a.`val`FROM test.test AS aWHERE TRUE AND a.`val` LIKE '80%/%' ESCAPE '/';
// 查询以 %88 开头的数据SELECT a.`id`, a.`key`, a.`val`FROM test.test AS aWHERE TRUE AND a.`val` LIKE '/%88%' ESCAPE '/';
// 查询以 _php 开头的数据SELECT a.`id`, a.`key`, a.`val`FROM test.test AS aWHERE TRUE AND a.`val` LIKE '/_php%' ESCAPE '/';
正则匹配模式
-
.: 匹配任意单个字符
-
?: 匹配前面的子表达式 0 次或 1 次
-
+: 匹配前面的子表达式 1 次或多次
-
*: 匹配前面的子表达式 0 次或多次
-
^: 表示匹配开始位置
-
$: 表示匹配结束位置
-
[]: 表示一个集合
-
{}: 表示重复的次数
查询以 ma 字符串开头的数据
SELECT a.`id`, a.`key`, a.`val`FROM test.test AS aWHERE TRUE AND a.`key` LIKE 'ma%';
SELECT a.`id`, a.`key`, a.`val`FROM test.test AS aWHERE TRUE AND a.`key` REGEXP '^ma';
查询以 ming 字符串结尾的数据
SELECT a.`id`, a.`key`, a.`val`FROM test.test AS aWHERE TRUE AND a.`key` LIKE '%ming';
SELECT a.`id`, a.`key`, a.`val`FROM test.test AS aWHERE TRUE AND a.`key` REGEXP 'ming$';
查询包含 xiao 字符串的数据
SELECT a.`id`, a.`key`, a.`val`FROM test.test AS aWHERE TRUE AND a.`key` LIKE '%xiao%';
SELECT a.`id`, a.`key`, a.`val`FROM test.test AS aWHERE TRUE AND a.`key` REGEXP 'xiao';
查询长度为 10, 中间包含 xiao 字符串的数据
SELECT a.`id`, a.`key`, a.`val`FROM test.test AS aWHERE TRUE AND a.`key` LIKE '___xiao___';
SELECT a.`id`, a.`key`, a.`val`FROM test.test AS aWHERE TRUE AND a.`key` REGEXP '^[a-zA-Z]{3}xiao[a-zA-Z]{3}$';
