MySQL JSON存储与两个表 [英] MySQL JSON Storage vs Two Tables

问题描述

使用JSON将数据存储在表中相对于拥有单独的元表有什么好处吗?

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

这是原始架构:

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版本，并且希望使用它，但是它比原始版本慢多少?

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.

推荐答案

使用大文本文件存储序列化的(无论何种序列化)都意味着:

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

• 无需修改架构即可添加/删除列
• 几乎可以存储任何您想要的东西
• 但是:您将无法在MySQL端使用这些数据-尤其是，您将无法在where子句中使用这些数据.
  基本上，这意味着使用MySQL进行存储数据-无非就是存储

• 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

如果您只想在PHP端使用这些数据，而不必在SQL端使用，则我想将所有内容都存储在大文本字段中是一种解决方案.
(在这种情况下，我不明白为什么它会比其他解决方案要慢)

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)

这篇关于MySQL JSON存储与两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！