MySQL 列拆分多行

第一步：创建一张地方表,并插入数据

-- ----------------------------
-- Table structure for place
-- ----------------------------
DROP TABLE IF EXISTS `place`;
CREATE TABLE `place` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `country` char(50) NOT NULL,
  `city` char(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of place
-- ----------------------------
INSERT INTO `place` VALUES ('1', '中国', '北京,上海,天津,广州');

表内容如下：

第二步：拆分语句
 

SELECT
  id,
	substring_index(substring_index(a.city,',',b.help_topic_id + 1),',' ,- 1) as ader_code
FROM
	`place` as a
JOIN mysql.help_topic b ON b.help_topic_id < (
	(length(a.city) - length(REPLACE(a.city, ',', '')) + 1)
)

拆分结果如下：