mysql - 从连接的 MySQL 表中选择解码后的 JSON 数据

Question

如果我有两个包含 JSON 数据的表，你能告诉我如何从 MySQL 数据库中进行选择吗？其中之一具有以下结构:

Table Trees
(id, name, value) - three columns

其中包括以下数据

1,  trees, [{"name":"Oaktree","value":1,"target":null},{"name":"Appletree","value":2,"target":null},{"name":"Plumtree","value":3,"target":null}]
2,  length, [{"name":"10m","value":1,"target":null},{"name":"15m","value":2,"target":null},{"name":"20m","value":3,"target":null}]
3,  age, [{"name":"5y","value":1,"target":null},{"name":"10y","value":2,"target":null},{"name":"20y","value":3,"target":null}]

第二个表的结构如下:

Table SelectedTrees
(properties) - only one column

其中包括以下数据

[{"id":"1","value":["1","3"]},{"id":"2","value":["1", "2", "3"]},{"id":"3","value":["2"]}]

这意味着从 Trees 表中选择的数据。 selectedTrees 中 properties 列中的 id 对应于 Trees 表中的 id 列。我想从数据库中选择真实的(json_decoded)值，例如:

树 = 橡树、梅树

长度 = 10m、15m、20m

年龄 = 10 岁

我该怎么做？提前致谢。

一月

Answer 1

简而言之，这是不可能的。关系数据库是为快速比较它们可以索引的常量值而构建的。 JSON 对于 MySQL 来说只是一个字符串，任何类型的部分字符串匹配都会触发所谓的表扫描，当您获得大量数据时，这实际上会变得非常慢。

你可以让它像这样工作:

SELECT * FROM Trees
JOIN SelectedTrees
  ON properties LIKE CONCAT('"id":"', Trees.id, '"')

然而，这只是您不应该在任何生产系统中使用的一种 hack，我建议不要在测试系统中使用它。而是重构您的数据库，这样就永远不会有任何 JSON 您要在查询中匹配。将辅助数据存储为 JSON 很好，只需确保在插入之前提取 ID 和名称，然后将其插入到数据库表中的单独列中，这样数据库引擎就可以发挥其关系魔力。