Converting BLOB in MySQL

Blob is a format used in MySQL to hold binary data (binary large objects). Blobs are treated as binary strings but has no character set information. Normal text is treated as character strings with character set information stored. If you want to use a blob you therefore have to know what character set it’s coded with.

Sometimes when you concatenate different data the result is returned as a blob even though you might want a string. One simple way of solving that is to convert it directly in the query as in this example. As mentioned above we also have to specify the character set used in the blob.

select convert(column using utf8) from table;

You can also be more specific of what type you want and use a cast() as in this example:

select cast(column as char(100) character set utf8) from table;

Note that varchar is not a valid type here since we’re not defining a table to store data in but rather data to be returned.