Which mysql column type for serialize(data)?
Always use any BLOB
data-type for serializing data so that it does not get cut off and break the serialization in a binary safe manner. If there is not a maximum to the length of the final string then you will need LONGBLOB
. If you know that the data won't fill 2^24 characters you could use a MEDIUMBLOB
. MEDIUMBLOB
is about 16MB while LONGBLOB
is about 4GB so I would say you're pretty safe with MEDIUMBLOB
.
Why a binary data type? Text data types in MySQL have an encoding. Character encoding will have an effect on how the serialized data is transposed between the different encodings. E.g. when stored as Latin-1 but then read out as UTF-8 (for example because of the database driver connection encoding setting), the serialized data can be broken because binary offsets did shift however the serialized data was not encoded for such shifts. PHP's serialized strings are binary data, not with any specific encoding.
You should choose BLOB (as Marc B noted) per the PHP manual for serialize():
"Note that this [outputs] a binary string which may include null bytes, and needs to be stored and handled as such. For example, serialize() output should generally be stored in a BLOB field in a database, rather than a CHAR or TEXT field."
Source: http://php.net/serialize
Of course J.Money's input regarding sizes must be borne in mind as well - even BLOB has its limits, and if you are going to exceed them then you would need MEDIUMBLOB or LONGBLOB.