导航：首页 > 数据库 >

MySQL 拼接Insert批量同步异构表数据

发表于：2024-11-26 作者：千家信息网编辑

千家信息网最后更新 2024年11月26日，需求：线上部分表数据需要同步到测试环境，但是测试环境表结构又有变更，额外添加需求：原线上和测试都有的表字段同步，其他不同的字段不用同步，置为NULL即可。思路：首先导出线上表数据到测试的test库，

千家信息网最后更新 2024年11月26日MySQL 拼接Insert批量同步异构表数据需求：线上部分表数据需要同步到测试环境，但是测试环境表结构又有变更，额外添加需求：原线上和测试都有的表字段同步，其他不同的字段不用同步，置为NULL即可。思路：首先导出线上表数据到测试的test库，考虑到两边表结构有变更，只能使用insert tab1(xx,xx) select xx,xx from tab1的方式插入，表比较多，手动对比所有字段工作量比较大，准备采用SQL拼接的方式拼接出插入的SQL去执行 实现过程： 1、将目标端要同步的数据库导入到测试端的test下面 2、创建同步信息表，并整理对应关系插入数据： CREATE TABLE `z_tab_sync` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `from_db` VARCHAR(100) DEFAULT NULL, `from_tab` VARCHAR(100) DEFAULT NULL, `to_db` VARCHAR(100) DEFAULT NULL, `to_tab` VARCHAR(100) DEFAULT NULL, KEY `id` (`id`) ) ENGINE=INNODB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
其中from_tab是目标端的表，to_tab是测试端的表 id from_db from_tab to_db to_tab ------ ------- --------------------- ---------- ------------------------- 1 test business_history tenancy_db business_history 2 test data_number tenancy_db data_number 3 test house tenancy_db house 4 test house_process tenancy_db house_process 5 test landlord tenancy_db landlord 6 test landlord_process tenancy_db landlord_process 7 test order_info tenancy_db decorate_order_info 8 test order_process tenancy_db decorate_order_process 9 test payment_record_stream tenancy_db decorate_payment_record 10 test repayment_plan tenancy_db decorate_repayment_plan 11 test shop_area tenancy_db shop_area
使用如下SQL拼接出要执行的SQL SELECT CONCAT('insert into `',b.to_tab,'`(',GROUP_CONCAT(CONCAT('`',a.column_name,'`')),') select ',GROUP_CONCAT(CONCAT('`',a.column_name,'`')),' from ',a.from_tab,';') FROM ( SELECT ts.id, ts.from_tab, cl.column_name FROM information_schema.`COLUMNS` cl LEFT JOIN test.`z_tab_sync` ts ON cl.table_name = ts.from_tab WHERE table_schema = 'test' AND ts.id IS NOT NULL ) a, ( SELECT ts.id, ts.to_tab, cl.column_name FROM information_schema.`COLUMNS` cl LEFT JOIN test.`z_tab_sync` ts ON cl.table_name = ts.to_tab WHERE table_schema = 'tenancy_db' AND ts.id IS NOT NULL ) b WHERE a.id = b.id AND a.column_name = b.column_name GROUP BY a.id;
得到的SQL形如 INSERT INTO `business_history` ( `settlementId`, `businessType`, `updateTime`, `status`, `createTime`, `id` ) SELECT `settlementId`, `businessType`, `updateTime`, `status`, `createTime`, `id` FROM business_history ;

很赞哦！