在一个 MySQL 语句中截断多个表 [英] Truncate multiple tables in one MySQL statement

问题描述

是否可以用一个 SQL 语句截断多个表?

Is there a possibility to truncate with one SQL statement, multiple tables?

像这样:

 truncate table #OBJ_AvailabilityTraining, #OBJ_AvailabilityHoliday, #Dates_temp;

问候

推荐答案

不，您只能使用 TRUNCATE 命令截断单个表.要截断多个表，您可以使用 T-SQL 并遍历表名以一次截断每个表.

No, you can only truncate a single table with TRUNCATE command. To truncate multiple tables you can use T-SQL and iterate through table names to truncate each at a time.

DECLARE @delimiter CHAR(1),
        @tableList VARCHAR(MAX),
        @tableName VARCHAR(20),
        @currLen INT

SET @delimiter = ','

SET @tableList = 'table1,table2,table3'

WHILE LEN(@tableList) > 0
BEGIN
    SELECT @currLen = 
    (
        CASE charindex( @delimiter, @tableList ) 
            WHEN 0 THEN len( @tableList  ) 
            ELSE ( charindex( @delimiter, @tableList  ) -1 )
        END
    ) 

    SELECT @tableName = SUBSTRING (@tableList,1,@currLen )

    TRUNCATE TABLE @tableName

    SELECT tableList = 
    (
        CASE ( len( @tableList ) - @currLen  ) 
            WHEN 0 THEN '' 
            ELSE right( @tableList, len( @tableList ) - @currLen  - 1 ) 
        END
    ) 
END

您可以在@tableList 变量中将所有表名以逗号分隔，是的，您可以截断来自不同架构的多个表(如果它们带有前缀).

You can have all your table names comma separated in @tableList variable and yes you can truncate multiple tables from different schemas if they are prefixed.

这篇关于在一个 MySQL 语句中截断多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！