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 数据。