用MySQL替换BLOB文本中的单词

I've got a huge mysql table (called tcountriesnew) and a column (called slogen, blob type). In each of those slogen blobs I'd like to replace a word, for example: banana to apple.

我有一个巨大的mysql表(称为tcountriesnew)和一个列(称为slogen,blob类型)。在每一个slogen blob中,我想替换一个单词,例如:banana to apple。

Unfortunately I tried to print all the rows with word banana, and it did not work.

不幸的是我尝试用单词banana打印所有行,但它没有用。

select * from tcountriesnew where slogen like '%banana%';

select * from tcountriesnew其中slogen喜欢'%banana%';

Please help me.

请帮帮我。

What i missed, what is the problem with my query?

我错过了什么,我的查询有什么问题?

How can i replace text in blob?

如何替换blob中的文本?

3 个解决方案

#1

Depends what you mean by "replace" show in select:

取决于你选择的“替换”节目的意思:

select replace(slogen, 'bananas', 'apples') from tcountriesnew where slogen like '%bananas%';

Or update data in a table:

或者更新表格中的数据:

update tcountriesnew set slogen=replace(slogen, 'bananas', 'apples') where slogen like '%bananas%';

BTW. Why are you using blob for text? You should use text type for textual data and blob for binary data.

BTW。你为什么用blob作为文本?您应该将文本类型用于文本数据,将blob用于二进制数据。

#2

In some cases it is needed to save texts BLOB. In my case, for whatever reason Drupal 7 developers choose to use a blob for all TEXT columns - this is out of developer's control.

在某些情况下,需要保存文本BLOB。在我的情况下,无论出于何种原因,Drupal 7开发人员选择对所有TEXT列使用blob - 这是开发人员无法控制的。

To convert a blob to text, use the MySql convert function. Then you have to save it into the database and convert it again to blob - but this is handled automatically by MySQL. So the following query would do the trick:

要将blob转换为文本,请使用MySql转换函数。然后你必须将它保存到数据库并再次转换为blob - 但这是由MySQL自动处理的。所以以下查询可以解决这个问题:

UPDATE tcountriesnew 
SET slogen = replace(CONVERT(slogen USING utf8), 'bananas', 'apples')
WHERE slogen LIKE '%bananas%';

On MySQL 5.5, This resolved my issue completely.

在MySQL 5.5上,这完全解决了我的问题。

Also, configure your PhpMyAdmin to show Blob data

另外,配置PhpMyAdmin以显示Blob数据

#3

Which version are you using ? Maybe it's this bug : http://bugs.mysql.com/bug.php?id=27. Otherwise try to cast your blob column.

你使用的是哪个版本?也许是这个错误:http://bugs.mysql.com/bug.php?id = 27。否则尝试转换blob列。

#1