如何将mysql表上的值转换成带触发器的json文件?

pu82cl6c  于 2021-06-17  发布在  Mysql
关注(0)|答案(2)|浏览(290)

我使用phpmyadmin来处理mysql数据库。我有一个表调用nodejs,这个表的列调用id和number。我尝试在mysql上使用以下触发器创建json文件:

SELECT id, number FROM NodeJS
INTO OUTFILE '/opt/brackets/www/node_modules/server/prova.json'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

但是prova.json文件的结果是:

"1","12"
"3","13.4"
"4","13.4"
"5","13.9"
"6","13.9"
"7","12.9"
"8","12.9"
"9","123"
"10","123"
"11","12345"
"12","12345"
"13","3"
"14","3"
"15","126"
"16","126"
"17","111"
"18","123"
"19","123"
"20","1"
"21","1"
"22","13.4"
"25","11"
"26","11"
"27","12342300"

因此,我想了解如何将其转换为真正的json文件,同时考虑到表的字段(如我前面所说)是id和number。
我感谢大家的关注。

66bbxpm5

66bbxpm51#

嗯,这不是要创建json文件。但也许下面的伎俩管用。使用 json_arrayagg() 以及 json_object() 创建json。然后将其导出到文件中,将所有分隔符设置为空字符串(或空白,json忽略)。

SELECT json_arrayagg(json_object('id', id, 'number', number))
       FROM nodejs
       INTO OUTFILE '/opt/brackets/www/node_modules/server/prova.json'
       FIELDS TERMINATED BY ''
       ENCLOSED BY ''
       LINES TERMINATED BY '';
c9x0cxw0

c9x0cxw02#

这就解决了您的问题,使用mysql conact、group\u concat和outfile export方法。

SELECT GROUP_CONCAT(CONCAT('{ "id": ' ,id, ', "number": "', number, '" } \r')) FROM nodejs
   into @result;
  SET @q = CONCAT('[ ', @result, ' ]');
  SELECT @q INTO OUTFILE 'e:/json_data.json';

您可以遵循相同的方法,添加、删除或修改所有字段,并且输出是完全有效的.json文件。

**注意:如果您尝试写入c:\驱动器,可能会出现权限问题,这就是我尝试写入e:\驱动器的原因。

相关问题