How can I directly view blobs in MySQL Workbench

Question

I'm using MySQL Workbench CE 5.2.30 CE / Rev 6790 . When execute the following statement:

SELECT OLD_PASSWORD("test")

I only get back a nice BLOB icon, I need to left-click to select the cell, right-click and choose "Open Value in viewer" and select the "Text" tab.

Using the same with phpMyAdmin, I get directly back the value of the OLD_PASSWORD call. It's just an example, but is there a way to directly see such results in the output?

Slink

make sure you check my answer below - upvoted 41 times - i am still (3 years later) getting people that upvote it. – Huntley

@Huntley thanks for the reminder, done! Although I didn't test it as I'm not using it currently. – Slink

thanks mark! i don't know why i just assumed the original asker would not even see my comment - i was just putting it as a pointer for future viewers :-p cheers. – Huntley

Be careful, this warning pups when I hover on this option in MySQL Workbench "warning: since binary byte strings tend to contain zero bytes in their values, turning this option on may lead to data truncation when viewing/ editing" – Grope

NOTE -- If you have arbitrary binary bytes -- The answers here aren't particularly useful if the BLOB is an arbitrary sequence of bytes; e.g. BINARY(16) to store 128-bit GUID or md5 checksum. In that case, there currently is no editor preference (though I have submitted a feature request now); in this case, the solution is HEX function in a query: SELECT HEX(mybinarycolumn) FROM mytable. (An alternative is to use phpMyAdmin instead of MySQL Workbench - there hex is shown by default.) – Upheld

Answer 1

In short:

In MySQL Workbench 6.0+

1. Go to Edit > Preferences
2. Choose SQL Editor
3. Under SQL Execution, check Treat BINARY/VARBINARY as nonbinary character string
4. Restart MySQL Workbench (you will not be prompted or informed of this requirement).

In older versions of MySQL Workbench

1. Go to Edit > Preferences
2. Choose SQL Queries
3. Under Query Results, check Treat BINARY/VARBINARY as nonbinary character string
4. It's not mandatory to restart MySQL Workbench (you will not be prompted or informed of this requirement).*

With this setting you will be able to concatenate fields without getting blobs.

I think this applies to versions 5.2.22 and later and is the result of this MySQL bug.

Disclaimer: I don't know what the downside of this setting is - maybe when you are selecting BINARY/VARBINARY values you will see it as plain text which may be misleading and/or maybe it will hinder performance if they are large enough?

Huntley

FYI: In version 5.2.39 of MySQL Workbench, it's "SQL Editor", not "SQL Queries". – Playhouse

in 5.2.44 CE rev 9933 the code posted by the original poster displays the data properly. I am pretty sure the relevant setting is "Treat BINARY/VARBINARY as nonbinary character string" which can be accessed via: Edit/Preferences/SQL Queries (under the "Query Results" section) This setting needs to be checked - I cannot recall if it was checked by default – Huntley

Please don't edit my post without updating the version number to which the edit applies - at time of this post 2014-11-23 11:28 - they are not compatible. Either to revert to the original, or update the version number to be accurate. – Huntley

I think I know where spioter got all his reputation points! Great work, thanks! – Abstraction

Works in 6.3.3 and I did NOT need to restart Workbench after changing the setting. – Rossierossing

Checked the option in 6.3.10 and it didn't seem to make a difference, even on restarting Workbench. – Thirtytwo

I no longer work with MySQL, sorry. Perhaps if you contacted their support team with the info from this thread, then they would be able to help. We could see who is faster: SO community or their support team, lol. – Huntley

Using MySQL Workbench version 8.0.21 on Mac and after checking the "Treat BINARY/VARBINARY as nonbinary character string" option, trying to refresh the resultset, the Workbench just loads and loads without returning any value from database... Anyone else occurs this kind of a problem? – Epineurium

@Thirtytwo Not sure if this is related, but for me it didn't work on columns 'user_password', 'user_newpassword', and 'user_email'. Perhaps these are exceptional? But using 'convert(col using utf8)` worked with all columns. – Acidulous

Answer 2

I'm not sure if this answers the question but if if you right click on the "blob" icon in the field (when viewing the table) there is an option to "Open Value in Editor". One of the tabs lets you view the blob. This is in ver. 5.2.34

Bobbobb

Works perfectly.This should be accepted answer as it doesn't require any settings change. – Barrettbarrette

To add on to this. It works for me as well. When you click on Open Value in Editor, it shows you the Binary values by default. but if you notice there is a tab saying Text. That tab shows you the right values in text format. :) – Distance

Agreed this is the simplest solution. I didn't notice the popup had tabs which was my problem. – Cata

FWIW: I'm sure this doesn't answer the question, as in the second paragraph OP states that this is what they do now .. and they are looking for a more convenient solution. Nevertheless, its an interesting social experiment, that shows it is easier for many (perhaps even most) people to glance through these answers to find one that helps them, than it is to find the answer they needed, buried somewhere in the question. :) – Upheld

Answer 3

Perform three steps:

1. Go to "WorkBench Preferences" --> Choose "SQL Editor" Under "Query Results": check "Treat BINARY/VARBINARY as nonbinary character string"

2. Restart MySQL WorkBench.

3. Now select SELECT SUBSTRING(<BLOB_COLUMN_NAME>,1,2500) FROM <Table_name>;

Prolong

funny how similar and later this is than the highest scoring answer :-p – Huntley

@Johny19 : I think it's being upvoted because of the example with "SUBSTRING". – Smother

Not BLOB<COLUMN_NAME> but <BLOB_COLUMN_NAME> – Liberticide

Answer 4

select CONVERT((column_name) USING utf8) FROM table;

In my case, Workbench does not work. so i used the above solution to show blob data as text.

Motion

Worked like a charm in version 8.0.19 on MacOS Mojave. – Wager

This probably should be the top answer (nothing wrong with the accepted answer!). using the top answer, for some reason some columns were still not converted. But this method works for all columns and forms of db_access, and requires no settings changes. – Acidulous

I have data which is stored as base64 in the database. I have to use both solutions. I set the binary/varbinary setting, but that alone did not return legible values. But it works when I use your solution too. Example, CONVERT(from_base64(data_i_am_querying) USING utf8) . Now I have legible data. I'm not sure why it worked, but it worked. – Pragmatics

Answer 5

casting works, but it is a pain, so I would recommend using spioter's method unless you are using a lot of truly blob data.

SELECT CAST(OLD_PASSWORD("test") AS CHAR)

You can also cast as other types, and even restrict the size, but most of the time I just use CHAR: http://dev.mysql.com/doc/refman/5.5/en/cast-functions.html#function_cast

Aparejo

I don't know if this is a consequence of casting, but I did exactly what you suggested and new lines did not appear. I got a very long text in just one line. It served for what I needed, anyway. – Mont

That is actually MySQL workbench, if you right click, view in popup window, then it will show correctly... yes, it is annoying. – Aparejo

Answer 6

Doesn't seem to be possible I'm afraid, its listed as a bug in workbench: http://bugs.mysql.com/bug.php?id=50692 It would be very useful though!

Naif

This isn't the right answer anymore (since 5.2.22). See spioter's answer below. – Edition

Answer 7

had the same problem, according to the MySQL documentation, you can select a Substring of a BLOB:

SELECT id, SUBSTRING(comment,1,2000) FROM t

HTH, glissi

Hardcore

Answer 8

I pieced a few of the other posts together, as the workbench 'preferences' fix did not work for me. (WB 6.3)

SELECT CAST(`column` AS CHAR(10000) CHARACTER SET utf8) FROM `table`;

Nippon

Answer 9

Work bench 6.3
Follow High scoring answer then use UNCOMPRESS()

(In short:
1. Go to Edit > Preferences
2. Choose SQL Editor
3. Under SQL Execution, check Treat BINARY/VARBINARY as nonbinary character string
4. Restart MySQL Workbench (you will not be prompted or informed of this requirement).)

Then

SELECT SUBSTRING(UNCOMPRESS(<COLUMN_NAME>),1,2500) FROM <Table_name>;

or

SELECT CAST(UNCOMPRESS(<COLUMN_NAME>) AS CHAR) FROM <Table_name>;

If you just put UNCOMPRESS(<COLUMN_NAME>) you can right click blob and click "Open Value in Editor".

Acciaccatura

This answer should explain when UNCOMPRESS is appropriate. While I have never used it, according to MySQL docs, it is only appropriate when COMPRESS was used to create the BINARY value: "If the argument is not a compressed value, the result is NULL." – Upheld

Answer 10

there is few things that you can do

SELECT GROUP_CONCAT(CAST(name AS CHAR))
FROM product
WHERE  id   IN (12345,12346,12347)

If you want to order by the query you can order by cast as well like below

SELECT GROUP_CONCAT(name ORDER BY name))
FROM product
WHERE id   IN (12345,12346,12347)

as it says on this blog

http://www.kdecom.com/mysql-group-concat-blob-bug-solved/

Schonfeld

When adding an answer, especially years later, please clarify why you are adding another answer. Both CSTobey and mbunch already showed that the blob field can be CAST .. AS CHAR. I think you are demonstrating that the CAST is useful to make GROUP_CONCAT work properly? Unfortunately, the link is broken. BTW, something is wrong with your second code example... – Upheld

Answer 11

NOTE: The previous answers here aren't particularly useful if the BLOB is an arbitrary sequence of bytes; e.g. BINARY(16) to store 128-bit GUID or md5 checksum.

In that case, there currently is no editor preference -- though I have submitted a feature request now -- see that request for more detailed explanation.

[Until/unless that feature request is implemented], the solution is HEX function in a query: SELECT HEX(mybinarycolumn) FROM mytable.

---

An alternative is to use phpMyAdmin instead of MySQL Workbench - there hex is shown by default.

Upheld

Thanks Steve, this is exactly what I was looking for. Hope that they add that option in workbench. – Unwisdom

Answer 12

You can try

SELECT *, HEX(<BLOB_COLUMN>) as blob_vectors
FROM <BLOB_COLUMN> 
LIMIT 100; 

Grafting