MYSQL生成json字符串

在mysql中生成json字符串

 

假设表A，字段A1 int,A2 varchar,A3 text,A4 bool。

 

生成单条记录的json："A":{"A1":A1Value,"A2":"A2Value","A3":"A3Value","A4":A4Value}

 

 

SET @JSONSTRING='';

 

SELECT CONCAT(

          "/"A/":"

          CONCAT("{/"A1/":",A1,","),

          CONCAT("/"A2/":/"",A2,"/","),

          CONCAT("/"A3/":/"",A3,"/","),

          CONCAT("/"A4/":",A4,"}")

    ) INTO @JSONSTRING;

 

SELECT @JSONSTRING;

 

得到的@JSONSTRING："A":{"A1":A1Value,"A2":"A2Value","A3":"A3Value","A4":A4Value}。

 

 

 

生成多条记录的json：

 

"A":{[{"A1":A1Value1,"A2":"A2Value1","A3":"A3Value1","A4":A4Value1}],

 

[{"A1":A1Value,"A2":"A2Value","A3":"A3Value","A4":A4Value}]}

 

 

 

 

SET @JSONSTRING='';

 

SELECT CONCAT(

          "/"A/":{"

          GROUP_CONCAT(

              CONCAT("{/"A1/":",A1,","),

              CONCAT("/"A2/":/"",A2,"/","),

              CONCAT("/"A3/":/"",A3,"/","),

              CONCAT("/"A4/":",A4,"}")

           ),

           "}"

   & nbsp;) INTO @JSONSTRING;

 

SELECT @JSONSTRING;

得到的@JSONSTRING：

"A":{[{"A1":A1Value1,"A2":"A2Value1","A3":"A3Value1","A4":A4Value1}],

 

[{"A1":A1Value,"A2":"A2Value","A3":"A3Value","A4":A4Value}]}