MySQL Blob to Text（数据和数据类型）

I have a mysql database that holds content as a blob, for whatever reason those developers chose to use a blob is out of my control. Is it possible to convert the data to text and the data type to text?

我有一个mysql数据库，它将内容保存为blob，无论出于何种原因，这些开发人员选择使用blob都是我无法控制的。是否可以将数据转换为文本，将数据类型转换为文本？

2 个解决方案

#1

have you tried the alter table command ?

你试过alter table命令吗？

alter table mytable change mycolumn mycolumn text;

from http://forums.mysql.com/read.php?103,164923,167648#msg-167648 it looks like you can use CAST.

从http://forums.mysql.com/read.php?103,164923,167648#msg-167648看起来你可以使用CAST。

you could create a new (TEXT) column, then fill it in with an update command:

您可以创建一个新的（TEXT）列，然后使用更新命令填充它：

update mytable set myNewColumn = CAST(myOldColumn AS CHAR(10000) CHARACTER SET utf8)

#2

Converting the field from blob to text truncates all characters > 127. In my case we have lots of european characters, so this was not an option. Here's what I did:

将字段从blob转换为文本会截断所有字符> 127.在我的情况下，我们有很多欧洲字符，所以这不是一个选项。这是我做的：

Create temp field as text
将临时字段创建为文本
Copy the blob field to the temp field: UPDATE tbl SET col_temp = CONVERT(col USING latin1); In this case my blob held latin1 encoded chars
将blob字段复制到temp字段：UPDATE tbl SET col_temp = CONVERT（col USING latin1）;在这种情况下，我的blob持有latin1编码的字符
Convert actual field to text datatype
将实际字段转换为text数据类型
Copy temp to actual field
将temp复制到实际字段
Remove temp column
删除临时列

Not exactly straightforward but it worked and no data loss. I'm using Version: '5.1.50-community'

不完全简单，但它工作，没有数据丢失。我正在使用版本：'5.1.50-community'

#1