mysql json 子表,MySQL 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.

解决方案

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)