欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

mysql生成表文件、truncate、Load_MySQL

程序员文章站 2022-04-25 18:04:55
...
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'	)
相关标签: 文件