mysql运行sql文件truncate table_mysql生成表文件、truncate、Load_MySQL

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')

相关标签：文件

本文原创发布php中文网，转载请注明出处，感谢您的尊重！