MySQL 5.6中含SET类型字段的表备份数据异常问题排查

看起来你遇到的问题是在使用INSERT INTO ... SELECT ...备份SET类型字段时，部分元素丢失了——具体是原表中plan1没有被正确导入到备份表中。虽然你说两张表结构完全一致，但这种情况通常是表结构的细微差异或者SET类型的隐式转换问题导致的，下面是具体的排查步骤和解决方案：

一、先确认两张表的SET定义完全一致 #

DESCRIBE命令只会显示基本的字段类型，可能隐藏了一些关键细节，比如字符集、排序规则或者SET选项的大小写/空格差异。请执行以下命令对比两张表的完整结构：

-- 查看原表和备份表的完整CREATE语句
SHOW CREATE TABLE Organization;
SHOW CREATE TABLE Organization_backup;

重点检查Plans字段的定义，确保：

• SET选项的顺序完全一致（比如都是set('plan1','plan2','plan3')，没有调换顺序）
• 选项的大小写完全匹配（比如原表是'Plan1'而备份表是'plan1'会导致不匹配）
• 选项没有多余的空格（比如原表是'plan1 '带空格，备份表是'plan1'）
• 字段的字符集和排序规则一致，可以用这个命令查看：

  SELECT column_name, column_type, character_set_name, collation_name 
  FROM information_schema.columns 
  WHERE table_schema = 'db' 
    AND table_name IN ('Organization', 'Organization_backup') 
    AND column_name = 'Plans';
  

如果发现差异，修正备份表的结构使其和原表完全一致，然后重新执行插入语句。

二、检查SET字段的实际存储值 #

SET类型在MySQL内部是用整数存储的（每个选项对应一个二进制位：plan1对应1，plan2对应2，plan3对应4）。你可以通过转换为整数来确认原表和备份表的实际值：

-- 原表的实际存储值
SELECT ID, Plans, CAST(Plans AS UNSIGNED) AS plans_int 
FROM Organization WHERE ID = 'ORG1';

-- 备份表的实际存储值
SELECT ID, Plans, CAST(Plans AS UNSIGNED) AS plans_int 
FROM Organization_backup WHERE ID = 'ORG1';

如果原表的plans_int是7（1+2+4），而备份表是6（2+4），说明插入时plan1对应的位没有被设置——这大概率是因为字符串转换时不匹配（比如大小写、字符集问题）。

三、可靠的解决方案 #

方案1：绕过字符串转换，直接插入数值 #

既然SET的本质是整数，你可以直接插入转换后的数值，避免字符串解析的问题：

INSERT INTO Organization_backup (ID, Plans, CreatedTS) 
SELECT ID, CAST(Plans AS UNSIGNED), CreatedTS 
FROM Organization;

方案2：使用mysqldump进行备份（更推荐） #

手动创建表和插入数据容易出错，使用官方的mysqldump工具是更可靠的备份方式：

# 导出原表数据
mysqldump -u 你的用户名 -p db Organization > Organization_backup.sql

# 修改备份文件中的表名为Organization_backup（可选，避免覆盖原表）
sed -i 's/Organization/Organization_backup/g' Organization_backup.sql

# 导入备份表到数据库
mysql -u 你的用户名 -p db < Organization_backup.sql

这样可以确保表结构和数据完全一致，不会出现SET字段的转换问题。

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