需求：线上部分表数据需要同步到测试环境，但是测试环境表结构又有变更，额外添加需求：原线上和测试都有的表字段同步，其他不同的字段不用同步，置为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_history2 test data_number tenancy_db data_number3 test house tenancy_db house4 test house_process tenancy_db house_process5 test landlord tenancy_db landlord6 test landlord_process tenancy_db landlord_process7 test order_info tenancy_db decorate_order_info8 test order_process tenancy_db decorate_order_process9 test payment_record_stream tenancy_db decorate_payment_record10 test repayment_plan tenancy_db decorate_repayment_plan11 test shop_area tenancy_db shop_area
使用如下SQL拼接出要执行的SQLSELECT CONCAT('insert into `',b.to_tab,'`(',GROUP_CONCAT(CONCAT('`',a.column_name,'`')),') select ',GROUP_CONCAT(CONCAT('`',a.column_name,'`')),' from ',a.from_tab,';')FROM(SELECTts.id,ts.from_tab,cl.column_nameFROMinformation_schema.`COLUMNS` clLEFT JOIN test.`z_tab_sync` tsON cl.table_name = ts.from_tabWHERE table_schema = 'test'AND ts.id IS NOT NULL ) a,(SELECTts.id,ts.to_tab,cl.column_nameFROMinformation_schema.`COLUMNS` clLEFT JOIN test.`z_tab_sync` tsON cl.table_name = ts.to_tabWHERE table_schema = 'tenancy_db'AND ts.id IS NOT NULL ) bWHERE a.id = b.id AND a.column_name = b.column_nameGROUP BY a.id;
得到的SQL形如INSERT INTO `business_history` (`settlementId`,`businessType`,`updateTime`,`status`,`createTime`,`id`)SELECT`settlementId`,`businessType`,`updateTime`,`status`,`createTime`,`id`FROMbusiness_history ;