Mysql CONCAT，GROUP_CONCAT

-- 初始化临时表以及数据
CREATE TEMPORARY TABLE IF NOT EXISTS data1
(
	Id INT NOT NULL,
	L1 VARCHAR(36) NULL,
	L2 VARCHAR(36) NULL,
	adt_redundancy VARCHAR(255) NULL
);

CREATE TEMPORARY TABLE IF NOT EXISTS templancode
(
	CultureCode VARCHAR(20) not NULL
);

CREATE TEMPORARY TABLE IF NOT EXISTS templanlike
(
	itemid VARCHAR(36) NULL,
	CultureCode VARCHAR(20) NULL,
	ItemContent VARCHAR(100) NULL
);

-- 语言种类表
TRUNCATE TABLE templancode;
-- 多语言表
TRUNCATE TABLE templanlike;
-- 业务数据表
TRUNCATE TABLE data1;

INSERT INTO templancode VALUES('zhCN'),('enUS'),('deDE');
INSERT INTO templanlike VALUES('z1','zhCN','语文'),('z1','enUS','Chinese'),('z1','deDE','Sprachen.');
INSERT INTO templanlike VALUES('z2','zhCN','数学'),('z2','enUS','Math'),('z2','deDE','Mathe.');
INSERT INTO templanlike VALUES('z3','zhCN','英语'),('z3','enUS','English'),('z3','deDE','Englisch.');

INSERT INTO data1(id,L1,L2) VALUES(1,'z1','z2'),(2,'z3',''),(3,'','');

SELECT * FROM templancode; SELECT * FROM templanlike; SELECT * FROM data1;

-- 将Key为z1的多语言内容转换为Json字符
SELECT GROUP_CONCAT(CONCAT('"',p3.CultureCode,'":"',
	 (SELECT p4.ItemContent FROM templanlike AS p4 
		WHERE p4.CultureCode=p3.CultureCode AND p4.itemid='z1'),'"')) as z1lan
FROM templancode as p3;

-- 拼接业务表字段L1的多语言内容
SELECT p0.Id,p0.L1,p0.L2,
		(SELECT GROUP_CONCAT(CONCAT('"',p1.CultureCode,'":"',
	 (SELECT p2.ItemContent FROM templanlike AS p2 
		WHERE p2.CultureCode=p1.CultureCode AND p2.itemid=p0.L1),'"'))
		FROM templancode as p1) as L1Language
FROM data1 as p0

-- 更新多语言冗余字段
UPDATE data1 SET adt_redundancy=CONCAT(
'"L1":{',
(SELECT GROUP_CONCAT(CONCAT('"',p1.CultureCode,'":"',
	 ifnull((SELECT p2.ItemContent FROM templanlike AS p2 
		WHERE p2.CultureCode=p1.CultureCode AND p2.itemid=data1.L1),''),'"'))
		FROM templancode as p1)
,'}'
);

UPDATE data1 SET adt_redundancy=CONCAT(adt_redundancy,
',"L2":{',
(SELECT GROUP_CONCAT(CONCAT('"',p1.CultureCode,'":"',
	 ifnull((SELECT p2.ItemContent FROM templanlike AS p2 
		WHERE p2.CultureCode=p1.CultureCode AND p2.itemid=data1.L2),''),'"'))
		FROM templancode as p1)
,'}'
);

SELECT * from data1;

-- 改代码块涉及到的临时表不能在一同语句中重复使用，可以创建实体表进行执行
/*
UPDATE data1 SET adt_redundancy=CONCAT(
'{"L1":',
(SELECT GROUP_CONCAT(CONCAT('"',p1.CultureCode,'":"',
	 (SELECT p2.ItemContent FROM templanlike AS p2 
		WHERE p2.CultureCode=p1.CultureCode AND p2.itemid=data1.L1),'"'))
		FROM templancode as p1)
,'}',
'{"L2":',
(SELECT GROUP_CONCAT(CONCAT('"',p3.CultureCode,'":"',
	 (SELECT p4.ItemContent FROM templanlike AS p4 
		WHERE p4.CultureCode=p1.CultureCode AND p4.itemid=data1.L2),'"'))
		FROM templancode as p3)
,'}'
);
*/