如何在MySQL中使用group-concat创建JSON格式？
您可以使用MySQL的group_concat()函数创建JSON格式。语法如下-
SELECT yourColumnName1,
GROUP_CONCAT(CONCAT('{anytName:"', yourColumnName, '",
anyName:"',yourColunName,'"}')) anyVariableName
from yourTableName
group by yourColumnName1;为了理解上述语法，让我们首先创建一个表。创建表的查询如下-
mysql> create table JsonFormatDemo -> ( -> UserId int, -> UserName varchar(100), -> UserEmail varchar(100) -> );
使用insert命令在表中插入一些记录。插入记录的查询如下-
mysql> insert into JsonFormatDemo values(101,'John','John@gmail.com'); mysql> insert into JsonFormatDemo values(101,'Bob','John@gmail.com'); mysql> insert into JsonFormatDemo values(102,'Carol','Carol@gmail.com'); mysql> insert into JsonFormatDemo values(103,'Sam','Sam@gmail.com');
使用select语句显示表中的所有记录。查询如下-
mysql> select *from JsonFormatDemo;
输出结果
+--------+----------+-----------------+ | UserId | UserName | UserEmail | +--------+----------+-----------------+ | 101 | John | John@gmail.com | | 101 | Bob | John@gmail.com | | 102 | Carol | Carol@gmail.com | | 103 | Sam | Sam@gmail.com | +--------+----------+-----------------+ 4 rows in set (0.00 sec)
使用group_concat()函数创建JSON格式的查询-
mysql> select UserId,
-> GROUP_CONCAT(CONCAT('{Name:"', UserName, '", Email:"',UserEmail,'"}')) JsonFormat
-> from JsonFormatDemo
-> group by UserId;输出结果
+--------+----------------------------------------------------------------------------+
| UserId | JsonFormat |
+--------+----------------------------------------------------------------------------+
| 101 | {Name:"John", Email:"John@gmail.com"},{Name:"Bob", Email:"John@gmail.com"} |
| 102 | {Name:"Carol", Email:"Carol@gmail.com"} |
| 103 | {Name:"Sam", Email:"Sam@gmail.com"} |
+--------+----------------------------------------------------------------------------+
3 rows in set (0.00 sec)