近期,由于数据量的激增,准备将数据从Mysql迁移到分布式数据库TiDB上,期间遇到了一些兼容性问题,今天就其中的一两个问题进行记录。
inner join
,left join
,right join
时,由于两表外键字段类型不一致导致出现了full join
的效果。- TiDB在修改字段类型、字段长度时有很多限制,比如在
varchar
转为bigint
时无法直接修改,需要使用中间字段。
一、发现问题:
数据库迁移后,系统测试时莫名发现很多重复数据,于是进行Mysql与TiDB执行结果进行对比
SQL:
select tmr.material_id, tm.id
from task_material_relation tmr
left join task_material tm on tmr.material_id = tm.id
where tmr.task_id = 805742236025094144;
Mysql查询结果:
TiDB查询结果:
二、问题分析:
- 基于执行结果发现,明明SQL中加入了
tmr.material_id = tm.id
连接条件,但是结果中task_material_relation.material_id
和task_material.id
值并不相等。 - 查看建表语句发现
material_id varchar(32)
,id bigint(20)
两连接字段类型不一致。 - 假设是由于类型引起的结果差异,接下来就修改成一致类型进行验证。
三、验证假设:
修改task_material_relation.material_id
字段类型,由varchar
修改为bigint
(有人会问之前为什么用varchar存储bigint,之前设计表结构时考虑到material的来源不唯一,每个系统中id类型可能不一样,但是本系统中使用的雪花算法)
-- 1. 增加一个临时字段用于存放material_id的值
alter table task_material_relation add column `temp` bigint not null comment '临时字段';
-- 2. 将material_id的值迁移到临时字段temp上
update task_material_relation set `temp` = `material_id`;
-- 3. 将material_id相关的索引删除,否则删除字段时有问题
show indexes from task_material_relation;
drop index `course_chapter_lecture_section_userType_source_type` on task_material_relation;
-- 4. 删除、新类型重建字段material_id
alter table task_material_relation drop column `material_id`;
alter table task_material_relation add column `material_id` bigint not null;
-- 5. 将temp上的值迁移到新字段material_id上并重建索引
update task_material_relation set `material_id` = `temp`;
create unique index `course_chapter_lecture_section_userType_source_type` on task_material_relation(`task_id`, `material_id`, `use_type`, `material_source`, `material_type`);
-- 6. 进行数据验证、测试,没问题删除临时字段temp
alter table task_material_relation drop column `temp`;
四、初步结论:
在进行第6步验证时,再次执行问题SQL,发现TiDB返回结果与Mysql中一致了,由此验证问题是由于外键字段类型不一致而产生的。
至于bigint
和smallint
、double
和decimal
,datetime
和timestamp
,varchar(32)
和varchar(64)
会不会产生问题,有待验证,期待大家把验证结果发在评论区。