blocks|key|1539965|text|这是不必要的。只需使用SELECT+CONVERT(column+USING+utf8)+FROM.....不仅仅是SELECT+column+FROM..。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1539966|entityMap^0|B|12|1M|I|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|M|8|@]|D|@]|E|$]]]|G|$]]

That's unnecessary. Just use <code>SELECT CONVERT(column USING utf8) FROM</code>..... instead of just <code>SELECT column FROM</code>...

blocks|key|1539922|text|下面是一个使用UTF-8编码将blob转换为char(1000)的a+person示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1539923|CAST(a.ar_options+AS+CHAR(10000)+CHARACTER+SET+utf8)|code-block|syntax|javascript|1539924|这就是他的答案。关于CAST+right+here，你可以读到更多的东西。我希望它能有所帮助。|1539925|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/UTF-8|1|http://forums.mysql.com/read.php?103,164923,167648#msg-167648|2|http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html^0|7|5|0|X|8|1|0|0|F|A|2|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]|$A|10|B|11|1|12]]|C|$]]|$1|D|3|E|5|F|7|13|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|14|8|@]|9|@$A|15|B|16|1|17]]|C|$]]|$1|K|3|-4|5|6|7|18|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]|S|$5|N|O|P|C|$Q|T]]|U|$5|N|O|P|C|$Q|V]]]]

Here's an example of <a href="http://forums.mysql.com/read.php?103,164923,167648#msg-167648" rel="noreferrer">a person</a> who wants to convert a blob to char(1000) with <a href="http://en.wikipedia.org/wiki/UTF-8" rel="noreferrer">UTF-8</a> encoding:

<pre><code>CAST(a.ar_options AS CHAR(10000) CHARACTER SET utf8)
</code></pre>

This is his answer. There is probably much more you can read about CAST <a href="http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html" rel="noreferrer">right here</a>. I hope it helps some.

blocks|key|1324885|text|你可以很容易地做到这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1324886|ALTER+TABLE+`table_name`+CHANGE+COLUMN+`column_name`+`column_name`+LONGTEXT+NULL+DEFAULT+NULL+;|code-block|syntax|javascript|1324887|上面的查询对我有效。我希望这对你也有帮助。|1324888|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You can do it very easily.

<pre><code>ALTER TABLE `table_name` CHANGE COLUMN `column_name` `column_name` LONGTEXT NULL DEFAULT NULL ;
</code></pre>

The above query worked for me. I hope it helps you too.

blocks|key|1540083|text|这些答案对我来说都不起作用。当转换为UTF8时，当编码器遇到一组无法转换为UTF8的字节时，将导致？替换会导致数据丢失。您需要使用UTF16：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1540084|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)|code-block|syntax|javascript|1540085|您可以在MySQL工作台中检查二进制值。右键单击字段->+Open+Value+in+Viewer->+Binary。当转换回二进制值时，二进制值应该与原始值相同。|1540086|或者，您可以只使用base-64，它就是为此目的而制作的：|1540087|SELECT
++++blobfield,
++++TO_BASE64(blobfield),
++++FROM_BASE64(TO_BASE64(blobfield))|1540088|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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:

<pre><code>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)
</code></pre>

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.

Alternatively, you can just use base-64 which was made for this purpose:

<pre><code>SELECT
 blobfield,
 TO_BASE64(blobfield),
 FROM_BASE64(TO_BASE64(blobfield))
</code></pre>

blocks|key|1539996|text|或者，您可以使用此函数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1539997|DELIMITER+$$

CREATE+FUNCTION+BLOB2TXT+(blobfield+VARCHAR(255))+RETURNS+longtext
DETERMINISTIC
NO+SQL
BEGIN
+++++++RETURN+CAST(blobfield+AS+CHAR(10000)+CHARACTER+SET+utf8);
END
$$


DELIMITER+;|code-block|syntax|javascript|1539998|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Or you can use this function:

<pre><code>DELIMITER $$

CREATE FUNCTION BLOB2TXT (blobfield VARCHAR(255)) RETURNS longtext
DETERMINISTIC
NO SQL
BEGIN
 RETURN CAST(blobfield AS CHAR(10000) CHARACTER SET utf8);
END
$$


DELIMITER ;
</code></pre>

blocks|key|1540201|text|​|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1540202|📷|atomic|offset|length|1540203|使用phpMyAdmin，您还可以设置选项以显示BLOB内容和显示完整文本。|1540204|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/c9219737b48d4ed843b6fc8bce6bfcb1.png|imageAlt^0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|S|8|@]|9|@$E|T|F|U|1|V]]|A|$]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|J|$K|$5|L|M|N|A|$O|P|Q|-4]]]]

<a href="https://i.stack.imgur.com/I82Ps.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/I82Ps.png" alt="phpMyAdmin screenshot"></a> Using phpMyAdmin you can also set the options to show BLOB content and show complete text.

blocks|key|1325143|text|SELECCT+TO_BASE64(blobfield)++
FROM+the+Table|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1325144|对我很管用。|unstyled|1325145|CAST(blobfield+AS+CHAR(10000)+CHARACTER+SET+utf8)和CAST(blobfield+AS+CHAR(10000)+CHARACTER+SET+utf16)没有显示我想要获取的文本值。|1325146|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>SELECCT TO_BASE64(blobfield) 
FROM the Table
</code></pre>
worked for me.
The CAST(blobfield AS CHAR(10000) CHARACTER SET utf8) and CAST(blobfield AS CHAR(10000) CHARACTER SET utf16) did not show me the text value I wanted to get.

blocks|key|1325218|text|我的MariaDB记录也有同样的问题。它(由我的同事)使用以下命令解决|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1325219|select
uncompress(blobfield)
from+table|code-block|syntax|javascript|1325220|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I had the same issue with my MariaDB records.
It was solved (by my colleague) using
<pre class="lang-sql prettyprint-override"><code>select
uncompress(blobfield)
from table
</code></pre>

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?

How do I convert from BLOB to TEXT in MySQL?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有很多记录，其中文本存储在MySQL的blob中。为了便于处理，我想将数据库中的格式更改为文本...您是否知道如何轻松地进行更改以避免中断数据-我猜需要对其进行适当的编码？

问如何在MySQL中将BLOB转换为文本？
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中将BLOB转换为文本？EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中将BLOB转换为文本？
EN