Mysql generates table files, truncate, Load, and 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; ')ExeSqlFROM 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')
I use mysql, and some tables do not have primary or foreign keys. When I use the truncate command to clear these tables,
Tables with foreign key connections cannot be directly cleared .. I don't know the specific table structure ..
Should the return values of the mysql truncate command and the count command be the same?
Can you check the differences between the following two deletion statements in your database?
TRUNCATE and DELETE have the following differences:
1. TRUNCATE is very fast on various tables, whether large or small. If the ROLLBACK command DELETE is used, the TRUNCATE command is not used.
2. TRUNCATE is a DDL language. Like all other DDL languages, it will be implicitly submitted and cannot use the ROLLBACK command for TRUNCATE.
3. TRUNCATE will reset the high horizontal line and all indexes. When you completely browse the entire table and index, the table after the TRUNCATE operation is much faster than the table after the DELETE operation.
4. TRUNCATE cannot trigger any DELETE trigger.
5. You cannot grant anyone the permission to clear tables of others.
6. After the table is cleared, the index of the table and the table is reset to the initial size, while the delete statement is not.
7. The parent table cannot be cleared.