MySQL blob导出sql文件乱码

今天想将数据库里quartz的TRIGGERS表导成sql文件，所以很熟练的就使用了mysqldump -h127.0.0.1 -udev -p quartz QRTZ_TRIGGERS >/home/dev/QRTZ_TRIGGERS.sql命令直接操作，但是打开sql文件发现里面居然是乱码的。

前言

因为之前导出表没有出现过这种情况。所以就查一下怎么解决。

现象

表结构

CREATE TABLE `QRTZ_TRIGGERS` (
  `SCHED_NAME` varchar(120) NOT NULL,
  `TRIGGER_NAME` varchar(200) NOT NULL,
  `TRIGGER_GROUP` varchar(200) NOT NULL,
  `JOB_NAME` varchar(200) NOT NULL,
  `JOB_GROUP` varchar(200) NOT NULL,
  `DESCRIPTION` varchar(250) DEFAULT NULL,
  `NEXT_FIRE_TIME` bigint(13) DEFAULT NULL,
  `PREV_FIRE_TIME` bigint(13) DEFAULT NULL,
  `PRIORITY` int(11) DEFAULT NULL,
  `TRIGGER_STATE` varchar(16) NOT NULL,
  `TRIGGER_TYPE` varchar(8) NOT NULL,
  `START_TIME` bigint(13) NOT NULL,
  `END_TIME` bigint(13) DEFAULT NULL,
  `CALENDAR_NAME` varchar(200) DEFAULT NULL,
  `MISFIRE_INSTR` smallint(2) DEFAULT NULL,
  `JOB_DATA` blob,
  PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  KEY `IDX_QRTZ_T_J` (`SCHED_NAME`,`JOB_NAME`,`JOB_GROUP`),
  KEY `IDX_QRTZ_T_JG` (`SCHED_NAME`,`JOB_GROUP`),
  KEY `IDX_QRTZ_T_C` (`SCHED_NAME`,`CALENDAR_NAME`),
  KEY `IDX_QRTZ_T_G` (`SCHED_NAME`,`TRIGGER_GROUP`),
  KEY `IDX_QRTZ_T_STATE` (`SCHED_NAME`,`TRIGGER_STATE`),
  KEY `IDX_QRTZ_T_N_STATE` (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`,`TRIGGER_STATE`),
  KEY `IDX_QRTZ_T_N_G_STATE` (`SCHED_NAME`,`TRIGGER_GROUP`,`TRIGGER_STATE`),
  KEY `IDX_QRTZ_T_NEXT_FIRE_TIME` (`SCHED_NAME`,`NEXT_FIRE_TIME`),
  KEY `IDX_QRTZ_T_NFT_ST` (`SCHED_NAME`,`TRIGGER_STATE`,`NEXT_FIRE_TIME`),
  KEY `IDX_QRTZ_T_NFT_MISFIRE` (`SCHED_NAME`,`MISFIRE_INSTR`,`NEXT_FIRE_TIME`),
  KEY `IDX_QRTZ_T_NFT_ST_MISFIRE` (`SCHED_NAME`,`MISFIRE_INSTR`,`NEXT_FIRE_TIME`,`TRIGGER_STATE`),
  KEY `IDX_QRTZ_T_NFT_ST_MISFIRE_GRP` (`SCHED_NAME`,`MISFIRE_INSTR`,`NEXT_FIRE_TIME`,`TRIGGER_GROUP`,`TRIGGER_STATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

导出sql文件部分内容截取

INSERT INTO `QRTZ_TRIGGERS` VALUES ('dynamicQrtzScheduler','tri-65705-eb0e74b1842b4027a377c43220d69054-to','trigrp-65705-eb0e74b1842b4027a377c43220d69054','job-65705-to','jobgrp-65705',NULL,1604620800000,1604534400000,5,'WAITING','CRON',1589279232000,0,NULL,1,_binary '¬\í\0^Esr\0^Uorg.quartz.JobDataMap<9f>°<83>è¿©°\Ë^B\0\0xr\0&org.quartz.utils.StringKeyDirtyFlagMap<82>^H\è\Ãû\Å](^B\0^AZ\0^SallowsTransientDataartz.utils.DirtyFlagMap^S\æ.­(v\n\Î^B\0^BZ\0^EdirtyL\0^Cmapt\0^OLjava/util/Map;xp^Asr\0^Qjava.util.HashMap^E^G\ÚÁ\Ã^V`\Ñ^C\0^BF\0\nloadFactorI\0    thresholdxp?@\0\0\0\0\0^Lw^H\0\0\0^P\0\0\0^At\0\nscheduleIdt\0 eb0e74b1842b4027a377c43220d69054'dynamicQrtzScheduler','tri-65706-b15eb9393c104988b983c0330cd307f2-from','trigrp-65706-b15eb9393c104988b983c0330cd307f2','job-65706-from','jobgrp-65706',NULL,1604577600000,1604494957613,5,'WAITING','CRON',1589279232000,0,NULL,1,_binary '¬\í\0^Esr\0^Uorg.quartz.JobDataMap<9f>°<83>è¿©°\Ë^B\0\0xr\0&org.quartz.utils.StringKeyDirtyFlagMap<82>^H\è\Ãû\Å](^B\0^AZ\0^SallowsTransientDataxr\0^]org.quartz.utils.DirtyFlagn\Î^B\0^BZ\0^EdirtyL\0^Cmapt\0^OLjava/util/Map;xp^Asr\0^Qjava.util.HashMap^E^G\ÚÁ\Ã^V`\Ñ^C\0^BF\0\nloadFactorI\0      thresholdxp?@\0\0\0\0\0^Lw^H\0\0\0^P\0\0\0^At\0\nscheduleIdt\0 b15eb9393c104988b983c0330cd307f2x\0'),('dynamicQrtzScheduler','txxS_5850ed3578dd-65706-b15eb9393c104988b983c0330cd307f2-to','trigrp-65706-b15eb9393c104988b983c0330cd307f2','job-65706-to','jobgrp-65706',NULL,1598486400000,1598411308915,5,'ERROR','CRON',1589279232000,0,NULL,1,_binary '¬\í\0^Esr\0^Uorg.quartz.JobDataMap<9f>°<83>è¿©°\Ë^B\0\0xr\0&org.quartz.utils.StringKeyDirtyFlagMap<82>^H\è\Ãû\Å](^B\0^AZ\0^SallowsTransientDataxr\0^]org.quartz.utils.DirtyFlagMap^S\æ.­(v\n\Î^B\0^BZ\0^EdirtyL\a/util/Map;xp^Asr\0^Qjava.util.HashMap^E^G\ÚÁ\Ã^V`\Ñ^C\0^BF\0\nloadFactorI\0    thresholdxp?@\0\0\0\0\0^Lw^H\0\0\0^P\0\0\0^At\0\nscheduleIdt\0 b15eb9393c104988b983c0330cd307f2x\0'),('dynamicQrtzScheduler','tri-65707-c0612608d3e1811430344fca9a1c-from','trigrp-65707-c0612608d3ee41b1811430344fca9a1c','job-65707-from','jobgrp-65707',NULL,1604534400000,1604457074711,5,'WAITING','CRON',1589279232000,0,NULL,1,_binary '¬\í^Esr\0^Uorg.quartz.JobDataMap<9f>°<83>è¿©°\Ë^B\0\0xr\0&org.quartz.utils.StringKeyDirtyFlagMap<82>^H\è\Ãû\Å](^B\0^AZ\0^SallowsTransientDataxr\0^]org.quartz.utils.DirtyFlagMap^S\æ.­(v\n\Î^B\0^BZ\0^EdirtyL\0^Cmapt\0^OLjava/util/Map;xp^Asr\0ashMap^E^G\ÚÁ\Ã^V`\Ñ^C\0^BF\0\nloadFactorI\0      thresholdxp?@\0\0\0\0\0^Lw^H\0\0\0^P\0\0\0^At\0\nscheduleIdt\0 c0612608d3ee41b1811430344fca9a1cx\0'),('dynamicQrtzScheduler','tri-65707-c0612608d3ee41b1811430344fca9a1c-to','tri-65707-c0612608d3ee41b1811430344fca9a1c','job-65707-to','jobgrp-65707',NULL,1604577600000,1604491200000,5,'WAITING','CRON',1589279232000,0,NULL,1,_binary '¬\í\0^Esr\0^Uorg.quartz.JobDataMap<9f<83>è¿©°\Ë^B\0\0xr\0&org.quartz.utils.StringKeyDirtyFlagMap<82>^H\è\Ãû\Å](^B\0^AZ\0^SallowsTransientDataxr\0^]org.quartz.utils.DirtyFlagMap^S\æ.­(v\n\Î^B\0^BZ\0^EdirtyL\0^Cmapt\0^OLjava/util/Map;xp^Asr\0^Qjava.util.HashMap^E^G\ÚÁ\Ã^V`\

开始以为是文件从centos上下载导windows上编码格式问题，就在原始的centos上打开文件，结果还是乱码的。

解决方法

后来在网上搜了搜解决方法，基本就如下两种

指定字符集编码(未解决)

1. 猜测是不是数据库字符编码存在问题，因为表是utf-8编码格式的，所以在导出命令里加了--default-character-set=utf8。但是试了下导出的文件还是乱码的，应该不是这个原因引起的。

blob字段问题(解决)

1. 根据网上的提示，发现表字段JOB_DATA是blob类型的。需要在命令上加上参数--hex-blob,加上命令mysqldump -h127.0.0.1 -udev --hex-blob -p quartz QRTZ_TRIGGERS >/home/dev/QRTZ_TRIGGERS.sql导出后blob类型的字段被转成了16进制。部分sql内容如下

   INSERT INTO `QRTZ_TRIGGERS` VALUES ('dynamicQrtzScheduler','tri-306056-c077eb0407ec464ebd061c221eead86b-from','trigrp-306056-c077eb0407ec464ebd061c221eead86b','job-306056-from','jobgrp-306056',NULL,1604976420000,1604371620000,5,'WAITING','CRON',1600138289000,0,NULL,1,0xACED0005737200156F72672E71756172747A2E4A6F62446174614D61709FB083E8BFA9B0CB020000787200266F72672E71756172747A2E7574696C732E537472696E674B65794469727479466C61674D61708208E8C3FBC55D280200015A0013616C6C6F77735472616E7369656E74446174617872001D6F72672E71756172747A2E7574696C732E4469727479466C61674D617013E62EAD28760ACE0200025A000564697274794C00036D617074000F4C6A6176612F7574696C2F4D61703B787001737200116A6176612E7574696C2E486173684D61700507DAC1C31660D103000246000A6C6F6164466163746F724900097468726573686F6C6478703F4000000000000C7708000000100000000174000A7363686564756C65496474002063303737656230343037656334363465626430363163323231656561643836627800),('dynamicQrtzScheduler','tri-306056-c077eb0407ec464ebd061c221eead86b-to','trigrp-306056-c077eb0407ec464ebd061c221eead86b','job-306056-to','jobgrp-306056',NULL,1604976720000,1604371920000,5,'WAITING','CRON',1600138289000,0,NULL,1,0xACED0005737200156F72672E71756172747A2E4A6F62446174614D61709FB083E8BFA9B0CB020000787200266F72672E71756172747A2E7574696C732E537472696E674B65794469727479466C61674D61708208E8C3FBC55D280200015A0013616C6C6F77735472616E7369656E74446174617872001D6F72672E71756172747A2E7574696C732E4469727479466C61674D617013E62EAD28760ACE0200025A000564697274794C00036D617074000F4C6A6176612F7574696C2F4D61703B787001737200116A6176612E7574696C2E486173684D61700507DAC1C31660D103000246000A6C6F6164466163746F724900097468726573686F6C6478703F4000000000000C7708000000100000000174000A7363686564756C65496474002063303737656230343037656334363465626430363163323231656561643836627800),

sql重新再导入导新的库里也是可以的，问题终于解决了。

总结

1. --hex-blob 使用十六进制表示法转储二进制列, 即以16进制导出blob字段数据。

2. 查了下mysql官方mysqldump参数的解释， 网址在最下面相关链接1

   --hex-blob
   
   Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263). The affected data types are BINARY, VARBINARY, BLOB types, BIT, all spatial data types, and other non-binary data types when used with the binary character set.

3. 网上说如果不用–hex-blob参数, mysqldump和MySQL workbench都还是会导致blob字段错乱。本人没有测试过。

相关链接

1. –hex-blob参数官方解释：https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_hex-blob