update - mysql json默认值




MYSQL 5.7中的本机JSON支持:MYSQL中JSON数据类型的优缺点是什么? (3)

我最近遇到了这个问题,并总结了以下经验:

1,没有办法解决所有问题。 2,您应该正确使用JSON。

一种情况:

我有一个名为: CustomField 的表,它必须有两列: namefieldsname 是本地化的字符串,其内容应为:

{
  "en":"this is English name",
  "zh":"this is Chinese name"
   ...(other languages)
}

fields 应如下所示:

[
  {
    "filed1":"value",
    "filed2":"value"
    ...
  },
  {
    "filed1":"value",
    "filed2":"value"
    ...
  }
  ...
]

如您所见, namefields 都可以另存为JSON,并且可以使用!

但是,如果我经常使用该 name 搜索该表,该怎么办? 使用 JSON_CONTAINSJSON_EXTRACT ...? 显然,将其另存为JSON并不是一个好主意,我们应该将其保存到一个独立的表: CustomFieldName

从上述情况来看,我认为您应该牢记以下想法:

  1. 为什么MYSQL支持JSON?
  2. 为什么要使用JSON? 您的业​​务逻辑只需要这个吗? 还是还有别的?
  3. 永远不要偷懒

谢谢

在MySQL 5.7中,添加了用于 在JSON 表中存储 JSON数据 的新数据类型。 显然,这将是MySQL的巨大变化。 他们列出了一些好处

文档验证 -JSON列中只能存储有效的JSON文档,因此您可以自动验证数据。

有效访问 -更重要的是,当您将JSON文档存储在JSON列中时,它不会存储为纯文本值。 相反,它以优化的二进制格式存储,从而可以更快地访问对象成员和数组元素。

性能 -通过在JSON列中的值上创建索引来提高查询性能。 这可以通过虚拟列上的“功能索引”来实现。

便利性 -JSON列的附加内联语法使在SQL中集成文档查询变得非常自然。 例如(features.feature是JSON列): SELECT feature->"$.properties.STREET" AS property_street FROM features WHERE id = 121254;

哇 ! 它们包括一些很棒的功能。 现在,更容易操作数据。 现在可以在列中存储更复杂的数据。 因此,MySQL现在具有NoSQL的味道。

现在我可以想象对JSON数据的查询类似

SELECT * FROM t1
WHERE JSON_EXTRACT(data,"$.series") IN 
( 
SELECT JSON_EXTRACT(data,"$.inverted") 
FROM t1 | {"series": 3, "inverted": 8} 
WHERE JSON_EXTRACT(data,"$.inverted")<4 );

那我可以在几个json列中存储巨大的小关系吗? 好吗? 它破坏规范化了吗? 如果可能的话,我想它将像MySQL列中的NoSQL一样 。 我真的很想了解更多有关此功能的信息。 MySQL JSON数据类型的优缺点。


根据我的经验,至少在MySql 5.7中,JSON实现由于性能不佳而不太有用。 好吧,对于读取数据和验证来说还不错。 但是,使用MySql进行JSON修改的速度比使用Python或PHP慢10到20倍。 让我们想象一下非常简单的JSON:

{ "name": "value" }

假设我们必须将其转换为类似的内容:

{ "name": "value", "newName": "value" }

您可以使用Python或PHP创建简单的脚本,该脚本将选择所有行并逐一更新它们。 您不必为此进行一项大事务,因此其他应用程序可以并行使用该表。 当然,如果需要,您也可以进行一个巨大的事务,因此可以保证MySql将执行“全部或不执行”,但是其他应用程序很可能在事务执行期间无法使用数据库。

我有4000万行表,Python脚本会在3-4小时内更新它。

现在我们有了MySql JSON,因此我们不再需要Python或PHP,我们可以执行以下操作:

UPDATE `JsonTable` SET `JsonColumn` = JSON_SET(`JsonColumn`, "newName", JSON_EXTRACT(`JsonColumn`, "name"))

它看起来简单而出色。 但是,它的速度比Python版本慢10到20倍,并且它是单个事务,因此其他应用程序无法并行修改表数据。

因此,如果我们只想在4000万行表中复制JSON键,则在30-40个小时内根本不需要使用表。 它没有感觉。

关于读取数据,根据我的经验,通过 WHERE JSON_EXTRACT 直接访问JSON字段也非常慢(这比不带索引的列上带有 LIKE TEXT 慢得多)。 虚拟生成的列的执行速度要快得多,但是,如果我们事先知道我们的数据结构,则不需要JSON,而可以使用传统列。 当我们在真正有用的地方使用JSON时,即当数据结构未知或经常更改(例如,自定义插件设置)时,定期为任何可能的新列创建虚拟列似乎不是一个好主意。

Python和PHP像使JSON验证一样具有吸引力,因此是否需要在MySql端进行JSON验证是一个问题。 为什么不同时验证XML,Microsoft Office文档或检查拼写? ;)


SELECT * FROM t1
WHERE JSON_EXTRACT(data,"$.series") IN ...

在这样的表达式或函数中使用列会浪费使用索引来帮助优化查询的任何机会。 上面显示的查询被强制执行表扫描。

关于“有效访问”的说法具有误导性。 这意味着在查询检查了带有JSON文档的行之后,它可以提取一个字段,而不必解析JSON语法的文本。 但是仍然需要进行表格扫描来搜索行。 换句话说,查询必须检查每一行。

以此类推,如果我在电话簿中搜索名字为“比尔”的人,即使我的名字被突出显示以使其更快地发现它们,我仍然必须阅读电话簿中的每一页。

MySQL 5.7允许您在表中定义虚拟列,然后在虚拟列上创建索引。

ALTER TABLE t1
  ADD COLUMN series AS (JSON_EXTRACT(data, '$.series')),
  ADD INDEX (series);

然后,如果您查询虚拟列,它可以使用索引并避免进行表扫描。

SELECT * FROM t1
WHERE series IN ...

或者,即使您查询虚拟列所基于的确切表达式(如在原始查询中一样),它也可以使用索引。

很好,但是有点遗漏了使用JSON的意义。 使用JSON的吸引力在于,它允许您添加新属性,而无需执行ALTER TABLE。 但是事实证明,如果要在索引的帮助下搜索JSON字段,则无论如何都必须定义一个额外的(虚拟)列。

但是,您不必为JSON文档中的 每个 字段定义虚拟列和索引,而只需定义要搜索或排序的列和索引。 JSON中可能还有其他属性,您只需要将它们提取到选择列表中,如下所示:

SELECT JSON_EXTRACT(data, '$.series') AS series FROM t1
WHERE <other conditions>

我通常会说这是在MySQL中使用JSON的最佳方法。 仅在选择列表中。

当您在其他子句(JOIN,WHERE,GROUP BY,HAVING,ORDER BY)中引用列时,使用常规列而不是JSON文档中的字段会更有效。

我在2018年4月的Percona Live会议上发表了名为“ 如何在MySQL错误 使用JSON的JSON” 的演讲。我将在秋季在Oracle Code One上更新并重复该演讲。

JSON还有其他问题。 例如,在我的测试中,JSON文档所需的存储空间是存储相同数据的常规列的2-3倍。

MySQL正在积极地推广其新的JSON功能,主要目的是劝说人们不要迁移到MongoDB。 但是像MongoDB这样的面向文档的数据存储从根本上讲是一种非关系式的数据组织方式。 它不同于关系型。 我并不是说一个比另一个更好,这只是一种不同的技术,适用于不同类型的查询。

当JSON使查询更高效时,您应该选择使用JSON。

不要仅仅因为一项新技术或为了时尚而选择一项技术。





nosql