MySQL 按关键字进行截取

MySQL 按关键字进行截取

---

 

问题背景

取MySQL数据表中某个字段中的IP信息。

如：t_log 表中的 user_ip 字段值为 {"username":"miracle","ip":"110.230.128.186"}，取出IP信息 110.230.128.186。

建表和初始化SQL语句，如下：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_log
-- ----------------------------
DROP TABLE IF EXISTS `t_log`;
CREATE TABLE `t_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `user_ip` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_log
-- ----------------------------
INSERT INTO `t_log` VALUES (1, 'USER', '{\"username\":\"miracle\",\"ip\":\"110.230.128.186\"}');
INSERT INTO `t_log` VALUES (2, 'VM', '{\"username\":\"miracle\",\"ip\":\"110.230.128.175\"}');
INSERT INTO `t_log` VALUES (3, 'USER', '{\"username\":\"miracle\",\"ip\":\"110.230.128.187\"}');

SET FOREIGN_KEY_CHECKS = 1;

 

实现方案

通过对相对有规律的数据进行分析，使用MySQL自带的函数 SUBSTRING_INDEX ，根据IP两侧的双引号"，对IP信息进行截取。

1、查询SQL

SELECT
    user_ip,
    SUBSTRING_INDEX( SUBSTRING_INDEX( user_ip, '"', - 2 ), '"', 1 ) AS ip 
FROM
    t_log 
WHERE
    event_type = 'USER';

 

2、查询效果

 

补充说明

SUBSTRING_INDEX(str, delim, count)，即：SUBSTRING_INDEX(被截取字符串，关键字，关键字出现的次数)

1、截取第二个“.”之前的所有字符
SELECT SUBSTRING_INDEX('www.cnblogs.com', '.', 2);
结果为：www.cnblogs

2、截取倒数第二个“.”之后的所有字符
SELECT SUBSTRING_INDEX('www.cnblogs.com', '.', -2);
结果为：cnblogs.com

3、如果关键字不存在，则返回整个字符串
SELECT SUBSTRING_INDEX('www.cnblogs.com', 'miracle', 1);
结果为：www.cnblogs.com

 

额外示例

根据“.”取 192.168.127.247 的相关信息。

1、从左往右查，取第一个“.”左边的字符串
SELECT SUBSTRING_INDEX("192.168.127.247", '.', 1) as str;
结果为：192

2、从左往右查，取第二个“.”左边的字符串
SELECT SUBSTRING_INDEX("192.168.127.247", '.', 2) as str;
结果为：192.168

3、从右往左查，取第一个“.”右边的字符串
SELECT SUBSTRING_INDEX("192.168.127.247", '.', -1) as str;
结果为：247

4、从右往左查，取第二个“.”右边的字符串
SELECT SUBSTRING_INDEX("192.168.127.247", '.', -2) as str;
结果为：127.247