Storing Serialized Data in Relational DBMS (i.e. MySQL)

Question

While I'm hoping to eventually switch to a newer, lighter, and/or faster DBMS (such as the aptly-named lightweight SQLite or the document-based Mongo- the latter of which would not present this issue), I am currently sticking to the standard MySQL system. Though I realize that any answer to this question may be one of opinion, I'm wondering how one would store serialized data such as an array or hash-map in a single column of a MySQL [or otherwise relational-based] database. Most data access would be executed via PHP, but I would like to store data in a standard format (i.e. JSON or binary hash-map- preferably the latter) rather than the plain-text of the dumped PHP array. The reason for this is that I may perform some data queries from Python, a compiled C/C++ application, or the command line.

Thanks.

Answer 1

If you want to store JSON in your MySQL, you might consider using some document store like MongoDB, simply because those database engines are able to make queries through the documents.

For example, if you would store your JSON in format {A: 'valueA', B: 'valueB'} and would like to get all documents where B='realvalueB', you would have to get all rows, convert them to php objects with json_decode and make comparisons...on the other hand, if you use MongoDb, you would make a query: db.myobjects.find({B:'realvalueB'}) and it would return only matched documents. This was just a simple example of why it is better. You can find here and here what you can do with mongodb queries.

So, if your json documents contain some useful information and you can filter by that info, it would be better to go with document store.

If you go with MySQL (because it is more mature, you are more familiar with it etc.) it is always good to go with "real" relational model, but you can always put some stuff in the blob if your use cases allow you to do so.

Answer 2

Just use TEXT field in MySQL and json_encode() and json_decode($v, true) in PHP. You may want to wrap those functions in your own code so when the need arises to replace this storage model with some other you can do it in one place instead of tracking all occurrences of json_encode and json_decode across your files.

If you intend to keep really huge hashes you may need to adjust some configs (as described here http://dev.mysql.com/doc/refman/5.0/en/blob.html):

The maximum size of a BLOB or TEXT object is determined by its type, but the largest value you actually can transmit between the client and server is determined by the amount of available memory and the size of the communications buffers. You can change the message buffer size by changing the value of the max_allowed_packet variable, but you must do so for both the server and your client program. For example, both mysql and mysqldump enable you to change the client-side max_allowed_packet value. See Section 7.9.2, “Tuning Server Parameters”, Section 4.5.1, “mysql — The MySQL Command-Line Tool”, and Section 4.5.4, “mysqldump — A Database Backup Program”. You may also want to compare the packet sizes and the size of the data objects you are storing with the storage requirements, see Section 10.5, “Data Type Storage Requirements”

And obviously use stored procedures for saving and reading hashes.

The above assumes that you want to just store and retrieve data. If you want to do any searching or aggregating then try to find another approach.

Answer 3

I wouldn't store it at all.
Instead of doing such a weird and apparently useless thing I would try to make a relational model out of the data. It was done many times before noSQL era and surely can be done one more time.

Also, I see no point in starting with MySQL (or SQLite which is the same in every way beside reliability) if you are planning to move to document-based Mongo.
To me, there is not single [sensible] reason to start a project with inappropriate storage, put some efforts in it, and eventually rewrite everything from scratch.

Answer 4

Forewarning: this is my opinion only.

If you are planning to query the data from means other than the one storing it (e.g. store from PHP, query from C/C++ or even command line), I would strongly discourage you from storing the entire data structure in one column. Instead, I would suggest creating your data model to better support your queries (e.g. store a hashmap as a set of name/value pairs mapped to the id of the hashmap). Then create a model layer in the code to deal with conversion between the code layer and the database layer.

When you need to store the data into the database, from your PHP code, simply call corresponding methods/functions in your model layer, passing the hashmap. When you need to load the data from the DB, call the corresponding methods/functions in the model layer passing ID of the hashmap (or whatever the relevant identifier is) - and get the hashmap back.

This way, besides making it much-much easier to query the data later, if/when you change the database engine, all you will need to do is to update a few methods/functions in your model layer.