Storing JSON in MySQL database? [duplicate]

Question

Is it acceptable to store JSON data in a MySQL table row? I need to store arrays in a mysql database. The problem is, i don't know how many columns i will need for each user. So I thought to store JSON in one row named array for exemple. Is this the best way?

Edit:

Also, I am using text as table column type.

Answer 1

Yes, it's a very good idea to use mysql as a key-value store, in fact facebook does for some uses.

CREATE TABLE `json` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `data` blob NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The above table-structure will get you far. It's very easy to shard or cluster.

Edit: The point here is to use PHP, Ruby etc to handle the json/data. You do SELECT ..., do your edits, then INSERT ... ON DUPLICATE KEY UPDATE ....