mysql 生成表文件、truncate、Load，mysqltruncate

mysql 生成表文件、truncate、Load，mysqltruncate

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

有外键连接的表貌似不能直接清空数据。。不知道你具体表结构我也不好说。。
 

你数据库 可以看看吗 注意下面 2个删除语句的区别
TRUNCATE和DELETE有以下几点区别
1、TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令DELETE将被撤销，而TRUNCATE则不会被撤销。
2、TRUNCATE是一个DDL语言，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。
3、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比DELETE操作后的表要快得多。
4、TRUNCATE不能触发任何DELETE触发器。
5、不能授予任何人清空他人的表的权限。
6、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。
7、不能清空父表。