Mysql CONCAT，GROUP_CONCAT

最新推荐文章于 2026-06-23 22:45:31 发布

原创最新推荐文章于 2026-06-23 22:45:31 发布 · 252 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #数据库 #database

Mysql 专栏收录该内容

6 篇文章

订阅专栏

本文深入探讨了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)
,'}'
);
*/