MySQL 拼接Insert批量同步異構(gòu)表數(shù)據(jù)
需求：線(xiàn)上部分表數(shù)據(jù)需要同步到測(cè)試環(huán)境，但是測(cè)試環(huán)境表結(jié)構(gòu)又有變更，額外添加需求：原線(xiàn)上和測(cè)試都有的表字段同步，其他不同的字段不用同步，置為NULL即可。
思路：首先導(dǎo)出線(xiàn)上表數(shù)據(jù)到測(cè)試的test庫(kù)，考慮到兩邊表結(jié)構(gòu)有變更，只能使用insert tab1(xx,xx) select xx,xx from tab1的方式插入，表比較多，手動(dòng)對(duì)比所有字段工作量比較大，準(zhǔn)備采用SQL拼接的方式拼接出插入的SQL去執(zhí)行
實(shí)現(xiàn)過(guò)程：
1、將目標(biāo)端要同步的數(shù)據(jù)庫(kù)導(dǎo)入到測(cè)試端的test下面
2、創(chuàng)建同步信息表，并整理對(duì)應(yīng)關(guān)系插入數(shù)據(jù)：
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是目標(biāo)端的表，to_tab是測(cè)試端的表
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拼接出要執(zhí)行的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 ;
