Shulou(Shulou.com)06/01 Report--

Requirements: some of the table data online needs to be synchronized to the test environment, but the table structure of the test environment has changed. Additional requirements are added: the table fields of both the original line and the test are synchronized, and other different fields do not need to be synchronized and can be set to NULL. Idea: first, export the online table data to the test test library. Considering the changes in the table structure on both sides, you can only use insert tab1 (xx,xx) select xx,xx from tab1 to insert. There are more tables, and it takes a lot of work to compare all fields manually. Prepare to splice the inserted SQL by SQL splicing to execute the implementation process: 1. Import the database to be synchronized at the target side into the test on the test side. 2. Create a synchronization information table. And sort out the corresponding insert data: CREATE TABLE `zroomtabsync` (`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

Where from_tab is the table on the target side To_tab is the table 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 on the test side 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

Use the following SQL to splice out the SQL SELECT CONCAT to be executed ('insert into `, b. Toggle tab'` (', GROUP_CONCAT (CONCAT ('`, a. Columnaccountname)'`),') select', GROUP_CONCAT (CONCAT ('', a. ColumnroomnameName'`'), 'from', a.fromroomnameta') ') 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

The resulting SELECT is shaped like INSERT INTO `settlementId` (`settlementId`, `businessType`, `updateTime`, `status`, `createTime`, `id`) SELECT `settlementId`, `businessType`, `updateTime`, `status`, `createTime`, `id`

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.