Best way to store base64 value in MySQL DB?

Question

I have a value that I would like to store in my DB, does the sorting make any difference, like a string like this

YToyOntzOjIwOiJUeXBlX29mX29yZ2FuaXNhdGlvbiI7czoyMDoiTWVtYmVyIG9mIFBhcmxpYW1lbnQiO3M6ODoiUG9zdGNvZGUiO3M6NzoiUEUxIDFKQSI7fQ== 

Could be saved? In addition, the only way I can write these values ​​to the database is to use a BLOB , which is apparently a very wrong way to store it.

Answer 1

The summary only matters if you need ORDER BY or do a column search. These base64 encoded elements will probably not be found or sorted.

If your encoded elements will be less than 64 Kbytes in length, define your column as follows:

  `columnname` TEXT CHARACTER SET ascii, 

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

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

Change over the years.

An 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: many of this material is stored in text columns without encoding it using the utf8 or utf8mb4 character set. A lot of? Yes. Thus, WordPress saves the options data.

Observation 2: if it can be converted to JSON, you can use the JSON data type in recent versions of MySQL. JSON searches are still not matching, but they are structured.