应该使用什么列类型在mysql db中存储序列化数据？ [英] What column type should be used to store serialized data in a mysql db?

问题描述

应该使用什么列类型在mysql db中存储序列化数据？
我知道你可以使用varbinary，blob，text。什么是最好的，为什么？

What column type should be used to store serialized data in a mysql db? I know you can use varbinary, blob, text. What's considered the best and why?

编辑：
我知道它不是好来存储序列化数据。我需要这样做在这一个case。请相信我，关注这个问题，如果你有答案。谢谢！

I understand it is not "good" to store serialized data. I need to do it in this one case though. Please just trust me on this and focus on the question if you have an answer. Thanks!

推荐答案

回答：文本在大量DBMS中已被弃用，似乎更好，所以最好使用blob或varchar有一个上限（和blob，你不会得到任何编码问题，这是一个主要的麻烦varchar和文本）。

To answer: text is deprecated in a lot of DBMS it seems, so better use either a blob or a varchar with a high limit (and with blob you won't get any encoding issues, which is a major hassle with varchar and text).

也指向这个线程在MySQL论坛，硬盘驱动器比软件便宜，所以你' d更好地首先设计您的软件并使其工作，只有当空间成为一个问题，您可能希望优化该方面。因此，不要尝试过早优化列的大小，最好先设置较大的大小（加上这将避免安全问题）。

Also as pointed in this thread at the MySQL forums, hard-drives are cheaper than software, so you'd better first design your software and make it work, and only then if space becomes an issue, you may want to optimize that aspect. So don't try to overoptimize the size of your column too early on, better set the size larger at first (plus this will avoid security issues).

关于各种评论：
这里太多的SQL狂热。尽管事实上我非常喜欢SQL和关系模型，但它们也有它们的缺陷。

About the various comments: Too much SQL fanaticism here. Despite the fact that I am greatly fond of SQL and relational models, they also have their pitfalls.

将序列化数据按原样存储到数据库中（例如存储JSON或XML格式的数据）有以下几个优点：

Storing serialized data into the database as-is (such as storing JSON or XML formatted data) has a few advantages:

• 您可以为数据提供更灵活的格式：


• 减少与对象模型的阻抗不匹配：存储和获取数据就像在程序中一样，相比之下，获取数据，然后在程序对象的结构和关系数据库的结构之间进行处理和转换。

很多其他的优点，所以请不要fanboyism：关系数据库是一个伟大的工具，但让我们不管其他工具，我们可以得到。更多工具，更好。

And there are a lot more other advantages, so please no fanboyism: relational databases are a great tool, but let's not dish the other tools we can get. More tools, the better.

对于一个具体的使用示例，我倾向于在我的数据库中添加一个JSON字段来存储记录的额外参数，属性）永远不会被单独选择，但仅在已经选择了正确的记录时使用。在这种情况下，我仍然可以用关系列来区分我的记录，并且当选择正确的记录时，我可以使用额外的参数为任何我想要的目的。

As for a concrete example of use, I tend to add a JSON field in my database to store extra parameters of a record where the columns (properties) of the JSON data will never be SELECT'd individually, but only used when the right record is already selected. In this case, I can still discriminate my records with the relational columns, and when the right record is selected, I can just use the extra parameters for whatever purpose I want.

所以我的建议，保持两个世界（速度，可串行化和结构的灵活性），只是使用几个标准的关系列作为唯一键来区分行，然后使用一个blob / varchar列，其中您的序列化数据将被插入。通常，唯一键只需要两个/三列，因此这不是一个主要的开销。

So my advice to retain the best of both world (speed, serializability and structural flexibility), just use a few standard relational columns to serve as unique keys to discriminate between your rows, and then use a blob/varchar column where your serialized data will be inserted. Usually, only two/three columns are required for a unique key, thus this won't be a major overhead.

此外，您可能对PostgreSQL感兴趣， JSON数据类型和 PostSQL项目，以便像关系列一样直接处理JSON字段。

Also, you may be interested by PostgreSQL which now has a JSON datatype, and the PostSQL project to directly process JSON fields just as relational columns.

这篇关于应该使用什么列类型在mysql db中存储序列化数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！