直接查詢

---切割前

select id,content from test_split;

1 12,13,14

2 21,25

3 33

--切割后

1 12

1 13

1 14

2 21

2 25

3 33

--執行sql

SELECT a.id,SUBSTRING_INDEX(SUBSTRING_INDEX(a.content,',',b.help_topic_id+1),',',-1) AS num

FROM test_split a join

mysql.help_topic b on b.help_topic_id < LENGTH(a.content)-LENGTH(REPLACE(a.content,',',''))+1;

?

?

存儲過程游標入庫

---切割前

select id,content from test_split;

1 12,13,14

2 21,25

3 33

--切割后

select id,content from test_split_open;

1 12

1 13

1 14

2 21

2 25

3 33

?

-------------創建表和插入測試數據

-- ----------------------------

-- Table structure for test_split

-- ----------------------------

DROP TABLE IF EXISTS `test_split`;

CREATE TABLE `test_split` (

`id` int(11) NOT NULL,

`content` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

?

-- ----------------------------

-- Records of test_split

-- ----------------------------

INSERT INTO `test_split` VALUES (1, '12,13,14');

INSERT INTO `test_split` VALUES (2, '21,25');

INSERT INTO `test_split` VALUES (3, '33');

?

-- ----------------------------

-- Table structure for test_split_open

-- ----------------------------

DROP TABLE IF EXISTS `test_split_open`;

CREATE TABLE `test_split_open` (

`id` int(11) NOT NULL,

`content` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

?

-------------切割存儲過程

CREATE PROCEDURE `p_Split`() BEGINDECLARE sid int;DECLARE scontent varchar(255);DECLARE s int DEFAULT 0;DECLARE ct CURSOR FOR select a.id,a.content from test_split a;DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;OPEN ct; FETCH ct into sid,scontent;while s <> 1 DOinsert into test_split_open(`id`,`content`) SELECT sid,SUBSTRING_INDEX(SUBSTRING_INDEX(scontent,',',help_topic_id+1),',',-1) AS num FROM mysql.help_topic WHERE help_topic_id < LENGTH(scontent)-LENGTH(REPLACE(scontent,',',''))+1;FETCH ct INTO sid,scontent;end WHILE;CLOSE ct; END

--執行存儲過程

call p_split();

總結

以上是生活随笔為你收集整理的MySQL 表一列逗号分隔字段，按逗号切割的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。