如何在 MySQL Workbench 中直接查看 blob
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4343945/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I directly view blobs in MySQL Workbench
提问by mark
I'm using MySQL Workbench CE 5.2.30 CE / Rev 6790 . When execute the following statement:
我正在使用 MySQL Workbench CE 5.2.30 CE / Rev 6790。执行以下语句时：
SELECT OLD_PASSWORD("test")
I only get back a nice BLOBicon, I need to left-click to select the cell, right-click and choose "Open Value in viewer" and select the "Text" tab.
我只得到一个漂亮的BLOB图标，我需要左键单击以选择单元格，右键单击并选择“在查看器中打开值”并选择“文本”选项卡。
Using the same with phpMyAdmin, I get directly back the value of the OLD_PASSWORDcall. It's just an example, but is there a way to directly see such results in the output?
使用与 phpMyAdmin 相同的方法，我可以直接返回OLD_PASSWORD调用的值。这只是一个例子，但有没有办法直接在输出中看到这样的结果？
回答by spioter
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).
- 转到编辑>首选项
- 选择SQL 编辑器
- 在SQL Execution 下，检查将 BINARY/VARBINARY 视为非二进制字符串
- 重新启动 MySQL Workbench（您将不会收到此要求的提示或通知）。
In MySQL Workbench 6.0+
在 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).*
- 转到编辑>首选项
- 选择SQL 查询
- 在Query Results 下，检查Treat BINARY/VARBINARY as nonbinary string
- 重新启动 MySQL Workbench 不是强制性的（您不会被提示或通知此要求）。*
With this setting you will be able to concatenate fields without getting blobs.
使用此设置，您将能够在不获取 blob 的情况下连接字段。
I think this applies to versions 5.2.22 and later and is the result of this MySQL bug.
我认为这适用于 5.2.22 及更高版本，并且是这个 MySQL 错误的结果。
Disclaimer: I don't know what the downside of this setting is - maybe when you are selecting BINARY/VARBINARYvalues you will see it as plain text which may be misleading and/or maybe it will hinder performance if they are large enough?
免责声明：我不知道此设置的缺点是什么 - 也许当您选择BINARY/VARBINARY值时，您会将其视为纯文本，这可能会产生误导和/或如果它们足够大可能会影响性能？
回答by NKP
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>;
转到“WorkBench Preferences”--> 在“Query Results”下选择“SQL Editor”：勾选“Treat BINARY/VARBINARY as nonbinary string”
重新启动 MySQL WorkBench。
现在选择
SELECT SUBSTRING(BLOB<COLUMN_NAME>,1,2500) FROM <Table_name>;
回答by Dan Sherman
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
我不确定这是否回答了问题，但如果您右键单击字段中的“blob”图标（查看表格时），则会有一个“在编辑器中打开值”的选项。其中一个选项卡可让您查看 Blob。这是在版本。5.2.34
回答by CSTobey
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.
转换有效，但它很痛苦，所以我建议使用 spioter 的方法，除非您使用大量真正的 blob 数据。
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
您也可以转换为其他类型，甚至限制大小，但大多数时候我只使用 CHAR：http: //dev.mysql.com/doc/refman/5.5/en/cast-functions.html#function_cast
回答by dmag
Doesn't seem to be possible I'm afraid, its listed as a bug in workbench: http://bugs.mysql.com/bug.php?id=50692It would be very useful though!
恐怕似乎不可能，它被列为工作台中的错误：http: //bugs.mysql.com/bug.php?id= 50692 不过它会非常有用！
回答by glissi
had the same problem, according to the MySQL documentation, you can select a Substring of a BLOB:
有同样的问题，根据MySQL 文档，您可以选择 BLOB 的子字符串：
SELECT id, SUBSTRING(comment,1,2000) FROM t
HTH, glissi
HTH，滑音
回答by mbunch
I pieced a few of the other posts together, as the workbench 'preferences' fix did not work for me. (WB 6.3)
我将其他一些帖子拼凑在一起，因为工作台“首选项”修复对我不起作用。(WB 6.3)
SELECT CAST(`column` AS CHAR(10000) CHARACTER SET utf8) FROM `table`;
回答by Stephanie
Work bench 6.3
Follow High scoring answer then use UNCOMPRESS()
工作台 6.3
按照高分答案然后使用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).)
（简而言之：
1. 转到编辑 > 首选项
2. 选择 SQL 编辑器
3. 在 SQL 执行下，选中将 BINARY/VARBINARY 视为非二进制字符串
4. 重新启动 MySQL Workbench（不会提示或通知您此要求）。）
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".
如果你只是把UNCOMPRESS(<COLUMN_NAME>)你可以右键单击 blob 并单击“在编辑器中打开值”。
回答by chil
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.
就我而言，Workbench 不起作用。所以我使用上述解决方案将 blob 数据显示为文本。
回答by Kdecom
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
正如它在这个博客上所说的
