Best way to store a base64 encoded value in MySQL DB?

Question

I have a value I'd like to store in my DB, does the Collation make any difference how a string like this

YToyOntzOjIwOiJUeXBlX29mX29yZ2FuaXNhdGlvbiI7czoyMDoiTWVtYmVyIG9mIFBhcmxpYW1lbnQiO3M6ODoiUG9zdGNvZGUiO3M6NzoiUEUxIDFKQSI7fQ==

Might be stored? Also, the only way I seem to be able to write these values to the DB is by using a BLOB this seems to be a really rather wrong way of storing it.

Answer 1

The collation only makes a difference if you need to ORDER BY or search the column. These base64 encoded items are probably not going to be searched or sorted.

If your encoded items are guaranteed to be less than 64K bytes in length, define your column like this:

   `columnname` TEXT CHARACTER SET ascii,

This is exactly what's needed for a base64 encoded variable; the encoding process turns everything into displayable ASCII.

If the items will be less than 16 megabytes in length, but some will be longer than 64k, use MEDIUMTEXT instead of TEXT.

Edit years later.

The OQ encoded string, decoded, is a serialized php object:

a:2:{s:20:"Type_of_organisation";s:20:"Member of Parliament";s:8:"Postcode";s:7:"PE1 1JA";}

Observation 1: lots of this stuff gets stored in text columns without encoding it, using the utf8 or utf8mb4 character set. Lots? Yes. WordPress stores option data this way.

Observation 2: If it can be translated to JSON, you could use the JSON data type in recent versions of MySQL. JSON searches still aren't sargable, but they are structured.