mysql生成表文件、truncate、Load
SELECT -- ' select * from `',TABLE_SCHEMA,'`.`',TABLE_NAME,'` into outfile ''/work/opdir/repair'''
-- concat('union all
-- select count(1) as count_, ''`',TABLE_SCHEMA,'`.`',TABLE_NAME, '`'' as tbname from `',TABLE_SCHEMA,'`.`',TABLE_NAME ,'`')
-- as sql_
CONCAT( ' select * from `',TABLE_SCHEMA,'`.`',TABLE_NAME,'` into outfile ''/work/opdir/repair/',TABLE_SCHEMA,'.',TABLE_NAME,'.tsv'' ;
truncate table ' ,TABLE_SCHEMA,'.',TABLE_NAME,' ;
LOAD DATA infile ''/work/opdir/repair/',TABLE_SCHEMA,'.',TABLE_NAME,'.tsv'' INTO TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CHARACTER SET utf8;
')
ExeSql
FROM information_schema.TABLES t
WHERE t.TABLE_TYPE LIKE '%T%'
AND t.TABLE_SCHEMA IN
(
'BCReport_Sync'-- ,'AdConversions','AdSync','BCSourceData_Sync'
)
AND t.`TABLE_NAME` NOT LIKE '%Log%'
AND t.`TABLE_NAME` NOT LIKE '%bak%'
AND t.`TABLE_NAME` NOT LIKE '%History%'
AND t.`TABLE_NAME` NOT LIKE '%Tmp_%'
AND t.`TABLE_NAME` NOT LIKE '%bak%'
AND t.`TABLE_NAME` NOT LIKE '%Monitor%'
AND t.`TABLE_NAME` NOT LIKE '%bak%'
AND t.`TABLE_NAME` NOT LIKE '%backup%'
AND t.`TABLE_NAME` NOT LIKE '%_BK'
AND t.`TABLE_NAME` NOT LIKE '%_Temp'
AND t.`TABLE_NAME` NOT LIKE '%2013%'
AND t.`TABLE_NAME` NOT LIKE '%2'
AND t.`TABLE_NAME` NOT LIKE '%2012%'
AND t.`TABLE_NAME` NOT LIKE '%2012%'
AND t.`TABLE_NAME` NOT LIKE '%2014%'
AND CONCAT(t.TABLE_SCHEMA ,'.', t.`TABLE_NAME`) IN
('AdConversions.DataInbound_TaskSchedule' ,
'AdConversions.DeviceForComparison' ,
'AdConversions.Fact_Realtime_Conversion' ,
'AdDataCenter.Ad_Account_Mirror' ,
'AdDataCenter.Ad_Account' ,
'AdDataCenter.Ad_AdGroup_AdAction_Mobisage' ,
'AdDataCenter.Ad_AdGroup_BidPrice' ,
'AdDataCenter.Ad_AdGroup_Property' ,
'AdDataCenter.Ad_AdGroup_WeekTimePeriod' ,
'AdDataCenter.Ad_AdGroup' ,
'AdDataCenter.Ad_Campaign_BudgetList' ,
'AdDataCenter.Ad_Campaign_DockingProduct_Mapping' ,
'AdDataCenter.Ad_Campaign_GeoTargeting' ,
'AdDataCenter.Ad_Campaign' ,
'AdDataCenter.Ad_Creative_Additional' ,
'AdDataCenter.Ad_Creative_ImageAd' ,
'AdDataCenter.Ad_Creative_Offerwall' ,
'AdDataCenter.Ad_Creative_Template' ,
'AdDataCenter.Ad_Creative' ,
'AdDataCenter.Ad_Targeting_Mobisage_AppIncluded' ,
'AdDataCenter.Ad_Targeting_Mobisage' ,
'AdDataCenter.Ad_Targeting' ,
'AdDataCenter.AdCreativeTemplate_SlotSize_Mapping' ,
'AdDataCenter.AdSync_AdCreative' ,
'AdDataCenter.App_MediaType_Mapping' ,
'AdDataCenter.AppOfferwall_Category_Mapping' ,
'AdDataCenter.Bill_AdAccount_Recharge_Daily' ,
'AdDataCenter.Bill_AdAccount_Spend_Daily' ,
'AdDataCenter.Dim_App' ,
'AdDataCenter.Dim_Slot' ,
'AdDataCenter.Docking_Product' ,
'AdDataCenter.Offerwall_AppBuckleScale_Mapping' ,
'AdDataCenter.Plugin_AdGroup' ,
'AdDataCenter.Slot_Affiliate_Business_Ratio' ,
'AdSync.Ad_Campaign_RealtimeControl' ,
'BCSourceData_Sync.Data_TaskSchedule' ,
'BCSourceData_Sync.Source_Fact_AccountHourlySpend' ,
'BCSourceData_Sync.Source_Fact_AdCreative_App_Usage_ThirdPartPlatform' )
