Its being stored as text as its serialized data (coming from an array or object in PHP). You don't want to mess with the encoding of serialized data (as stated above). You have to run it through unserialize() and var_dump() in PHP to easily be able to read / use the data.
Hi Andy, as King Kovifor said, it is serialized content you're storing in the data_registry table. Content returned from the PHP serialize() function should be considered a byte-stream. It's not so much about encoding (although, if for some reason your serialized content contains bytes that are outside your encoding it would also affect the chosen table format), but about the fact that it could contain null bytes, which would, if you were using a TEXT field, cause text content to be truncated. Have a look here:
Returns a string containing a byte-stream representation of value that can be stored anywhere.
Note that this is 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.
Okay, great - but for me to understand the different I need to know what serialized data is? Is this a long string of data - containing the content of many fields - that is separated out into it's individual components when it is de-serialised? Sort of like storing an array in its native state?
So instead of having a table with 30 fields you can store the same data in a serialised BLOB?
Like maybe having all the Custom user profile fields in one place - one BLOB?
If so, how do you get at individual parts of a BLOB?
Basically it creates a state of data (except resource-type). Upon unserialization, you get an identical copy of what you serialized. Its useful for complex data (that'd take a long time to compute), or variable data sets (custom profile fields are serialized, IIRC) that you can't predict the names or amount, or objects where you update instance variables.
If you want to get individual parts of a serialized array you need to unserialize it for use in your code. BLOB is just a storage method and considered a binary string.
I'm still curious what the advantage of a *binary* format was for textual data. It's frustrating that JSON or another human-readable format could not have been used. There are some transforms I'd like to do with Custom User Fields that I wanted to do without running the data through PHP.