TRUNCATE 匹配名称模式的所有表 [英] TRUNCATE all tables matching name pattern

问题描述

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

This is the sql i'm using based from this answer:

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'.我究竟做错了什么?它似乎适用于其他用户

but I get this error Access denied for user 'root'@'%' to database 'information_schema'. What am I doing wrong? It seems to work for other users

推荐答案

您试图在 "information_schema" 数据库上执行此语句.阅读有关此数据库的更多信息 [https://dev.mysql.com/doc/refman/5.7/en/information-schema.html]

You trying to execute this statement on "information_schema" database. Read more about this database [https://dev.mysql.com/doc/refman/5.7/en/information-schema.html]

您不应该在 information_schema 数据库上运行语句(除非您真的知道自己在做什么).数据库充当元"存储库，指示服务器如何运行.很有可能您不需要触摸它，如果这样做，您可能会破坏服务器.

You should not be running statements on the information_schema database (unless you REALLY know what you're doing). The database serves as a "meta" repository that dictates how the server operates. Chances are that you have no need to touch it and you'll likely brick your server if you do.

这里已经回答了.[#1044 - 用户root"的访问被拒绝@'localhost' 到数据库 'information_schema'

上述限制:此查询仅当语句返回的表数为 1 时适用于 1 个以上的表，您将需要在迭代中使用它.

Restriction to above: This query will work only if the no of table returned by the statement is 1 for more than 1 tables, you will require to use it in iteration.

为了使所有与模式匹配的表都能工作，我们需要使用存储过程.

请更改程序名称

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

这篇关于TRUNCATE 匹配名称模式的所有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！