如何在 MySQL 中从 BLOB 转换为 TEXT？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/948174/
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 do I convert from BLOB to TEXT in MySQL?
提问by Yuma
I have a whole lot of records where text has been stored in a blob in MySQL. For ease of handling I'd like to change the format in the database to TEXT... Any ideas how easily to make the change so as not to interrupt the data - I guess it will need to be encoded properly?
我有很多记录，其中文本已存储在 MySQL 的 blob 中。为了便于处理，我想将数据库中的格式更改为文本...任何想法如何轻松进行更改以免中断数据 - 我想它需要正确编码？
回答by Yuma
That's unnecessary. Just use SELECT CONVERT(column USING utf8) FROM..... instead of just SELECT column FROM...
那是不必要的。只需使用SELECT CONVERT(column USING utf8) FROM......而不是SELECT column FROM......
回答by ólafur Waage
Here's an example of a personwho wants to convert a blob to char(1000) with UTF-8encoding:
这是一个想要使用UTF-8编码将 blob 转换为 char(1000)的人的示例：
CAST(a.ar_options AS CHAR(10000) CHARACTER SET utf8)
This is his answer. There is probably much more you can read about CAST right here. I hope it helps some.
回答by Asped
I have had the same problem, and here is my solution:
我遇到了同样的问题，这是我的解决方案：
- create new columns of type text in the table for each blob column
- convert all the blobs to text and save them in the new columns
- remove the blob columns
- rename the new columns to the names of the removed ones
- 在表中为每个 blob 列创建文本类型的新列
- 将所有 blob 转换为文本并将它们保存在新列中
- 删除 blob 列
- 将新列重命名为已删除列的名称
ALTER TABLE mytable ADD COLUMN field1_new TEXT NOT NULL, ADD COLUMN field2_new TEXT NOT NULL; update mytable set field1_new = CONVERT(field1 USING utf8), field2_new = CONVERT(field2 USING utf8); alter table mytable drop column field1, drop column field2; alter table mytable change column field1_new field1 text, change column field2_new field2 text;
ALTER TABLE mytable ADD COLUMN field1_new TEXT NOT NULL, ADD COLUMN field2_new TEXT NOT NULL; update mytable set field1_new = CONVERT(field1 USING utf8), field2_new = CONVERT(field2 USING utf8); alter table mytable drop column field1, drop column field2; alter table mytable change column field1_new field1 text, change column field2_new field2 text;
回答by Jignesh Mesvaniya
You can do it very easily.
你可以很容易地做到这一点。
ALTER TABLE `table_name` CHANGE COLUMN `column_name` `column_name` LONGTEXT NULL DEFAULT NULL ;
The above query worked for me. I hope it helps you too.
上面的查询对我有用。我希望它也能帮助你。
回答by ankit
If you are using MYSQL-WORKBENCH, then you can select blob column normally and right click on column and click open value in editor. refer screenshot:
如果您使用的是MYSQL-WORKBENCH，那么您可以正常选择 blob 列并右键单击列并单击在编辑器中打开值。参考截图：
回答by Adam Sánchez Ayte
Or you can use this function:
或者您可以使用此功能：
DELIMITER $$
CREATE FUNCTION BLOB2TXT (blobfield VARCHAR(255)) RETURNS longtext
DETERMINISTIC
NO SQL
BEGIN
RETURN CAST(blobfield AS CHAR(10000) CHARACTER SET utf8);
END
$$
DELIMITER ;
回答by Dean Or
None of these answers worked for me. When converting to UTF8, when the encoder encounters a set of bytes it can't convert to UTF8 it will result in a ? substitution which results in data loss. You need to use UTF16:
这些答案都不适合我。转换为 UTF8 时，当编码器遇到一组无法转换为 UTF8 的字节时，会导致 ? 导致数据丢失的替换。您需要使用 UTF16：
SELECT
blobfield,
CONVERT(blobfield USING utf16),
CONVERT(CONVERT(blobfield USING utf16), BINARY),
CAST(blobfield AS CHAR(10000) CHARACTER SET utf16),
CAST(CAST(blobfield AS CHAR(10000) CHARACTER SET utf16) AS BINARY)
You can inspect the binary values in MySQL Workbench. Right click on the field -> Open Value in Viewer-> Binary. When converted back to BINARY the binary values should be the same as the original.
您可以在 MySQL Workbench 中检查二进制值。右键单击该字段 -> 在查看器中打开值 -> 二进制。转换回 BINARY 时，二进制值应与原始值相同。
Alternatively, you can just use base-64 which was made for this purpose:
或者，您可以只使用为此目的而制作的 base-64：
SELECT
blobfield,
TO_BASE64(blobfield),
FROM_BASE64(TO_BASE64(blobfield))
回答by marcosn
Using phpMyAdmin you can also set the options to show BLOB content and show complete text.
使用 phpMyAdmin，您还可以设置选项以显示 BLOB 内容和显示完整文本。
