将异构json对象存储在同一个MySQL表中？

What is the recommended way of storing heterogeneous json strings into the same MySQL table? I've got some other tables in this MySQL database that store information other than JSON objects, so I would like to store these JSON objects in this same DB in the best recommended way.

将异构json字符串存储到同一MySQL表中的推荐方法是什么?我在这个MySQL数据库中有一些其他表来存储除JSON对象之外的信息,所以我想以最佳推荐的方式将这些JSON对象存储在同一个DB中。

I want to store json strings from a Perl script into a MySQL table that not only won't have the same values, but also will have different hierarchical structures in the different json objects. I am considering storing them as either strings or blobs, together with some minimal metadata for each entry. E.g.:

我想将来自Perl脚本的json字符串存储到MySQL表中,该表不仅不具有相同的值,而且在不同的json对象中也将具有不同的层次结构。我正在考虑将它们存储为字符串或blob,以及每个条目的一些最小元数据。例如。:

CREATE TABLE `entry` (
  `entry_id`               int(10) unsigned NOT NULL AUTO_INCREMENT,
  `file`                   varchar(1023)    DEFAULT NULL,
  `json`                   blob             DEFAULT NULL,
  `update_timestamp`       TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`entry_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Are there any tools or libraries I should consider before I do this straight on with Perl and MySQL? I wonder if there is anything out there that does the reverse of what DBIx::JSON does for SELECT queries from MySQL...

在使用Perl和MySQL直接进行此操作之前,是否有任何工具或库需要考虑?我想知道是否有任何东西与DBIx :: JSON对MySQL的SELECT查询所做的相反...

2 个解决方案

#1

Json objects in the DB are perfectly fine until you don't want to query the content of it.

数据库中的Json对象完全没问题,直到您不想查询它的内容为止。

The storage type however is worth the discussion. I would compress either the whole ROW in InnoDB with ( ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 ) or doing it in the application compressing only the JSON and put it in binary format.

然而,存储类型值得讨论。我会使用(ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE = 8)压缩InnoDB中的整个ROW,或者在仅压缩JSON的应用程序中进行压缩,并将其放入二进制格式。

With the first option (application handles TEXT, MySQL handles compression) I would prefer the json column to by text type (TEXT, LONGTEXT, MEDIUMTEXT, TINYTEXT).

使用第一个选项(应用程序处理TEXT,MySQL处理压缩)我更喜欢按文本类型(TEXT,LONGTEXT,MEDIUMTEXT,TINYTEXT)的json列。

With the second version (application handles compression, MySQL only sees binary) I would of course use blob format (TINYBLOB, BLOB, MEDIUMBLOB or LONGBLOB).

使用第二个版本(应用程序处理压缩,MySQL只看到二进制)我当然会使用blob格式(TINYBLOB,BLOB,MEDIUMBLOB或LONGBLOB)。

Both are valid options. It really depends on your personal priorities. Moving compression to application is the benefit of scaling easier but introduce complexity. Let MySQL take care of compression is transparent and easy to setup however it puts some pressure on CPU (which is by the way rarely a bottleneck for databases).

两者都是有效的选择。这真的取决于你的个人优先事项。将压缩移动到应用程序是扩展更容易但引入复杂性的好处。让MySQL处理压缩是透明的,易于设置,但它给CPU带来了一些压力(这很少是数据库的瓶颈)。

#2

-1

I found that Postgresql 9.3 has better capabilities for JSON than the MySQL versions I am using:

我发现Postgresql 9.3比我使用的MySQL版本具有更好的JSON功能:

http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3#JSON%3a_Additional_functionality

http://www.postgresql.org/docs/9.3/static/functions-json.html

#1