Storing JSON in MySQL?

Question

I have some things that do not need to be indexed or searched (game configurations) so I was thinking of storing JSON on a BLOB. Is this a good idea at all? Or are there alternatives?

You might find this article and accompanying discussion on Hacker News interesting: news.ycombinator.com/item?id=496946 — treeface
– treeface, Commented Oct 22, 2010 at 22:08
While it works fine, as the answers say, I consider it a really bad practice. It's inevitable that you'll want to query it and/or modify it (in part) at some point in the future. Both of those operations will be impossible if you use this approach. I'm speaking from experience... one of the worst DB design decisions I ever made was more or less what you're talking about. — rmeador
– rmeador, Commented Oct 22, 2010 at 22:24

Alan Geleynse · Accepted Answer · 2010-10-22 22:10:49Z

5

If you need to query based on the values within the JSON, it would be better to store the values separately.

If you are just loading a set of configurations like you say you are doing, storing the JSON directly in the database works great and is a very easy solution.

answered Oct 22, 2010 at 22:10

Alan Geleynse

25.2k6 gold badges49 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Timmy Over a year ago

Ya, I do not need the values at all, except for at retrieval. thx

Timmy Over a year ago

and i do have columns for things that needs to be indexed

Alan Geleynse Over a year ago

That is fine if you are indexing other columns. As long as the data in the JSON does not need to be indexed you can keep it there. You can always change the database later if you ever want to see how many users have some option set, but I am sure you have more interesting things to work on first.

T.J. Crowder · Accepted Answer · 2010-10-22 22:10:12Z

2

No different than people storing XML snippets in a database (that doesn't have XML support). Don't see any harm in it, if it really doesn't need to be searched at the DB level. And the great thing about JSON is how parseable it is.

answered Oct 22, 2010 at 22:10

T.J. Crowder

1.1m201 gold badges2k silver badges2k bronze badges

Comments

stevelove · Accepted Answer · 2010-10-22 22:14:34Z

2

I don't see why not. As a related real-world example, WordPress stores serialized PHP arrays as a single value in many instances.

answered Oct 22, 2010 at 22:14

stevelove

3,2121 gold badge25 silver badges29 bronze badges

Comments

mahdieh Saeed · Accepted Answer · 2010-10-24 11:58:30Z

0

I think,It's beter serialize your XML.If you are using python language ,cPickle is good choice.

answered Oct 24, 2010 at 11:58

mahdieh Saeed

294 bronze badges

1 Comment

StaxMan Over a year ago

Why? There may be valid reasons, but just saying 'use XXX instead' is not very useful...

Collectives™ on Stack Overflow

Storing JSON in MySQL?

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related