mysql - TRUNCATE 所有匹配名称模式的表

Question

这是我正在使用的基于this的sql答案:

SET @pattern = '%_movielist';

SELECT concat('TRUNCATE TABLE ', GROUP_CONCAT(concat(TABLE_NAME)), ';')
INTO @truncatelike FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE @pattern;

SELECT @truncatelike;

PREPARE stmt FROM @truncatelike;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

但我收到此错误用户“root”@'%'对数据库“information_schema”的访问被拒绝。我究竟做错了什么？它似乎适用于其他用户

Answer 1

您尝试在“information_schema”数据库上执行此语句。了解有关此数据库的更多信息 [ https://dev.mysql.com/doc/refman/5.7/en/information-schema.html]

您不应该在 information_schema 数据库上运行语句(除非您真的知道自己在做什么)。该数据库充当“元”存储库，指示服务器如何运行。您很可能不需要碰它，如果碰的话，您的服务器可能会变砖。

这里已经回答了这个问题。 [#1044 - Access denied for user 'root'@'localhost' to database 'information_schema'

对上述的限制:仅当语句返回的表数为 1(对于 1 个以上的表)时，此查询才会起作用，您将需要在迭代中使用它。

要使此功能适用于与模式匹配的所有表，我们需要使用存储过程。

请更改过程名称

CREATE PROCEDURE `new_procedure`()
BEGIN
-- Pattern to Match 
SET @pattern = '%_movielist';
-- Temporary Table to Store the Result of The Select Statement

CREATE TEMPORARY TABLE IF NOT EXISTS Table_ToBeTruncated 
    (
       Id int NOT NULL AUTO_INCREMENT,TableName varchar(100),
       PRIMARY KEY (id)
    );

-- Insert all the TableName  to be Truncated 
    insert Table_ToBeTruncated(TableName)
    SELECT distinct concat('TRUNCATE TABLE `', TABLE_NAME, '`;')
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME LIKE @pattern and TABLE_SCHEMA = 'movielist';

-- Declare a variable to count the no of records to be truncated.
SET @count=(Select count(*)from Table_ToBeTruncated);

-- Iterate the list 
WHILE @count> 0 DO

    -- Pick One table from the Temporary Table List;
    SELECT TableName into @truncatelike from Table_ToBeTruncated where ID= @count;

    -- Prepare the statement
    PREPARE stmt FROM @truncatelike;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Decrease the counter.
    set @count = @count- 1;

END WHILE;

drop TEMPORARY TABLE IF EXISTS Table_ToBeTruncated ;

END