How can I directly view blob in MySQL Workbench

Question

I am using MySQL Workbench CE 5.2.30 CE / Rev 6790. When performing the following statement:

SELECT OLD_PASSWORD("test") 

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

Using the same thing with phpMyAdmin, I get the OLD_PASSWORD call value directly back. This is just an example, but is there a way to see such results directly at the output?

Answer 1

In short:

1. Go to Edit> Preferences
2. Select SQL Editor
3. In the Executing SQL section, select the Treat BINARY / VARBINARY as a string of non-binary characters check box.
4. Restart MySQL Workbench (you will not be prompted or informed about this requirement).

In MySQL Workbench 6. 0+

1. Go to Edit> Preferences
2. Select SQL Queries
3. In the Query Results section, select the Process BINARY / VARBINARY check box as a string of non-binary characters.
4. Restarting the MySQL Workbench is optional (you will not be prompted or informed about this requirement). *

With this option, you can combine fields without receiving blobs.

I think this applies to versions 5.2.22 and higher and is the result of this MySQL error .

Disclaimer: I do not know what the flip side of this parameter is - maybe when you select the BINARY / VARBINARY you will see it in plain text, which can be misleading and / or maybe it will decrease performance if they big enough?

Answer 2

Follow the three steps:

• Go to the "WorkBench Settings" section → Select "SQL Editor" in the "Query Results" section: check the box "Treat BINARY / VARBINARY as a non-binary character string"

• Reboot the MySQL WorkBench.

• Now select SELECT SUBSTRING(BLOB<COLUMN_NAME>,1,2500) FROM <Table_name>;

Answer 3

I am not sure if this question answers the question, but if you right-click on the "blob" icon in the field (when viewing the table), the option "Open value in editor" will appear. One of the tabs allows you to view blob. It's right. 5.2.34

Answer 4

casting works, but it's pain, so I would recommend using the spioter method if you are not using a lot of really blob data.

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

You can also use other types and even limit 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

Answer 5

It seems impossible, I'm afraid it was indicated as an error in the workbench: http://bugs.mysql.com/bug.php?id=50692 It would be very useful though!

Answer 6

had the same problem, according to the MySQL documentation , you can select the BLOB substring:

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

Hh, glissi

Answer 7

Workplace 6.3
Follow the high score, then use UNCOMPRESS()

(In short:
1. Go to "Edit"> "Settings"
2. Select SQL Editor
3. In the "Executing SQL" section, select the "Processing BINARY / VARBINARY" check box as a non-string character string
4. Restart the MySQL Workbench (you will not be prompted or informed about 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 on the block and click "Open value in editor."

Answer 8

I put together several other posts, since correcting “preferences” in the workplace did not help me. (WB 6.3)

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

Answer 9

there are a few things you can do

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

If you want to order on request, you can order it, as well as below

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

as stated in this blog

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

Answer 10

NOTE. The previous answers here are not particularly useful if the blob is an arbitrary sequence of bytes; for example, BINARY (16) for storing a 128-bit GUID or md5 checksum.

In this case, there is currently no editor preference - although I have already sent a request to add a function - see this request for a more detailed explanation.

[Until this function request is implemented], the solution is the HEX function in the request: SELECT HEX(mybinarycolumn) FROM mytable .

---

An alternative is to use phpMyAdmin instead of MySQL Workbench - it displays hex by default.

Answer 11

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

In my case, Workbench is not working. so I used the above solution to display blob data as text.

Answer 12

SELECT *, CONVERT (UNCOMPRESS (column) USING "utf8") AS column FROM table_name