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 ;

分享标题：MySQL拼接Insert批量同步异构表数据
网站链接：http://lszwz.com/article/jdcshj.html

其他资讯

售后响应及时

7×24小时客服热线

数据备份

更安全、更高效、更稳定

价格公道精准

项目经理精准报价不弄虚作假

合作无风险

重合同讲信誉，无效全额退款

乐山小谭建站工作室是一家专注从事于高品质视觉体验及互联网设计开发，乐山网站建设，乐山网站设计，乐山网页设计，乐山网站制作，品牌网站建设，营销网站建设，集团网站建设，企业网站建设，外贸网站建设，响应式网站建设，小程序开发，微信开发，企业形象设计，企业宣传视频等服务，小谭建站位于乐山市龙岗区大运软件小镇，小谭建站拥有经验丰富的高级网站建设工程师和一流的网页高端设计人员，具备各种规模与类型网站建设的雄厚实力，在网站建设领域树立了自己独特的设计风格。

友情链接交换友情链接

成都圣月窗帘西部信息服务器托管虚拟主机购买网络整合营销航空文化传媒成都做手机网站分类目录网站成都网站推广成都广告招牌制作响应式网站建设方案

Copyright © 2022 青羊区小谭信息技术咨询服务工作室乐山建站工作室 All Rights Reserved 蜀ICP备2021004003号-25

Copyright © 2022 青羊区小谭信息技术咨询服务工作室乐山建站工作室

蜀ICP备2021004003号-25