PHP + MySQL - What column type should be used to store serialized objects?

Question

With PHP, I am storing serialized objects in a MySQL database.

What column type should I use? I am currently using "LONGTEXT" since basically it's a string.

It's working but I worry that bigger objects might not save correctly.

What column type should I use?

Have to wonder whether it would actually make more sense to not serialize, and put them in normalised table structure. — Jonnix, Jul 04 '17 at 15:45
That's one factor, another one in ability to search and index columns, you can see the relations more easily, you can end up reducing duplicate data etc etc. The list goes on. — Jonnix, Jul 04 '17 at 15:48
Yes, you might be correct by saying that. Some data I might have to store in a table to run queries on. I am just trying to understand in what cases I can store a serialized object. — David Somekh, Jul 04 '17 at 15:50

score 2 · Accepted Answer · answered Jul 04 '17 at 15:49

2

Always use BLOB type to store serialized objects.

use MEDIUMBLOB or just BLOB, as long as those types are sufficient to store your serialized data.

BLOB type holds larger object and are binary safe.

Read more about BLOB

answered Jul 04 '17 at 15:49

Abhishek Shaw

A binary large object column with a maximum length of 16777215 (2^24 - 1) characters. A binary large object column with a maximum length of 4294967295 (2^32 - 1) characters. A **BLOB** can be 65535 bytes maximum. If you need more consider using a **MEDIUMBLOB** for 16777215 bytes or a **LONGBLOB** for 4294967295 bytes. – Abhishek Shaw Jul 04 '17 at 15:53
It depends on your serialization method. JSON is UTF-8 and should be stored as such, and MySQL 5.6 has a `JSON` column type. – tadman Jul 04 '17 at 16:02

score 0 · Answer 2 · answered Jul 04 '17 at 15:43

0

BLOB, can hold various types of data.

This was the column type i used for storing objects in a database recently.

Hope this helps

answered Jul 04 '17 at 15:43

JParkinson1991

Thanks! Using LONGBLOB, you know what is the length limit? – David Somekh Jul 04 '17 at 15:46
My assumption is you dont need LONGBLOB, its size limit is 4GB – JParkinson1991 Jul 04 '17 at 15:48
Thanks! You know where can I find the size limits for each BLOB? – David Somekh Jul 04 '17 at 15:49
A good answer to that can be found in this question: https://stackoverflow.com/questions/5775571/what-is-the-maximum-length-of-data-i-can-put-in-a-blob-column-in-mysql – JParkinson1991 Jul 04 '17 at 15:50
Please mark my answer as correct if you're sorted with everything you need now. Thanks! – JParkinson1991 Jul 04 '17 at 15:58

2 Answers2