UnicodeDecodeError: 'utf-8' codec can't decode byte 0xff in position 0: invalid start byte

[chenpcpolive-prog]

mysql> select version()  ;show create table  all_mysql_types \G;
+------------+
| version()  |
+------------+
| 5.7.44-log |
+------------+
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: all_mysql_types
Create Table: CREATE TABLE `all_mysql_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `tinyint_col` tinyint(4) DEFAULT NULL,
  `smallint_col` smallint(6) DEFAULT NULL,
  `mediumint_col` mediumint(9) DEFAULT NULL,
  `int_col` int(11) DEFAULT NULL,
  `bigint_col` bigint(20) DEFAULT NULL,
  `float_col` float DEFAULT NULL,
  `double_col` double DEFAULT NULL,
  `decimal_col` decimal(10,2) DEFAULT NULL,
  `char_col` char(20) DEFAULT NULL,
  `varchar_col` varchar(100) DEFAULT NULL,
  `tinytext_col` tinytext,
  `text_col` text,
  `mediumtext_col` mediumtext,
  `longtext_col` longtext,
  `enum_col` enum('a','b','c','d') DEFAULT NULL,
  `set_col` set('x','y','z','w') DEFAULT NULL,
  `binary_col` binary(20) DEFAULT NULL,
  `varbinary_col` varbinary(100) DEFAULT NULL,
  `tinyblob_col` tinyblob,
  `blob_col` blob,
  `mediumblob_col` mediumblob,
  `longblob_col` longblob,
  `date_col` date DEFAULT NULL,
  `datetime_col` datetime DEFAULT NULL,
  `timestamp_col` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `time_col` time DEFAULT NULL,
  `year_col` year(4) DEFAULT NULL,
  `json_col` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COMMENT='MySQL全字段类型测试表'
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> 
 

[root@ykas-sr-5156 ibd2sql-main]# python3 main.py  ../all_mysql_types.ibd 
Traceback (most recent call last):
  File "main.py", line 178, in <module>
    aa = frm2sdi.MYSQLFRM(FRMFILENAME).get_sdi_page()
  File "/root/cp/recover/ibd2sql-main/ibd2sql/frm2sdi.py", line 133, in __init__
    self._read_columns()
  File "/root/cp/recover/ibd2sql-main/ibd2sql/frm2sdi.py", line 343, in _read_columns
    self.COLUMNS['field'][i]['comment'] = self.read(self.COLUMNS['field'][i]['metadata']['comment_length']).decode()
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xff in position 0: invalid start byte
[root@ykas-sr-5156 ibd2sql-main]# 

我建了一张全字段的表，目前检测下来应该是有部分列类弄不兼容，大佬有办法修复么？

[ddcw]

从报错来看是, 解析frm文件中字段注释的时候编码问题, 已复现, 我尽快修复.

如果不关心注释的话, 可以修改ibd2sql/frm2sdi.py的341行代码为: self.COLUMNS['field'][i]['comment'] = self.read(self.COLUMNS['field'][i]['metadata']['comment_length']).hex() , 即不做转码处理

[ddcw]

感谢反馈, 已修复. 可下载最新版验证: https://github.com/ddcw/ibd2sql/archive/refs/heads/ibd2sql-v2.x.zip
原因为: enum/set元数应该在默认值之前读取.
验证结果:

[chenpcpolive-prog]

验证没问题，真牛！！感谢

[chenpcpolive-prog]

但是数据恢复这块的话，还是有点问题。```
INSERT INTO ...`all_mysql_types` VALUES (1,1,10,100,1000,10000,1.100000023841858,2.2,100.55,'char1 ','varchar1','tiny1','text1','medium1','long1','a','x,y',0x62696e31000000,0x00,0x00,0x00,0x00,0x00000000000000000076617262,'13495-01-17','1349-07-25 20:18:15','2081-09-18 18:36:37','42:00:00',2005,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (2,2,11,101,1001,10001,1.2000000476837158,2.3,101.55,'char2 ','varchar2','tiny2','text2','medium2','long2','b','x,z',0x62696e32000000,0x00,0x00,0x00,0x00,0x00000000000000000076617262,'13495-01-18','1349-07-25 20:18:15','2081-09-19 12:48:53','74:00:00',2005,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (3,3,12,102,1002,10002,1.2999999523162842,2.4,102.55,'char3 ','varchar3','tiny3','text3','medium3','long3','c','y,z',0x62696e33000000,0x00,0x00,0x00,0x00,0x00000000000000000076617262,'13495-01-19','1349-07-25 20:18:15','2081-09-20 07:01:09','106:00:00',2005,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (4,4,13,103,1003,10003,1.399999976158142,2.5,103.55,'char4 ','varchar4','tiny4','text4','medium4','long4','d','x,w',0x62696e34000000,0x00,0x00,0x00,0x00,0x00000000000000000076617262,'13495-01-20','1349-07-25 20:18:15','2081-09-21 01:13:25','138:00:00',2005,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (5,5,14,104,1004,10004,1.5,2.6,104.55,'char5 ','varchar5','tiny5','text5','medium5','long5','a','y,w',0x62696e35000000,0x00,0x00,0x00,0x00,0x00000000000000000076617262,'13495-01-21','1349-07-25 20:18:15','2081-09-21 19:25:41','170:00:00',2005,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (6,6,15,105,1005,10005,1.600000023841858,2.7,105.55,'char6 ','varchar6','tiny6','text6','medium6','long6','b','z,w',0x62696e36000000,0x00,0x00,0x00,0x00,0x00000000000000000076617262,'13495-01-22','1349-07-25 20:18:15','2081-09-22 13:37:57','202:00:00',2005,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (7,7,16,106,1006,10006,1.7000000476837158,2.8,106.55,'char7 ','varchar7','tiny7','text7','medium7','long7','c','x,y,z',0x62696e37000000,0x00,0x00,0x00,0x00,0x00000000000000000076617262,'13495-01-23','1349-07-25 20:18:15','2081-09-23 07:50:13','234:00:00',2005,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (8,8,17,107,1007,10007,1.7999999523162842,2.9,107.55,'char8 ','varchar8','tiny8','text8','medium8','long8','d','x,y,w',0x62696e38000000,0x00,0x00,0x00,0x00,0x00000000000000000076617262,'13495-01-24','1349-07-25 20:18:15','2081-09-24 02:02:29','266:00:00',2005,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (9,9,18,108,1008,10008,1.899999976158142,3.0,108.55,'char9 ','varchar9','tiny9','text9','medium9','long9','a','x,z,w',0x62696e39000000,0x00,0x00,0x00,0x00,0x00000000000000000076617262,'13495-01-25','1349-07-25 20:18:15','2081-09-24 20:14:45','298:00:00',2005,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (10,10,19,109,1009,10009,2.0,3.1,109.55,'char10 ','varchar10','tiny10','text10','medium10','long10','b','y,z,w',0x62696e3130000000,0x00,0x00,0x00,0x00,0x00000000000000007661726269,'14104-09-16','1349-07-25 20:18:15','2081-09-25 14:27:01','330:00:00',2005,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (11,11,20,110,1010,10010,2.0999999046325684,3.2,110.66,'c11 ','vc11','t11','txt11','m11','l11','c','x,y,z,w',0x62313100,0x00,0x00,0x00,0x00,0x000000000000000000000000766231,'6280-09-02','4038-04-07 29:08:43','2051-09-20 17:24:00','-2041:24:02',1975,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (12,12,21,111,1011,10011,2.200000047683716,3.3,111.66,'c12 ','vc12','t12','txt12','m12','l12','d','x',0x62313200,0x00,0x00,0x00,0x00,0x000000000000000000000000766231,'6408-09-02','4038-04-07 29:08:44','2051-09-20 17:32:48','-2041:24:02',1975,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (13,13,22,112,1012,10012,2.299999952316284,3.4,112.66,'c13 ','vc13','t13','txt13','m13','l13','a','y',0x62313300,0x00,0x00,0x00,0x00,0x000000000000000000000000766231,'6536-09-02','4038-04-07 29:08:45','2051-09-20 17:41:36','-2041:24:02',1975,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (14,14,23,113,1013,10013,2.4000000953674316,3.5,113.66,'c14 ','vc14','t14','txt14','m14','l14','b','z',0x62313400,0x00,0x00,0x00,0x00,0x000000000000000000000000766231,'6664-09-02','4038-04-07 29:08:46','2051-09-20 17:50:24','-2041:24:02',1975,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (15,15,24,114,1014,10014,2.5,3.6,114.66,'c15 ','vc15','t15','txt15','m15','l15','c','w',0x62313500,0x00,0x00,0x00,0x00,0x000000000000000000000000766231,'6792-09-02','4038-04-07 29:08:47','2051-09-20 17:59:12','-2041:24:02',1975,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (16,16,25,115,1015,10015,2.5999999046325684,3.7,115.66,'c16 ','vc16','t16','txt16','m16','l16','d','x,y',0x62313600,0x00,0x00,0x00,0x00,0x000000000000000000000000766231,'6920-09-02','4038-04-07 29:08:48','2051-09-20 18:08:00','-2041:24:02',1975,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (17,17,26,116,1016,10016,2.700000047683716,3.8,116.66,'c17 ','vc17','t17','txt17','m17','l17','a','x,z',0x62313700,0x00,0x00,0x00,0x00,0x000000000000000000000000766231,'7048-09-02','4038-04-07 29:08:49','2051-09-20 18:16:48','-2041:24:02',1975,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (18,18,27,117,1017,10017,2.799999952316284,3.9,117.66,'c18 ','vc18','t18','txt18','m18','l18','b','y,z',0x62313800,0x00,0x00,0x00,0x00,0x000000000000000000000000766231,'7176-09-02','4038-04-07 29:08:50','2051-09-20 18:25:36','-2041:24:02',1975,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (19,19,28,118,1018,10018,2.9000000953674316,4.0,118.66,'c19 ','vc19','t19','txt19','m19','l19','c','y,w',0x62313900,0x00,0x00,0x00,0x00,0x000000000000000000000000766231,'7304-09-02','4038-04-07 29:08:51','2051-09-20 18:34:24','-2041:24:02',1975,'null');
INSERT INTO `..`.`all_mysql_types` VALUES (20,20,29,119,1019,10019,3.0,4.1,119.66,'c20 ','vc20','t20','txt20','m20','l20','d','z,w',0x62323000,0x00,0x00,0x00,0x00,0x000000000000000000000000766232,'6152-09-02','4038-04-07 29:08:52','2051-09-20 18:43:12','-2041:24:02',1975,'null');

原始写入数据，供大神测试 ：

`INSERT INTO `all_mysql_types` VALUES (1,1,10,100,1000,10000,1.1,2.2,100.55,'char1','varchar1','tiny1','text1','medium1','long1','a','x,y',_binary 'bin1\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'varbin1',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-01','2025-01-01 10:00:00','2026-05-08 02:32:44','10:00:00',2025,'{\"a\": 1}'),(2,2,11,101,1001,10001,1.2,2.3,101.55,'char2','varchar2','tiny2','text2','medium2','long2','b','x,z',_binary 'bin2\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'varbin2',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-02','2025-01-02 10:00:00','2026-05-08 02:32:44','11:00:00',2025,'{\"a\": 2}'),(3,3,12,102,1002,10002,1.3,2.4,102.55,'char3','varchar3','tiny3','text3','medium3','long3','c','y,z',_binary 'bin3\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'varbin3',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-03','2025-01-03 10:00:00','2026-05-08 02:32:44','12:00:00',2025,'{\"a\": 3}'),(4,4,13,103,1003,10003,1.4,2.5,103.55,'char4','varchar4','tiny4','text4','medium4','long4','d','x,w',_binary 'bin4\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'varbin4',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-04','2025-01-04 10:00:00','2026-05-08 02:32:44','13:00:00',2025,'{\"a\": 4}'),(5,5,14,104,1004,10004,1.5,2.6,104.55,'char5','varchar5','tiny5','text5','medium5','long5','a','y,w',_binary 'bin5\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'varbin5',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-05','2025-01-05 10:00:00','2026-05-08 02:32:44','14:00:00',2025,'{\"a\": 5}'),(6,6,15,105,1005,10005,1.6,2.7,105.55,'char6','varchar6','tiny6','text6','medium6','long6','b','z,w',_binary 'bin6\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'varbin6',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-06','2025-01-06 10:00:00','2026-05-08 02:32:44','15:00:00',2025,'{\"a\": 6}'),(7,7,16,106,1006,10006,1.7,2.8,106.55,'char7','varchar7','tiny7','text7','medium7','long7','c','x,y,z',_binary 'bin7\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'varbin7',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-07','2025-01-07 10:00:00','2026-05-08 02:32:44','16:00:00',2025,'{\"a\": 7}'),(8,8,17,107,1007,10007,1.8,2.9,107.55,'char8','varchar8','tiny8','text8','medium8','long8','d','x,y,w',_binary 'bin8\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'varbin8',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-08','2025-01-08 10:00:00','2026-05-08 02:32:44','17:00:00',2025,'{\"a\": 8}'),(9,9,18,108,1008,10008,1.9,3,108.55,'char9','varchar9','tiny9','text9','medium9','long9','a','x,z,w',_binary 'bin9\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'varbin9',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-09','2025-01-09 10:00:00','2026-05-08 02:32:44','18:00:00',2025,'{\"a\": 9}'),(10,10,19,109,1009,10009,2,3.1,109.55,'char10','varchar10','tiny10','text10','medium10','long10','b','y,z,w',_binary 'bin10\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'varbin10',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-10','2025-01-10 10:00:00','2026-05-08 02:32:44','19:00:00',2025,'{\"a\": 10}'),(11,11,20,110,1010,10010,2.1,3.2,110.66,'c11','vc11','t11','txt11','m11','l11','c','x,y,z,w',_binary 'b11\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'vb11',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-11','2025-01-11 11:00:00','2026-05-08 02:32:44','08:00:00',2025,'{\"key\": 11}'),(12,12,21,111,1011,10011,2.2,3.3,111.66,'c12','vc12','t12','txt12','m12','l12','d','x',_binary 'b12\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'vb12',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-12','2025-01-12 12:00:00','2026-05-08 02:32:44','09:00:00',2025,'{\"key\": 12}'),(13,13,22,112,1012,10012,2.3,3.4,112.66,'c13','vc13','t13','txt13','m13','l13','a','y',_binary 'b13\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'vb13',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-13','2025-01-13 13:00:00','2026-05-08 02:32:44','10:00:00',2025,'{\"key\": 13}'),(14,14,23,113,1013,10013,2.4,3.5,113.66,'c14','vc14','t14','txt14','m14','l14','b','z',_binary 'b14\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'vb14',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-14','2025-01-14 14:00:00','2026-05-08 02:32:44','11:00:00',2025,'{\"key\": 14}'),(15,15,24,114,1014,10014,2.5,3.6,114.66,'c15','vc15','t15','txt15','m15','l15','c','w',_binary 'b15\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'vb15',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-15','2025-01-15 15:00:00','2026-05-08 02:32:44','12:00:00',2025,'{\"key\": 15}'),(16,16,25,115,1015,10015,2.6,3.7,115.66,'c16','vc16','t16','txt16','m16','l16','d','x,y',_binary 'b16\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'vb16',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-16','2025-01-16 16:00:00','2026-05-08 02:32:44','13:00:00',2025,'{\"key\": 16}'),(17,17,26,116,1016,10016,2.7,3.8,116.66,'c17','vc17','t17','txt17','m17','l17','a','x,z',_binary 'b17\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'vb17',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-17','2025-01-17 17:00:00','2026-05-08 02:32:44','14:00:00',2025,'{\"key\": 17}'),(18,18,27,117,1017,10017,2.8,3.9,117.66,'c18','vc18','t18','txt18','m18','l18','b','y,z',_binary 'b18\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'vb18',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-18','2025-01-18 18:00:00','2026-05-08 02:32:44','15:00:00',2025,'{\"key\": 18}'),(19,19,28,118,1018,10018,2.9,4,118.66,'c19','vc19','t19','txt19','m19','l19','c','y,w',_binary 'b19\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'vb19',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-19','2025-01-19 19:00:00','2026-05-08 02:32:44','16:00:00',2025,'{\"key\": 19}'),(20,20,29,119,1019,10019,3,4.1,119.66,'c20','vc20','t20','txt20','m20','l20','d','z,w',_binary 'b20\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',_binary 'vb20',_binary '',_binary '\"',_binary '3',_binary 'D','2025-01-20','2025-01-20 20:00:00','2026-05-08 02:32:44','17:00:00',2025,'{\"key\": 20}');`

[ddcw]

测试发现5.7报错, 8.0正常, 使用8.0的表结构解析5.7的也正常, 说明是5.7解析表结构时,有隐藏属性不对, 我尽快修复.

[ddcw]

已修复, 可下载最新版验证: https://github.com/ddcw/ibd2sql/archive/refs/heads/ibd2sql-v2.x.zip
原因: mysql的binary类型对应innodb类型为29

[chenpcpolive-prog]

大部分字段目前没问题，肉眼能看到的问题是2.5999999046325684（精度有问题）--但是导入数据库中数据对的上，对不上的是binary 字段，使用生成的insert的时候通过对比发现不一致

INSERT INTO `..`.`all_mysql_types` VALUES (16,16,25,115,1015,10015,2.5999999046325684,3.7,115.66,'c16                 ','vc16','t16','txt16','m16','l16','d','x,y',0x6231360000000000000000000000000000000000,0x76623136,0x11,0x22,0x33,0x44,'2025-01-16','2025-01-16 16:00:00','2026-05-08 10:32:44','13:00:00',2025,'{"key": 16}');


SELECT
  a.id AS 源库ID,
  b.id AS 目标库ID,
  -- 逐列对比，不一致会显示 NOT MATCH
  IF(a.tinyint_col <=> b.tinyint_col, 'MATCH', 'NOT MATCH') AS tinyint_col对比,
  IF(a.smallint_col <=> b.smallint_col, 'MATCH', 'NOT MATCH') AS smallint_col对比,
  IF(a.mediumint_col <=> b.mediumint_col, 'MATCH', 'NOT MATCH') AS mediumint_col对比,
  IF(a.int_col <=> b.int_col, 'MATCH', 'NOT MATCH') AS int_col对比,
  IF(a.bigint_col <=> b.bigint_col, 'MATCH', 'NOT MATCH') AS bigint_col对比,
  IF(a.float_col <=> b.float_col, 'MATCH', 'NOT MATCH') AS float_col对比,
  IF(a.double_col <=> b.double_col, 'MATCH', 'NOT MATCH') AS double_col对比,
  IF(a.decimal_col <=> b.decimal_col, 'MATCH', 'NOT MATCH') AS decimal_col对比,
  IF(a.char_col <=> b.char_col, 'MATCH', 'NOT MATCH') AS char_col对比,
  IF(a.varchar_col <=> b.varchar_col, 'MATCH', 'NOT MATCH') AS varchar_col对比,
  IF(a.tinytext_col <=> b.tinytext_col, 'MATCH', 'NOT MATCH') AS tinytext_col对比,
  IF(a.text_col <=> b.text_col, 'MATCH', 'NOT MATCH') AS text_col对比,
  IF(a.mediumtext_col <=> b.mediumtext_col, 'MATCH', 'NOT MATCH') AS mediumtext_col对比,
  IF(a.longtext_col <=> b.longtext_col, 'MATCH', 'NOT MATCH') AS longtext_col对比,
  IF(a.enum_col <=> b.enum_col, 'MATCH', 'NOT MATCH') AS enum_col对比,
  IF(a.set_col <=> b.set_col, 'MATCH', 'NOT MATCH') AS set_col对比,
  IF(a.binary_col <=> b.binary_col, 'MATCH', 'NOT MATCH') AS binary_col对比,
  IF(a.varbinary_col <=> b.varbinary_col, 'MATCH', 'NOT MATCH') AS varbinary_col对比,
  IF(a.tinyblob_col <=> b.tinyblob_col, 'MATCH', 'NOT MATCH') AS tinyblob_col对比,
  IF(a.blob_col <=> b.blob_col, 'MATCH', 'NOT MATCH') AS blob_col对比,
  IF(a.mediumblob_col <=> b.mediumblob_col, 'MATCH', 'NOT MATCH') AS mediumblob_col对比,
  IF(a.longblob_col <=> b.longblob_col, 'MATCH', 'NOT MATCH') AS longblob_col对比,
  IF(a.date_col <=> b.date_col, 'MATCH', 'NOT MATCH') AS date_col对比,
  IF(a.datetime_col <=> b.datetime_col, 'MATCH', 'NOT MATCH') AS datetime_col对比,
  IF(a.timestamp_col <=> b.timestamp_col, 'MATCH', 'NOT MATCH') AS timestamp_col对比,
  IF(a.time_col <=> b.time_col, 'MATCH', 'NOT MATCH') AS time_col对比,
  IF(a.year_col <=> b.year_col, 'MATCH', 'NOT MATCH') AS year_col对比,
  IF(a.json_col <=> b.json_col, 'MATCH', 'NOT MATCH') AS json_col对比
FROM
  cp.all_mysql_types a
LEFT JOIN
  【目标库名】.all_mysql_types b ON a.id = b.id
-- 只查不一致的数据（非常适合DataX校验）
WHERE
  NOT (
    a.tinyint_col <=> b.tinyint_col
    AND a.smallint_col <=> b.smallint_col
    AND a.mediumint_col <=> b.mediumint_col
    AND a.int_col <=> b.int_col
    AND a.bigint_col <=> b.bigint_col
    AND a.float_col <=> b.float_col
    AND a.double_col <=> b.double_col
    AND a.decimal_col <=> b.decimal_col
    AND a.char_col <=> b.char_col
    AND a.varchar_col <=> b.varchar_col
    AND a.tinytext_col <=> b.tinytext_col
    AND a.text_col <=> b.text_col
    AND a.mediumtext_col <=> b.mediumtext_col
    AND a.longtext_col <=> b.longtext_col
    AND a.enum_col <=> b.enum_col
    AND a.set_col <=> b.set_col
    AND a.binary_col <=> b.binary_col
    AND a.varbinary_col <=> b.varbinary_col
    AND a.tinyblob_col <=> b.tinyblob_col
    AND a.blob_col <=> b.blob_col
    AND a.mediumblob_col <=> b.mediumblob_col
    AND a.longblob_col <=> b.longblob_col
    AND a.date_col <=> b.date_col
    AND a.datetime_col <=> b.datetime_col
    AND a.timestamp_col <=> b.timestamp_col
    AND a.time_col <=> b.time_col
    AND a.year_col <=> b.year_col
    AND a.json_col <=> b.json_col
  )
ORDER BY a.id;

[ddcw]

1. float存储的就不是实际值, 而是近似值. 而解析的是存储的值, 所以解析出来的就是近视值. 官方也更推荐使用decimal来代替flaot/double
2. binary那个应该是表结构不一样导致的. 之前修复binary bug的时候,忘记同步修改元数据信息了, 已更新
3. 对于binary为空(非null)时, 使用默认空字符串表示(0x是mysql显示的值,无法作为insert插入数据库.... 坑我一手.)

可下载最新版验证: (5.7的checskum table无法校验json字段) https://github.com/ddcw/ibd2sql/archive/refs/heads/ibd2sql-v2.x.zip