In MySQL, what is the difference between storing data in multiple columns vs storing data in JSON columns-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW)

In MySQL, what is the difference between storing data in multiple columns vs storing data in JSON columns

P粉393030917 2023-07-24 16:17:05

363

<p>Suppose I have a table called T1. Initially, it has ID, Identifier, C1, C2 as columns. Over time I will add new columns C3, C4 and so on. Would it be better to have these columns as ID, Identifier, Info? Where Info is a JSON column containing C1, C2, C3 as keys and their corresponding values. Considering the size of approximately 10^9 records, what will be the performance of reads, writes, and updates? </p>

P粉393030917

reply all(1)

P粉1808446192023-07-25 10:55:29 1 floor

If you plan to use a relational database, you should use the relational model. While many relational database management systems can now handle JSON, that doesn't mean you should save your data as JSON (by default).

A good example of using JSON is if your source data is provided in JSON format. You can load it as-is into a relational database and then use SQL to "unpack" it from the staging table's JSON format into the appropriate relational format.

As for performance, there are too many variables involved to provide a useful answer. The only way you can get useful answers is to test in your environment with your data and queries relevant to you.