MySQL 关键字查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50714
 Source Host           : 127.0.0.1
 Source Database       : test

 Target Server Type    : MySQL
 Target Server Version : 50714
 File Encoding         : utf-8

 Date: 03/08/2017 22:41:15 PM
*/

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;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

// 查询以 90% 结尾的数据
SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  TRUE
  AND a.`val` LIKE '%90/%' ESCAPE '/';

// 查询以 80 开头匹配任意字符串并以 % 结尾的数据
SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  TRUE
  AND a.`val` LIKE '80%/%' ESCAPE '/';

// 查询以 %88 开头的数据
SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  TRUE
  AND a.`val` LIKE '/%88%' ESCAPE '/';

// 查询以 _php 开头的数据
SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  TRUE
  AND a.`val` LIKE '/_php%' ESCAPE '/';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  TRUE
  AND a.`key` LIKE 'ma%';

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  TRUE
  AND a.`key` REGEXP '^ma';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  TRUE
  AND a.`key` LIKE '%ming';

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  TRUE
  AND a.`key` REGEXP 'ming$';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  TRUE
  AND a.`key` LIKE '%xiao%';

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  TRUE
  AND a.`key` REGEXP 'xiao';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  TRUE
  AND a.`key` LIKE '___xiao___';

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  TRUE
  AND a.`key` REGEXP '^[a-zA-Z]{3}xiao[a-zA-Z]{3}$';