SELECT
SUBSTRING_INDEX( SUBSTRING_INDEX( '12,2,3,4,5,6,7,8,9,6', ',', id ), ',',- 1 ) AS num,
id
FROM
sequence_id
WHERE
id <= LENGTH(
REPLACE ( '1,2,3,4,5,6,7,8,9,10', ',', '' ));
DROP TABLE IF EXISTS `sequence_id`;
CREATE TABLE `sequence_id` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '序列id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of sequence_id
-- ----------------------------
INSERT INTO `sequence_id` VALUES (1);
INSERT INTO `sequence_id` VALUES (2);
INSERT INTO `sequence_id` VALUES (3);
INSERT INTO `sequence_id` VALUES (4);
INSERT INTO `sequence_id` VALUES (5);
INSERT INTO `sequence_id` VALUES (6);
INSERT INTO `sequence_id` VALUES (7);
INSERT INTO `sequence_id` VALUES (8);
MySQL中一個很好用的擷取字串的函式:substring_index。
用法規則:
substring_index(“待擷取有用部分的字串”,“擷取資料依據的字元”,擷取字元的位置N)
詳細說明:
首先,設待處理物件字串為“15,151,152,16”(雖然這裡指的不是iP,可以看作是IP來處理吧)
這裡擷取的依據是逗號:“,”
具體要擷取第N個逗號前部分的字元;
意思是:在字串中以逗號為索引,獲取不同索引位的字元。
舉例如下:
1. 取第一個逗號前的字串 :
root@localhost|iris>SELECT SUBSTRING_INDEX('15,151,152,16',',',1);
+----------------------------------------+
| SUBSTRING_INDEX('15,151,152,16',',',1) |
+----------------------------------------+
| 15 |
+----------------------------------------+
1 row in set (0.00 sec)
==>得到結果為: 15
2.擷取第二個逗號前面部分
root@localhost|iris>SELECT SUBSTRING_INDEX('15,151,152,16',',',2);
+----------------------------------------+
| SUBSTRING_INDEX('15,151,152,16',',',2) |
+----------------------------------------+
| 15,151 |
+----------------------------------------+
1 row in set (0.00 sec)
==>得到結果為:15,151
N可以為負數,表示倒數第N個索引字元後面的字串。有負號的時候,可以將整個字元倒過來看,依舊是第N個字元前面的部分。
1.擷取目標字串中最後一個含 “,” 位子的後的部分:
root@localhost|iris>SELECT SUBSTRING_INDEX('15,151,152,16',',',-1) as 'subrting';
+----------+
| subrting |
+----------+
| 16 |
+----------+
1 row in set (0.00 sec)
==>得到結果為: 16
2.取倒數第2個逗號前那部分字串裡,最後逗號後面的部分
root@localhost|iris>SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('15,151,152,16',',',2),',',-1) as 'subrting';
+----------+
| subrting |
+----------+
| 151 |
+----------+
1 row in set (0.00 sec)
==>得到結果為: 151
3.取倒數第二個逗號後面部分字串,再去這部分裡第一個都號前的部分:
root@localhost|iris>SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('15,151,152,16',',',-2),',',1) as 'subrting';
+----------+
| subrting |
+----------+
| 152 |
+----------+
1 row in set (0.00 sec)
==> 得到結果為:152