MySQL 按关键字进行截取

秒速五厘米 2024-03-23 13:14 30阅读 0赞

#### 一、问题背景 ####

取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、查询效果 #####

![在这里插入图片描述][1e90cb0303724e938d730062dda8a0a0.png]

#### 三、补充说明 ####

**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);