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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT    CONVERT  UNCOMPRESS column  USING  utf8    AS column FROM table name

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 lt COLUMN NAME gt  1 2500  FROM  lt Table name gt

User · Answer

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  lt COLUMN NAME gt   1 2500  FROM  lt Table name gt     or  SELECT CAST UNCOMPRESS  lt COLUMN NAME gt   AS CHAR  FROM  lt Table name gt     If you just put UNCOMPRESS  lt COLUMN NAME gt   you can right click blob and click   Open Value in Editor

User · Answer

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

User · Answer

In short    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 MySQL Workbench 6 0    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

[mysql] How can I directly view blobs in MySQL Workbench

Examples related to mysql

Examples related to mysql-workbench