时间戳

首页 > 解决方案 > 使用 MySQL JSON 字段加入具有自定义字段的表

mysql - 使用 MySQL JSON 字段加入具有自定义字段的表

问题描述

因此，我制作了这个系统来为我正在开发的应用程序存储带有自定义字段的自定义对象。首先，我有 object_def，我在其中保存对象定义：

id   | name     | fields       
------------------------------------------------------------
101  | Group 1  | [{"name": "Title", "id": "AbCdE123"}, ...]
102  | Group 2  | [{"name": "Name", "id": "FgHiJ456"},  ...]

所以我们有 ID (INT)、名称 (VARCHAR) 和字段 (LONGTEXT)。在字段中是这样的对象字段：{id: string, type: string, name: string}[].

现在在对象表中，我有这个：

id      | object_def_id | object_values       
------------------------------------------------------------
235     | 101           | {"AbCdE123": "The Object", ... }
236     | 102           | {"FgHiJ456": "John Perez", ... }

其中 object_values 也是 LONGTEXT。使用该系统，我可以使用 JSON.parse() 在我的应用程序中的表格上显示对象。

现在我了解到 MySQL 中有一个 JSON 类型，我希望它用它来做查询和其他东西（我对此真的很陌生）。

我已将 LONGTEXT 更改为 JSON，现在我想做一个 SELECT 来显示如下结果：

#Select objects in group 1:

id   | group    | Title      | ... | other_custom_field
-------------------------------------------------------
235  | Group 1  | The Object | ... | other_custom_value

#Select objects in group 2:

id   | group    | Name       | ... | other_custom_field
-------------------------------------------------------
236  | Group 2  | John Perez | ... | other_custom_value

Id，然后是组名（我可以使用 INNER JOIN 执行此操作），然后是具有相应值的所有自定义字段。

这可能吗？我怎样才能做到这一点（希望不改变我的数据库结构）？我正在学习 MySQL、SQL 和数据库，所以我非常感谢你的帮助。谢谢！

标签： mysqljsondatabase

解决方案

我在您的设计中看到的问题：

  • JSON 格式不正确。

    [{name: 'Title', id: 'AbCdE123'}, ...]

    应该：

    [{"name": "Title", "id": "AbCdE123"}, ...]

    您应该使用 JSON 数据类型而不是 LONGTEXT，因为 JSON 至少会拒绝无效的 JSON 语法。

  • 根据数据设置列标题。您不能在 SQL 中执行此操作。在准备查询时，必须固定列和标题。您不能执行更改其自己的列标题的 SQL 查询。

  • 您的对象 def 有一个属性数组，但在 MySQL 5.7 中无法循环遍历 JSON 数组的“行”。您需要在 MySQL 8.0中使用JSON_TABLE() 。

  • 这将使您更接近能够查找对象值，但是您仍然必须将数据转换为您描述的结果集，每列中有一个属性，就好像数据以传统方式存储一样. 但是 SQL 不允许您在单个查询中进行动态透视。您不能创建一个基于它找到的数据动态增长自己的选择列表的 SQL 查询。

这一切都让我好奇...

为什么不以传统方式存储数据？

为每个对象类型创建一个表。每个属性向该表添加一列。这样你就可以得到列名。你得到列类型。你得到列约束——例如，你将如何模拟NOT NULL或UNIQUE在你当前的系统中？

如果您不想使用 SQL，请不要使用。还有其他选择，例如文档数据库或键/值数据库。但是不要通过使用它来实现Inner-Platform来折磨糟糕的 SQL 。

推荐阅读