将MySQL查询结果转换为JSON

Question

将MySQL查询结果转换为JSON

3

我正在从事的项目需要保存所有的数据库操作。因此，当添加新用户时，我必须记录日期、操作类型（如“插入”、“更新”、“删除”）和所有用户数据。该项目处于开发阶段，因此User表中的列正在变化。

我的计划是从Users表中选择新用户数据，并将它们作为JSON列插入到UserLog表中。

是否可以将SELECT * FROM table_name转换为JSON格式？我知道可以使用JSON_OBJECT函数将分离的列转换为JSON，但正如我上面提到的，这些列是浮动的，所以每次更改主表时都必须更改JSON_OBJECT名称。而且有很多表！

它应该像这样工作：

CREATE TABLE Users (
    id INT(1) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstName VARCHAR(30) NOT NULL,
    lastName VARCHAR(30) NOT NULL,
    email VARCHAR(50),
)

查询：

SELECT * FROM Users;

请返回：

[
    {
        "id": 1,
        "firstName": "Lucas",
        "lastName": "Smith",
        "email": "lucas@def.com"
    },
    {
        "id": 2,
        "firstName": "Ben",
        "lastName": "Kovalsky",
        "email": "ben@def.com"
    },
    ...
]

有没有简单的解决方案来解决这个问题？如果没有，你记录数据库操作的策略是什么？

- LukaszTaraszka

3个回答

2

我知道这是一个旧帖子，但如果仍然面临这个问题的任何人，有一种方法可以将结果集转换为json，而无需知道列名。关键是要将列的名称以像'column_1'，column_1，'column_2'，column_2，...这样的字符串形式获取并在准备好的查询中使用此字符串。

SET @column_name_string_for_query = "";
  
SHOW COLUMNS
FROM your_table_name
WHERE @column_name_string_for_query := TRIM(", " FROM CONCAT("'", Field, "', ", Field, ", ", @column_name_string_for_query));

SET @query_string = concat("
  SELECT JSON_ARRAYAGG(JSON_OBJECT(", @column_name_string_for_query, ")) 
  FROM your_table_name"
);
PREPARE statement FROM @query_string;
EXECUTE statement;
DEALLOCATE PREPARE statement;

你也可以从 INFORMATION_SCHEMA.COLUMNS 获取列名，但这仅适用于非临时表。上面的解决方案适用于临时表和普通表。

为了方便使用，你还可以将此保存为存储过程。

- Mark Longhurst

0

通常将输出转换为JSON或任何其他格式是由编程语言或您的mySQL IDE来完成的工作，但也有一种方法可以从mySQL中实现。

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-json-output.html

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-json-wrapping.html

直接从文档中：

MySQL  localhost:33060+ ssl  world_x  JS > shell.options.set('resultFormat','json')
MySQL  localhost:33060+ ssl  world_x  JS > session.sql("select * from city where countrycode='AUT'")
{
    "ID": 1523,
    "Name": "Wien",
    "CountryCode": "AUT",
    "District": "Wien",
    "Info": {
        "Population": 1608144
    }
}
{
    "ID": 1524,
    "Name": "Graz",
    "CountryCode": "AUT",
    "District": "Steiermark",
    "Info": {
        "Population": 240967
    }
}
{
    "ID": 1525,
    "Name": "Linz",
    "CountryCode": "AUT",
    "District": "North Austria",
    "Info": {
        "Population": 188022
    }
}
{
    "ID": 1526,
    "Name": "Salzburg",
    "CountryCode": "AUT",
    "District": "Salzburg",
    "Info": {
        "Population": 144247
    }
}
{
    "ID": 1527,
    "Name": "Innsbruck",
    "CountryCode": "AUT",
    "District": "Tiroli",
    "Info": {
        "Population": 111752
    }
}
{
    "ID": 1528,
    "Name": "Klagenfurt",
    "CountryCode": "AUT",
    "District": "Kärnten",
    "Info": {
        "Population": 91141
    }
}
6 rows in set (0.0031 sec)

此外，还可以添加从5.7+版本开始提供的JSON_OBJECT，请参考这里的答案。

mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
+-----------------------------------------+
| JSON_OBJECT('id', 87, 'name', 'carrot') |
+-----------------------------------------+
| {"id": 87, "name": "carrot"}            |
+-----------------------------------------

- Elzo Valugi

这是一个展示选项的shell，但我计划将其作为查询结果获取，就像在JSON_OBJECT中一样。是否有办法将select all作为JSON字符串返回，并将其插入到另一个表的行中？ - LukaszTaraszka

@LukaszTaraszka 还有这个：https://dev59.com/yVgR5IYBdhLWcg3wEp8v - Elzo Valugi

谢谢，Elzo，但这与下面@adiDX写的一样。我有50多个随时间变化的列，所以我不能使用JSON_XXX的解决方案 - 列名必须在其中指定。我以为在MySQL中可以原生地做到这一点。最后，我在我的数据库提供商源代码中实现了它。 - LukaszTaraszka

链接的文档是错误的。mysql命令没有"--result-format"选项。`$ mysql -e "select * from gdprt_trunk.uid_delete_deleteduidbatch;" --result-format=json mysql: [ERROR] 未知变量'result-format=json'。 - Matthew Scouten

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- adiDX · Accepted Answer

我不太了解MySQL，因为我已经转到PostgreSQL，但我发现最近的MySQL（从版本8开始）支持JSON：

SELECT JSON_ARRAYAGG(
  JSON_OBJECT(
    'id', `id`,
    'firstName', `firstName`,
    'lastName', `lastName`,
    'email', `email`
  )
)
FROM Users;

应该可以正常工作。

编辑，来源:

请注意，这些链接提供了关于MySQL中JSON值和函数的详细信息。