Mysqldump缺失--hex-blob致varbinary数据损坏，求修复方案

问题本质 #

用mysqldump导出时没加--hex-blob参数，varbinary这类二进制字段会被当作普通文本处理，里面的不可见字节、特殊字符会被直接输出或转义，导致导入时数据长度超出字段限制，甚至数据本身损坏。

可行修复方案 #

方案1：手动修复单条报错数据

1. 打开user_data.sql文件，定位到报错的第1行记录，找到ip字段的具体值（可能是乱码、\x00这类转义字符）。
2. 将该值转换为十六进制字符串——比如二进制的192.168.1.1对应的十六进制是C0A80101。
3. 把SQL中该字段的赋值替换为UNHEX('C0A80101')，比如原语句INSERT INTO ... (ip) VALUES ('乱码内容')改成INSERT INTO ... (ip) VALUES (UNHEX('C0A80101'))。
4. 重新执行导入操作。

方案2：批量修复SQL文件（适合大量数据）

用脚本批量处理SQL文件中的ip字段值，将其转换为十六进制后用UNHEX包裹。以下是一个简单的Python示例：

import re

# 读取原始SQL文件
with open('user_data.sql', 'r', encoding='latin1') as f:
    content = f.read()

# 匹配ip字段的赋值语句（假设格式为ip='xxx'）
def replace_ip(match):
    original_value = match.group(1)
    # 将原始字符串转换为十六进制
    hex_value = original_value.encode('latin1').hex()
    return f"ip=UNHEX('{hex_value}')"

# 替换所有匹配的ip字段
fixed_content = re.sub(r"ip='(.*?)'", replace_ip, content)

# 保存修复后的SQL文件
with open('fixed_user_data.sql', 'w', encoding='latin1') as f:
    f.write(fixed_content)

执行脚本后，用source /home/migration/fixed_user_data.sql重新导入数据。

方案3：临时修改表结构绕过导入限制

1. 创建一个临时表，结构和目标表完全一致，仅将ip字段类型改为varchar(255)（确保长度足够容纳损坏的数据）：

CREATE TABLE temp_user_data LIKE user_data;
ALTER TABLE temp_user_data MODIFY COLUMN ip varchar(255);

2. 将数据导入临时表：

source /home/migration/user_data.sql

3. 将临时表的数据转换后插入原表（需列出所有字段，确保顺序一致）：

INSERT INTO user_data SELECT id, name, CAST(ip AS VARBINARY), ... 
FROM temp_user_data;

4. 验证数据正确性后，删除临时表：

DROP TABLE temp_user_data;

注意：此方法可能会丢失部分特殊字节数据，建议先验证少量数据的转换结果。

验证修复结果 #

导入完成后，可通过以下语句验证ip字段是否正确（如果是IPv4地址）：

SELECT INET_NTOA(ip) FROM user_data LIMIT 10;

如果输出正常的IP地址，说明修复成功。

内容的提问来源于stack exchange，提问作者Hatsune121