手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

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

[日期:2017-07-29] 来源:Linux社区  作者:静以致远√团团 [字体: ]

需求:线上部分表数据需要同步到测试环境,但是测试环境表结构又有变更,额外添加需求:原线上和测试都有的表字段同步,其他不同的字段不用同步,置为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 ;

本文永久更新链接地址http://www.linuxidc.com/Linux/2017-07/145977.htm

linux
相关资讯       MySQL insert 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款