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?

make sure you check my answer below - upvoted 41 times - i am still (3 years later) getting people that upvote it. — spioter
– spioter, Commented Oct 24, 2014 at 18:35
@spioter thanks for the reminder, done! Although I didn't test it as I'm not using it currently. — mark
– mark, Commented Nov 3, 2014 at 12:50
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. — spioter
– spioter, Commented Nov 4, 2014 at 16:46
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" — Accountant م
– Accountant م, Commented Feb 8, 2019 at 22:40
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.) — ToolmakerSteve
– ToolmakerSteve, Commented Apr 9, 2019 at 20:47

emanresu_123 · Accepted Answer · 2023-04-22 21:26:35Z

129

In short:

In MySQL Workbench 6.0+

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

In older versions of MySQL Workbench

Go to Edit > Preferences
Choose SQL Queries
Under Query Results, check Treat BINARY/VARBINARY as nonbinary character string
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?

edited Apr 22, 2023 at 21:26

emanresu_123

6410 bronze badges

answered Sep 6, 2011 at 16:38

spioter

1,8701 gold badge13 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

GDICommander Over a year ago

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

spioter Over a year ago

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

trebormf Over a year ago

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

fsasvari Over a year ago

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?

Gerard ONeill Over a year ago

@HartleySan 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.

|

Dan Sherman · Accepted Answer · 2011-08-01 19:13:57Z

38

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

answered Aug 1, 2011 at 19:13

Dan Sherman

3893 silver badges2 bronze badges

4 Comments

Yuvraj Mudaliar Over a year ago

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

The 0bserver Over a year ago

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. :)

GhostCoder77 Over a year ago

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

ToolmakerSteve Over a year ago

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. :)

Gerard ONeill · Accepted Answer · 2021-09-01 17:01:21Z

32

Perform three steps:

Go to "WorkBench Preferences" --> Choose "SQL Editor" Under "Query Results": check "Treat BINARY/VARBINARY as nonbinary character string"
Restart MySQL WorkBench.
Now select SELECT SUBSTRING(<BLOB_COLUMN_NAME>,1,2500) FROM <Table_name>;

edited Sep 1, 2021 at 17:01

Gerard ONeill

4,17043 silver badges25 bronze badges

answered Nov 2, 2012 at 17:45

NKP

4495 silver badges3 bronze badges

3 Comments

spioter Over a year ago

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

Björn Over a year ago

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

QkiZ Over a year ago

Not BLOB<COLUMN_NAME> but <BLOB_COLUMN_NAME>

Suraj Rao · Accepted Answer · 2019-06-18 15:36:20Z

18

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.

edited Jun 18, 2019 at 15:36

Suraj Rao

29.7k11 gold badges96 silver badges104 bronze badges

answered Jun 18, 2019 at 15:35

chil

1811 silver badge2 bronze badges

3 Comments

bappak Over a year ago

Worked like a charm in version 8.0.19 on MacOS Mojave.

Gerard ONeill Over a year ago

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.

ken koehler Over a year ago

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.

CSTobey · Accepted Answer · 2012-05-21 16:27:12Z

15

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

answered May 21, 2012 at 16:27

CSTobey

1,53113 silver badges10 bronze badges

2 Comments

Antonio Medeiros Over a year ago

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.

CSTobey Over a year ago

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

dmag · Accepted Answer · 2010-12-23 09:20:28Z

7

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!

answered Dec 23, 2010 at 9:20

dmag

4052 silver badges7 bronze badges

1 Comment

htxryan Over a year ago

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

friism · Accepted Answer · 2012-11-13 01:11:40Z

5

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

edited Nov 13, 2012 at 1:11

friism

19.3k5 gold badges83 silver badges116 bronze badges

answered May 18, 2011 at 13:55

glissi

591 silver badge1 bronze badge

Comments

mbunch · Accepted Answer · 2018-09-07 18:13:34Z

5

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`;

edited Sep 7, 2018 at 18:13

answered Mar 15, 2018 at 22:09

mbunch

5691 gold badge7 silver badges21 bronze badges

Comments

Stephanie · Accepted Answer · 2016-10-06 14:59:33Z

4

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".

edited Oct 6, 2016 at 14:59

answered Oct 6, 2016 at 14:48

Stephanie

1211 silver badge5 bronze badges

1 Comment

ToolmakerSteve Over a year ago

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."

Kdecom · Accepted Answer · 2013-04-28 21:10:25Z

2

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/

answered Apr 28, 2013 at 21:10

Kdecom

7286 silver badges6 bronze badges

1 Comment

ToolmakerSteve Over a year ago

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...

ToolmakerSteve · Accepted Answer · 2019-04-09 20:49:32Z

2

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.

answered Apr 9, 2019 at 20:49

ToolmakerSteve

22.1k16 gold badges114 silver badges233 bronze badges

1 Comment

M22an Over a year ago

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

curiouz · Accepted Answer · 2023-09-06 05:31:08Z

1

You can try

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

answered Sep 6, 2023 at 5:31

curiouz

1361 gold badge4 silver badges14 bronze badges

Collectives™ on Stack Overflow

How can I directly view blobs in MySQL Workbench

12 Answers 12

9 Comments

4 Comments

3 Comments

3 Comments

2 Comments

1 Comment

Comments

Comments

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

12 Answers 12

9 Comments

4 Comments

3 Comments

3 Comments

2 Comments

1 Comment

Comments

Comments

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related