I keep finding that MySQL Workbench shows query results as BLOB. e.g:
SELECT INET_NTOA(167773449) --> BLOB
If I select to 'view value' I can determine the text value is '10.0.5.9' but it's pretty irritating when I SELECT multiple rows and want to glance at the contents.
Is there a way around this or is it a limitation of the tool?
Background: This problem occurs when the binary string values (BINARY/VARBINARY type) are returned in the results. The binary strings contain the zero bytes and for some reason, apparently security, have not been shown by default. More details about binary strings here.
Even in the reported example SELECT INET_NTOA(167773449), the function returns binary string. Check this for reference.
Solution: Since MySQL Workbench v5.2.22, it can be set through preferences whether to SHOW or HIDE such values.
Reference: The original issue has been reported and answered with fix here.
Edit -> Preferences -> SQL Editor -> SQL Execution
What you can do is Cast your BLOB type to a string. This will simply allow you to glance at whats in your BLOB type when browsing your select statement.
SELECT CAST(`blob_column_name` AS CHAR(10000) CHARACTER SET utf8) FROM `table_name`;
SELECT CONVERT(FROM_BASE64(myMEDIUMTEXTcol) using utf8) notAblobject FROM myTable;
My table contains a MEDIUMTEXT column which stores a Base64 string. In my frontend JS code I kept getting an Object returned instead of a string. The Object appeared to contain an array of ASCII values.
Testing the associated query in phpMyAdmin indicated the result was a BLOB. I updated my SQL query, as above, in order to get a simple string returned instead of an Object.
