mysql 生成表文件、truncate、Load

最新推荐文章于 2025-12-06 13:46:53 发布

原创最新推荐文章于 2025-12-06 13:46:53 发布 · 1.1k 阅读

CC 4.0 BY-SA版权

本文介绍了使用SQL语句进行数据库表的批量操作，包括选择、插入、删除、更新等，以及如何通过联接和拼接字符串实现复杂的数据修复流程。重点涵盖了特定条件下的表名筛选、文件输出和导入、数据清空、重新加载等关键步骤。


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