MySQL JSON存储vs两个表(MySQL JSON Storage vs Two Tables)

MySQL JSON存储vs两个表(MySQL JSON Storage vs Two Tables)

使用JSON将数据存储在表中与使用单独的元表相比会有什么好处？

这是原始模式：

Users Table UserId | Username | Etc... 5 | John | Avatar Table Id | UserId | ImageName | ImageType 1 | 5 | example.png | original 2 | 5 | example_thumb.png | thumbnail

这是JSON版本：

Users Table UserId | Username | Avatar 5 | John | {"original":"example.png","thumbnail":"example_thumb.png"}

我个人喜欢json版本，并且宁愿使用它，但比原始版本慢多少？

就我而言，我需要用户的头像（和不同的尺寸）几乎每个查询。

Would there be any benefit in using JSON to store data in a table vs having a separate meta table?

Here is the original schema:

Users Table UserId | Username | Etc... 5 | John | Avatar Table Id | UserId | ImageName | ImageType 1 | 5 | example.png | original 2 | 5 | example_thumb.png | thumbnail

Here is the JSON version:

Users Table UserId | Username | Avatar 5 | John | {"original":"example.png","thumbnail":"example_thumb.png"}

I personally like the json version and would prefer to use it, but how much slower is it than the original version?

In my case, I need the user's avatars (and different sizes) almost every query.

最满意答案

使用大文本文件来存储序列化（不管是什么样的序列化）意味着：

无需修改架构以添加/删除列 能够存储你想要的任何东西 但是：你将无法在MySQL端处理这些数据 - 尤其是，你将无法在where子句中使用这些数据。 基本上，这意味着使用MySQL来存储数据 - 只不过是存储

如果你只想在PHP方面使用这些数据，并且不需要在SQL方面工作，那么我认为将所有东西都存放在一个大文本字段中是一个解决方案。 （在这种情况下，我不明白为什么它会比另一种解决方案慢）

Using a big text file to store serialized (no matter what kind of serialization) means :

No need to modify the schema to add / remove columns Ability to store pretty much anything you want BUT : you will not be able to work with those data on the MySQL side -- especially, you won't be able to use those in a where clause.Basically, this means using MySQL to store data -- and nothing more than storage

If you only want to use those data on the PHP side, and never have to work with the on the SQL side, I suppose that storing everything a big text field is a solution. (and, in this case, I don't see why it would be slower than another solution)