mysql 字段json行转列

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for keyid
-- ----------------------------
DROP TABLE IF EXISTS `keyid`;
CREATE TABLE `keyid` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of keyid
-- ----------------------------
INSERT INTO `keyid` VALUES ('0');
INSERT INTO `keyid` VALUES ('1');
INSERT INTO `keyid` VALUES ('2');
INSERT INTO `keyid` VALUES ('3');
INSERT INTO `keyid` VALUES ('4');
INSERT INTO `keyid` VALUES ('5');
INSERT INTO `keyid` VALUES ('6');
INSERT INTO `keyid` VALUES ('7');
INSERT INTO `keyid` VALUES ('8');
INSERT INTO `keyid` VALUES ('9');

执行sql 创建表

sql字段表的三条数据  json 字段名为 AREA 第二条是2个省市区 要拆分展示四列

[{"cityCode":"0472","cityName":"包头市","districtCode":"047202","districtName":"昆都仑区","provinceCode":"040","provinceName":"内蒙古"}]

[{"cityCode":"011","cityName":"北京市","districtCode":"01001","districtName":"东城区","provinceCode":"010","provinceName":"北京市"},{"cityCode":"0310","cityName":"邯郸市","districtCode":"031002","districtName":"丛台区","provinceCode":"060","provinceName":"河北省"}]

[]

select 
	a.id,a.dealer_id
	replace(JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ), '$[0].provinceCode'),'"','')  as authProvinceCode,
  replace(JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ), '$[0].provinceName'),'"','') as authProvinceName,
  replace(JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ), '$[0].cityCode'),'"','') as authCityCode,
  replace(JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ), '$[0].cityName'),'"','') as authCityName,
  replace(JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ), '$[0].districtCode'),'"','') as authDistrictCode,
  replace(JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ), '$[0].districtName'),'"','') as authDistrictName 
from 
	( select tt.id,tt.dealer_id,
	 (case tt.AREA
     when '[]'  then ( '{"cityCode":"","cityName":"","districtCode":"","districtName":"","provinceCode":"","provinceName":""}')
     else ( replace(replace(replace(tt.AREA,"},{","};{"),"]",""),"[",""))
     end 
	 )as jsonarr
from ltx_dealer_auth_info tt where tt.dealer_id = 'f66b4c11b2dd43a3bb65c98c3e818f5c' and tt.del_flag='0') a
join keyid b 
  -- < sql中相当于小于 如果放到XML文件中
  -- on b.id < ( length( a.jsonarr ) - length( replace ( a.jsonarr, ";", "" ) ) + 1 )
on b.id<( length( a.jsonarr ) - length( replace ( a.jsonarr, ";", "" ) ) + 1 )
where a.jsonarr != ''