set - MySQL blob 转储到制表符分隔的文件

Question

我正在 Amazon 的 EC2 中迁移一个 MySQL 5.1 数据库，我遇到了用于图像存储的 longblob 数据类型的表问题。基本上，迁移后，longblob 列中的数据大小不同，因为字符编码似乎不同。

首先，这里有一个迁移前后的例子:

旧的:

x??]]??}?_ѕ??d??i|w?%?????q$??+?

新:

x��]]����_ѕ��d��i|w�%�����q$��+�

我检查了两台机器上的字符集变量，它们是相同的。我还检查了“显示创建表”，它们也相同。客户端的连接方式相同(没有设置名称，或指定字符集)。

这是我使用的 mysqldump 命令(我也尝试过不使用 --hex-blob):

mysqldump --hex-blob --default-character-set=utf8 --tab=. DB_NAME

这是我加载数据的方式:

mysql DB_NAME --default-character-set=utf8 -e "LOAD DATA INFILE 'EXAMPLE.txt' INTO TABLE EXAMPLE;"

以下是 MySQL 字符集变量(相同):

旧的:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     | 
| character_set_connection | latin1                     | 
| character_set_database   | utf8                       | 
| character_set_filesystem | binary                     | 
| character_set_results    | latin1                     | 
| character_set_server     | latin1                     | 
| character_set_system     | utf8                       | 
| character_sets_dir       | /usr/share/mysql/charsets/ | 
+--------------------------+----------------------------+

新:

+--------------------------+----------------------------+    
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     | 
| character_set_connection | latin1                     | 
| character_set_database   | utf8                       | 
| character_set_filesystem | binary                     | 
| character_set_results    | latin1                     | 
| character_set_server     | latin1                     | 
| character_set_system     | utf8                       | 
| character_sets_dir       | /usr/share/mysql/charsets/ | 
+--------------------------+----------------------------+

我不确定还有什么可以尝试运行 mysqldump 并使两台机器上的 blob 数据相同。任何提示将不胜感激。

Answer 1

这个问题似乎是 mysql (http://bugs.mysql.com/bug.php?id=27724) 中的错误。解决方案是不使用 mysqldump，而是为具有 blob 数据的表编写自己的 SELECT INTO OUTFILE 脚本。这是一个例子:

SELECT
COALESCE(column1, @nullval), 
COALESCE(column2, @nullval), 
COALESCE(HEX(column3), @nullval), 
COALESCE(column4, @nullval), 
COALESCE(column5, @nullval)
FROM table
INTO OUTFILE '/mnt/dump/table.txt' 
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

加载数据:

SET NAMES utf8;
LOAD DATA INFILE '/mnt/dump/table.txt'
INTO TABLE table
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
(column1, column1, @column1, column1, column1)
SET data = UNHEX(@column1)

这会正确加载 blob 数据。