Which mysql column type for serialize(data)?
mysql, php
Solution
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.
Problem
I am serializing alot of arrays in php that are to be stored in a database using mysql. The length of the final string can vary greatly from anything inbetween 2000 to 100,000+, I was wondering what would the best column type for this to be? I currently have it set as `LONGTEXT` but I feel this is overkill! The database is already active and has around 3million rows this is a new column which will added soon. Thanks