Mysql 把每条数据的某个值按特定符号分割成多条数据
一、先创建一个实例表
DROP TABLE IF EXISTS `test_a`;
CREATE TABLE `test_a` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`notice` varchar(20) DEFAULT NULL,
`descs` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- ----------------------------
-- Records of test_a
-- ----------------------------
INSERT INTO `test_a` VALUES ('1', 'a', 'A,B,R');
INSERT INTO `test_a` VALUES ('2', 'b', 'B,D');
INSERT INTO `test_a` VALUES ('3', 'c', 'S,W,M,L');
INSERT INTO `test_a` VALUES ('4', 'd', 'C,E,D');
DROP TABLE IF EXISTS `test_b`;
CREATE TABLE `test_b` (
`id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
-- ----------------------------
-- Records of test_b
-- ----------------------------
INSERT INTO `test_b` VALUES ('0');
INSERT INTO `test_b` VALUES ('1');
INSERT INTO `test_b` VALUES ('2');
INSERT INTO `test_b` VALUES ('3');
INSERT INTO `test_b` VALUES ('4');
INSERT INTO `test_b` VALUES ('5');
二、现要实现如下结果:
三、实现语句
INSERT INTO test_c SELECT
notice,
SUBSTRING_INDEX(
SUBSTRING_INDEX(a.descs, ",", b.id + 1),
"," ,- 1
) AS descs
FROM
test_a a
INNER JOIN test_b b ON b.id < (
LENGTH(a.descs) - LENGTH(REPLACE(a.descs, ",", "")) + 1
);