binlog2sql的对MySQL列的兼容性测试 _兼容性

胸怀万里世界，放眼无限未来。这篇文章主要讲述binlog2sql的对MySQL列的兼容性测试相关的知识，希望能为你提供帮助。
结论：
binlog2sql对 int类型、char/varchar类型、text 类型支持完善。
binlog2sql对bit类型支持不太好，后期需要修数据。
【binlog2sql的对MySQL列的兼容性测试】binlog2sql对json类型支持的不完善，导出的数据基本上无法使用。

样例：

binlog2sql对 int类型、char/varchar类型、text 类型支持完善，如下案例：

CREATE TABLE `sbt_t1` (
`id` int NOT NULL AUTO_INCREMENT,
`b` char(2) not null default 01,
`c` varchar(255) not null default OnlyForTest,
`d` text comment 正文,
`e` tinyint not null default 0,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

insert into sbt_t1(b,c,d,e) values (01,OnlyForTest,仅供测试,23);
insert into sbt_t1(b,c,d,e) values (02,仅供测试,OnlyForTest,125);
insert into sbt_t1(b,c,d,e) values (00,仅供测试,OnlyForTest,34);

[sbtest] > select * from sbt_t1;
+----+----+--------------+--------------+-----+
| id | b| c| d| e|
+----+----+--------------+--------------+-----+
|1 | 01 | OnlyForTest| 仅供测试|23 |
|2 | 02 | 仅供测试| OnlyForTest| 125 |
|3 | 00 | 仅供测试| OnlyForTest|34 |
+----+----+--------------+--------------+-----+
3 rows in set (0.01 sec)

[sbtest] > update sbt_t1 set e=123 where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1Changed: 1Warnings: 0

[sbtest] > update sbt_t1 set d=aaaaaaaaaaaa where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1Changed: 1Warnings: 0

[sbtest] > delete from sbt_t1 where id=1;
Query OK, 1 row affected (0.00 sec)

[sbtest] > select * from sbt_t1;
+----+----+--------------+--------------+-----+
| id | b| c| d| e|
+----+----+--------------+--------------+-----+
|2 | 02 | 仅供测试| aaaaaaaaaaaa | 125 |
|3 | 00 | 仅供测试| OnlyForTest| 123 |
+----+----+--------------+--------------+-----+
2 rows in set (0.00 sec)

[root@mxali0027 mysqlBak]# python3.6binlog2sql-master/binlog2sql/binlog2sql.py --flashback -P3306-usysbench -psysbench -h 172.19.135.197 -dsbtest --start-file=mysql-bin.000008
INSERT INTO `sbtest`.`sbt_t1`(`id`, `b`, `c`, `d`, `e`) VALUES (1, 01, OnlyForTest, 仅供测试, 23);
UPDATE `sbtest`.`sbt_t1` SET `id`=2, `b`=02, `c`=仅供测试, `d`=OnlyForTest, `e`=125 WHERE `id`=2 AND `b`=02 AND `c`=仅供测试 AND `d`=aaaaaaaaaaaa AND `e`=125 LIMIT 1;
UPDATE `sbtest`.`sbt_t1` SET `id`=3, `b`=00, `c`=仅供测试, `d`=OnlyForTest, `e`=34 WHERE `id`=3 AND `b`=00 AND `c`=仅供测试 AND `d`=OnlyForTest AND `e`=123 LIMIT 1;

binlog2sql对decimal和datetime类型的支持：

CREATE TABLE `sbt_t4` (
`id` int NOT NULL AUTO_INCREMENT,
`b` decimal(12,2) not null default 0.00,
`c` datetime(6) not null default 1970-01-01 00:00:00,
`d` datetime(6) default null,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

insert into sbt_t4(b,c,d) values (12.21,now(),2021-12-27 00:00:00);
insert into sbt_t4(b,c,d) values (23.21,2021-02-27 11:00:00,2021-01-27 00:00:00);
insert into sbt_t4(b,c,d) values (45.00,2021-02-27 00:00:00.123,2021-01-27 00:00:00.345);
insert into sbt_t4(b,c,d) values (45.00,2021-02-27 00:00:00.12345,2021-01-27 00:00:00.34567);

[sbtest] > select * from sbt_t4;
+----+-------+----------------------------+----------------------------+
| id | b| c| d|
+----+-------+----------------------------+----------------------------+
|1 | 12.21 | 2021-12-27 17:21:29.000000 | 2021-12-27 00:00:00.000000 |
|2 | 23.21 | 2021-02-27 11:00:00.000000 | 2021-01-27 00:00:00.000000 |
|3 | 45.00 | 2021-02-27 00:00:00.123000 | 2021-01-27 00:00:00.345000 |
|4 | 45.00 | 2021-02-27 00:00:00.123450 | 2021-01-27 00:00:00.345670 |
+----+-------+----------------------------+----------------------------+
4 rows in set (0.00 sec)

delete from sbt_t4 where id=4;
update sbt_t4 set b=22.12 where id=2;

回滚SQL：
UPDATE `sbtest`.`sbt_t4` SET `id`=2, `b`=23.21, `c`=2021-02-27 11:00:00, `d`=2021-01-27 00:00:00 WHERE `id`=2 AND `b`=22.12 AND `c`=2021-02-27 11:00:00 AND `d`=2021-01-27 00:00:00 LIMIT 1;

INSERT INTO `sbtest`.`sbt_t4`(`id`, `b`, `c`, `d`) VALUES (4, 45.00, 2021-02-27 00:00:00.123450, 2021-01-27 00:00:00.345670);

经测试，都是可以正常执行的。

binlog2sql对bit类型支持不太好：

sbtest> CREATE TABLE `bit_t1` (
`id` int NOT NULL AUTO_INCREMENT,
`b` bit(1) not null default b1,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

insert into bit_t1(b) values (b1);
insert into bit_t1(b) values (b0);
insert into bit_t1(b) values (b0);
insert into bit_t1(b) values (b1);

(sbtest) > select * from bit_t1;
+----+------+
| id | b|
+----+------+
|4 | 0x01 |
| 11 | 0x00 |
| 18 | 0x00 |
| 25 | 0x01 |
+----+------+
4 rows in set (0.00 sec)

(sbtest) > delete from bit_t1 where id=18;
Query OK, 1 row affected (0.01 sec)

(sbtest) > update bit_t1 set b=b1 where id=11;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1Changed: 1Warnings: 0

(sbtest) > select * from bit_t1;
+----+------+
| id | b|
+----+------+
|4 | 0x01 |
| 11 | 0x01 |
| 25 | 0x01 |
+----+------+
3 rows in set (0.00 sec)

$ python binlog2sql.py --flashback -h192.168.3.14 -P3306 -usysbench -psysbench -d sbtest --start-file=binlog.000015
UPDATE `sbtest`.`bit_t1` SET `id`=11, `b`=b0 WHERE `id`=11 AND `b`=b1 LIMIT 1;
INSERT INTO `sbtest`.`bit_t1`(`id`, `b`) VALUES (18, 0);

恢复测试：
(sbtest) > UPDATE `sbtest`.`bit_t1` SET `id`=11, `b`=0 WHERE `id`=11 AND `b`=1 LIMIT 1;
ERROR 1406 (22001): Data too long for column b at row 1

修改下恢复出的sql文件，将全部的bit类型的结果值前面都加上b标识符。如下：
(sbtest) > UPDATE `sbtest`.`bit_t1` SET `id`=11, `b`=b0 WHERE `id`=11 AND `b`=b1 LIMIT 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1Changed: 1Warnings: 0

binlog2sql对json类型支持的不完善，如下案例：

sbtest> show create table json_t1 \\G
***************************[ 1. row ]***************************
Table| json_t1
Create Table | CREATE TABLE `json_t1` (
`id` int NOT NULL AUTO_INCREMENT,
`b` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

sbtest> select * from json_t1 ;
+----+------------------------------+
| id | b|
+----+------------------------------+
| 4| "age": "18", "name": "sun" |
| 11 | "age": "18", "name": "sun" |
| 18 | "age": "18", "name": "sun" |
+----+------------------------------+
3 rows in set

sbtest> update json_t1 set b= where id=11;
Query OK, 1 row affected

sbtest> select * from json_t1 ;
+----+------------------------------+
| id | b|
+----+------------------------------+
| 4| "age": "18", "name": "sun" |
| 11 ||
| 18 | "age": "18", "name": "sun" |
+----+------------------------------+
3 rows in set

$ python binlog2sql.py --flashback -h192.168.3.14 -P3306 -usysbench -psysbench -d sbtest --start-file=binlog.000015
UPDATE `sbtest`.`json_t1` SET `id`=11, `b`=bage: "18", bname: "sun" WHERE `id`=11 AND `b`= LIMIT 1; #start 1451 end 1736 time 2021-12-27 14:06:07

解析出的这个sql，实际上是有问题的，修改正确的如下：
UPDATE `sbtest`.`json_t1` SET `id`=11, `b`="age": "18", "name": "sun" WHERE `id`=11 AND json_length(`b`)=0 LIMIT 1;

sbtest> select * from json_t1 ;
+----+------------------------------+
| id | b|
+----+------------------------------+
| 4| "age": "18", "name": "sun" |
| 11 | "age": "18", "name": "sun" |
| 18 | "age": "18", "name": "sun" |
+----+------------------------------+
3 rows in set

binlog2sql的对MySQL列的兼容性测试

推荐阅读

野罂粟和罂粟的区别野罂粟

小学生营养早餐，小学生营养早餐食谱大全及做法

高三陪读2021（十七）

孕妇下肢浮肿是什么原因？

小米盒子增强版

武则天和李世民是什么关系武则天和李世民的关系

教授王君盛恐其不章也章的意思

踩坑记|踩坑记在STM32上移植emWin和RTOS，进HardFault_Handler

苹果如何设置出圆，苹果手机屏幕上的圆圈怎么弄出来

spice 源代码分析,android系统源代码分析

自然灾害的手抄报a3 自然灾害的手抄报a3的画法

领养的猫一直叫是怎么办

（61）道歉就是

年度最佳游戏名单公开！年度最佳游戏

电商是什么什么是电商商机，商机好项目

春季嘴唇干裂吃啥好？嘴唇干裂多吃五类食物

海口到重庆机票长沙到海口低价飞机票价格

荠菜饺子的做法荠菜饺子的家常做法荠菜饺子的做法

三星电视遥控器故障怎么检查,这几招教给你

51单片机震荡电路分析,单片机时钟振荡电路