在MySQL上将JSON字符串作为字段数据

Question

我有这样的想法:使用PHP的json_encode将多维/非多维数组变成JSON字符串，并将数据存储到我的SQL数据库中。

例如，我有一个名为“users”的表。表“用户”有三个字段: id、name、data

使用php，我想获取用户John的数据：SELECT data FROM users WHERE name='john'

现在，“data”字段的值/文本将如下所示：{"gender":"male","birthday":"8-Jan-1991","country":"UK","city":"London"}

我将使用PHP的json_decode对'data‘字段进行解码，然后使用我自制的stdClass函数之一将stdClass对象转换为数组。然后我可以在我想要的任何地方展示约翰的信息：$user['data']['country']。

这使我从榛子中为国家、城市、生日等建立了额外的数据库字段，但安全吗？使用此方法在MySQL上存储数据是否有任何缺点或问题？

Answer 1

正确的转义()，但是我必须补充一点，这是XML比json更好的格式，因为它还允许您在查询中使用xml中的数据。

<?xml version="1.0" encoding="UTF-8" ?>
<user>
   <gender>male</gender>
   <birthday>8-Jan-1991</birthday>
   <country>UK</country>
   <city>London</city>
</user> 

选择

SELECT ExtractValue(data, '//gender') AS gender FROM users WHERE name='john' AND EXTRACTVALUE(data, '//country') != 'UK';

萃取值

Answer 2

如果我是您，我只需为数据集添加新列。

在MySQL字段中使用JSON并不坏。这让我省去了很多悲伤。但是它确实引入了一些开销，并限制了您可以从数据库引擎中使用的功能。经常操作SQL模式并不是最好的做法，但在不必对JSON对象进行解码时也是如此。

如果数据模式是相当静态的，就像您的示例一样，存储用户的性别、生日等，最好使用列。然后，您可以使用SQL快速、轻松地操作数据.排序、筛选、创建索引以进行更快的查找等等。由于数据模式是相当静态的，所以除了创建列的几分钟时间之外，您实际上并没有从JSON中获得任何东西。最后，在应用程序的生命周期中，您会在机器周期中损失更多的时间。

我在MySQL字段中使用JSON的地方是数据模式非常流畅的地方。作为一名测试工程师，这几乎是常态。例如，在我当前的一个项目中，目标度量(存储在MySQL中)的列表非常有规律地变化，这取决于正在处理的问题或正在调整的性能特性。对于开发工程师来说，这是一个常规的活动，他们要求新的度量标准，他们当然希望这一切都能被清晰地显示出来，并且能够快速地进行更改。因此，我没有每天使用SQL模式进行模糊处理，而是将静态模式(测试类型、日期、产品版本等)存储为列，但是将不断流动的测试结果数据存储为JSON对象。这意味着我仍然可以使用基于测试类型、版本、日期等的SQL语句来查询数据，但是在集成新的度量标准时，永远不需要触及表模式。为了显示实际的测试数据，我只需迭代结果并将JSON对象解码为数组，然后从那里开始。随着这个项目的扩展，我最终将实现memcached来缓存所有东西。

这也有一个副作用，就是将100+测试指标捆绑到一个文本blob中，整个blob由我zlib压缩而成，大约是原始大小的10%。这意味着我们节省了相当多的数据存储，因为我们已经有7位数的行了。