主页 / dba / 问题 / 1018
Accepted
poelinca
Asked: 2011-02-02 23:50:19 +0800 CST

MySQL：以前缀开头的 DROP TABLE

  • 772

示例：我有 30 多个以前缀“dp_”开头的表和大约 12 个以“ex_”开头的表。

问题：如何在一个查询中删除所有以“dp_”开头的表？

mysql

5 个回答

  1. Best Answer

    这是一个接受数据库和前缀字符串作为参数的存储过程：

    DELIMITER $$

DROP PROCEDURE DropTablesWithPrefix $$
CREATE PROCEDURE DropTablesWithPrefix(db VARCHAR(64),prfx VARCHAR(20))
StoredProcedure:BEGIN
    DECLARE ndx,maxidx INT;
    DECLARE giventable,SQLSTMT VARCHAR(500);

    CREATE TABLE TableZapList
    (
        droptablesql VARCHAR(512),
        idx INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ) ENGINE=MyISAM;

    INSERT INTO TableZapList (droptablesql)
    SELECT CONCAT('DROP TABLE ',table_schema,'.',table_name)
    FROM information_schema.tables
    WHERE table_schema = db AND SUBSTR(table_name,LENGTH(prfx)) = prfx;
    SELECT COUNT(1) INTO maxid FROM TableZapList;

    IF maxid = 0 THEN
        LEAVE StoredProcedure;
    END IF;<BR>

    SET ndx = 0;
    WHILE ndx < maxid DO
        SET ndx = ndx + 1;
        SELECT droptablesql INTO SQLSTMT FROM TableZapList WHERE idx = ndx;
        SET @sql = SQLSTMT;
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END WHILE;<BR>

    SELECT droptablesql FROM TableZapList;
    DROP TABLE TableZapList;
END;

DELIMITER ;

    更新 2011-07-12 14:55 EDT

    我只是想到了一种更清洁，简单的方法。无需使用存储过程，只需使用 GROUP_CONCAT 函数来收集所有要 zap 的表。然后组合成一个查询：

    这是一个删除当前数据库中所有以 wp_pol 开头的表的查询：

    SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';')
FROM information_schema.tables
WHERE table_schema=database()
AND table_name like 'wp_pol%';

    接下来要做的是将它的结果存储在

    SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';')
INTO @dropcmd
FROM information_schema.tables
WHERE table_schema=database()
AND table_name like 'wp_pol%';

    最后一件事是使用这三 (3) 个命令执行动态 SQL：

    PREPARE s1 FROM @dropcmd;
EXECUTE s1;
DEALLOCATE PREPARE s1;

    这是在 Windows 中使用 MySQL 5.5.12 的演示：

    Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.12 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

MySQL (Current test) :: use garbage
Database changed
MySQL (Current garbage) :: show tables;
+------------------------------+
| Tables_in_garbage            |
+------------------------------+
| datas                        |
| rolando                      |
| wp_commentmeta               |
| wp_comments                  |
| wp_contact_form_7            |
| wp_links                     |
| wp_most_read_hits            |
| wp_options                   |
| wp_pollsa                    |
| wp_pollsip                   |
| wp_pollsq                    |
| wp_postmeta                  |
| wp_posts                     |
| wp_posts_idtracker           |
| wp_tantan_wordpress_s3_cache |
| wp_term_relationships        |
| wp_term_taxonomy             |
| wp_terms                     |
| wp_usermeta                  |
| wp_users                     |
| wp_w3tc_cdn_queue            |
+------------------------------+
21 rows in set (0.00 sec)

MySQL (Current garbage) :: SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';') INTO @dropcmd FROM information_schema.tables WHERE table_schema=database() AND table_name like 'wp_pol%';
Query OK, 1 row affected (0.00 sec)

MySQL (Current garbage) :: SELECT @dropcmd;
+--------------------------------------------------------------------+
| @dropcmd                                                           |
+--------------------------------------------------------------------+
| DROP TABLE garbage.wp_pollsa,garbage.wp_pollsip,garbage.wp_pollsq; |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL (Current garbage) :: PREPARE s1 FROM @dropcmd; EXECUTE s1; DEALLOCATE PREPARE s1;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

MySQL (Current garbage) :: show tables;
+------------------------------+
| Tables_in_garbage            |
+------------------------------+
| datas                        |
| rolando                      |
| wp_commentmeta               |
| wp_comments                  |
| wp_contact_form_7            |
| wp_links                     |
| wp_most_read_hits            |
| wp_options                   |
| wp_postmeta                  |
| wp_posts                     |
| wp_posts_idtracker           |
| wp_tantan_wordpress_s3_cache |
| wp_term_relationships        |
| wp_term_taxonomy             |
| wp_terms                     |
| wp_usermeta                  |
| wp_users                     |
| wp_w3tc_cdn_queue            |
+------------------------------+
18 rows in set (0.00 sec)

MySQL (Current garbage) ::

    试试看 ！！！

    • 11

  2. 首先，在 Unix 提示符下生成一个脚本来执行此操作：

    $  echo "select concat('drop table ', table_name, ';') from information_schema.tables where table_name like 'prefix_%';" |mysql --user=root --password=blah --batch >drop.sql

    将前缀替换为您自己的。该--batch选项会抑制 MySQL 默认执行的精美格式，因此您可以生成可运行的 SQL 脚本。

    查看脚本，如果看起来正常，drop.sql请在mysql>提示符下运行。

    • 8

  3. 您应该查询系统表以获取这些表名并构建一个字符串以动态执行它。在 SQL Server 中，我会执行以下操作：

    declare @x varchar(max), @enter char(2);
select @x = '', @enter = char(13)+char(10);

Select @x = @x + 'drop table ' + table_name + ';' + @enter    
from information_schema.tables    
where table_name like '%accounting%'

print @x
execute (@x);

    现在你必须在 MySQL 中找到对应的系统表。

    • 4

  4. 要回答你的问题，不。不在 MySQL 中使用单个命令/查询。你必须链接命令。

    但是，如果你想实现你的目标，这里有一种方法：

    从 bash 脚本类似于：

    #/bin/bash
TABLES=`mysql -s -e "SELECT CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) AS T FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'dp_%'" -u user -p`

for i in $TABLES; 
do 
 echo "DROP TABLE $i;" >> drops.sql ; 
done

cat drops.sql

    然后查看 drops.sql 文件。如果一切都好，做一个BACKUP，然后......

    mysql -u username -p -v --show-warnings < drops.sql
    • 3

  5. 你总是可以使用像navicat这样的数据库管理员，这样的问题就会通过简单的选择和删除来消失。

    • 1

相关问题