数据库修改字段 将空格分隔 转成 json格式

建表语句

CREATE TABLE `demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `seller_address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

初始数据格式

-- 空格 转 JSON格式
UPDATE 
	demo 
SET 
	seller_address = 
	CONCAT(
	'{\"name\": \"',
	SUBSTRING_INDEX(SUBSTRING_INDEX(seller_address,"  ", 2)," ", -1), 
	'\", \"phone\": \"', 
	SUBSTRING_INDEX(seller_address,"  ", -1), 
	'\", \"address\": \"', 
	SUBSTRING_INDEX(seller_address,"  ", 1),
	'\"}'
	) 
WHERE seller_address IS NOT NULL;

当前格式

-- JSON格式 转 空格格式
UPDATE
	demo
SET
	seller_address = 
	CONCAT(
	JSON_UNQUOTE ( seller_address -> '$.address' ),'  ',
	JSON_UNQUOTE ( seller_address -> '$.name' ), '  ',
	JSON_UNQUOTE ( seller_address -> '$.phone' )
	) 
WHERE seller_address IS NOT NULL;

数据回滚后