mysql下批量清空某个库下的所有表(库不要删除，保留空库)

时间：2021-01-05 16:59:03

总所周知，mysql下要想删除某个库下的某张表，只需要切换到该库下，执行语句"drop table tablename"即可删除！但若是该库下有成百上千张表，要是再这样一次次执行drop语句，就太费劲了！

正确的批量删除某个库下的所有表的方法只需如下两步：
1）第一步（只需将下面的"库名"替换成实际操作中的库名即可）
select concat('drop table ',table_name,';') from information_schema.TABLES where table_schema='库名';
2）第二步
切换到这个库下，把第一步的执行结果导出，然后全部执行

例如：
批量删除kevin库下的所有表

mysql> select concat('drop table ',table_name,';') from information_schema.TABLES where table_schema='kevin';

+--------------------------------------+

| concat('drop table ',table_name,';') |

+--------------------------------------+

| drop table haha;                     |  #只需要复制这里的drop语句，放在一起批量粘贴执行即可！（这里kevin库下就2张表，如果是N张表，就执行复制->粘贴执行）

| drop table heihei;                   |

+--------------------------------------+

2 rows in set (0.00 sec)

mysql> use kevin;                            #切换到kevin库下，然后执行将上面复制的drop语句，直接粘贴执行即可！

Database changed

mysql> drop table haha;

Query OK, 0 rows affected (0.09 sec)

mysql> drop table heihei;

Query OK, 0 rows affected (0.08 sec)

确实采用上面直接复制拼接语句查询出来结果的方法，在多表情况下比较复杂，却需要调整格式。优化方案是：将查询结果直接导出到文件，再直接source 执行文件。做法如下：

1）在mysql配置文件里添加数据导出导入权限。

[root@localhost ~]# vim /etc/my.cnf

.......

[mysqld]

secure_file_priv=/opt/mysql/data                 //表示打开mysql数据导出导入权限，且限制导出导入只能发生在/opt/mysql/data目录下

给指定的/opt/mysql/data目录授予sql用户权限

[root@localhost ~]# chown -R mysql.mysql /opt/mysql

重启mysql服务

[root@localhost ~]# /etc/init.d/mysqld restart

Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

将kevin库下的所有表导出到授权的/opt/mysql/data目录下

mysql> select concat('drop table ',table_name,';') from information_schema.TABLES where table_schema='kevin';

+--------------------------------------+

| concat('drop table ',table_name,';') |

+--------------------------------------+

| drop table anan;                     |

| drop table beibei;                   |

| drop table haha;                     |

| drop table hehe;                     |

| drop table heihei;                   |

| drop table huihui;                   |

| drop table huohuo;                   |

| drop table jiajia;                   |

| drop table jingjing;                 |

| drop table liuliu;                   |

| drop table popo;                     |

| drop table qiuqiu;                   |

| drop table renren;                   |

| drop table youyou;                   |

+--------------------------------------+

14 rows in set (0.00 sec)

现在开始批量删除kevin库下的所有表

[root@localhost ~]# cat /opt/mysql/data/table.txt

drop table anan;

drop table beibei;

drop table haha;

drop table hehe;

drop table heihei;

drop table huihui;

drop table huohuo;

drop table jiajia;

drop table jingjing;

drop table liuliu;

drop table popo;

drop table qiuqiu;

drop table renren;

drop table youyou;

mysql> select concat('drop table ',table_name,';') from information_schema.TABLES where table_schema='kevin' into outfile '/opt/mysql/data/table.txt';

Query OK, 14 rows affected (0.00 sec)

mysql> source /opt/mysql/data/table.txt;

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;

Empty set (0.00 sec)

mysql>

==================关于Mysql数据导出导入参数secure_file_priv说明===================
MySQL 5.7版本后引入了secure_file_priv参数，这个参数用来限制数据导入和导出操作的效果，比如用来限制LOAD DATA, SELECT ... OUTFILE, and LOAD_FILE()传到哪个指定目录的，这些操作需要用户具有FILE权限。 在mysql配置文件my.cnf的[mysqld]区域下配置：
1）如果这个参数设为空或"/"，则MySQL服务允许将将数据导出到任意目录。
2）如果这个参数设为一个具体的目录名，则MySQL服务只允许在此目录中执行文件导入和导出操作。这个目录必须存在且设置为mysql用户权限，MySQL服务不会创建它；
3）如果这个参数为NULL，则MySQL服务会禁止导入和导出操作。
4）如果没有这个参数配置，则Mysql服务默认是没有打开这个功能，即不能进行数据导入导出操作。

配置示例：

1）允许mysql数据导入导出，且导出到任意目录（注意导出到的目录权限要是mysql.mysql）

[root@localhost ～]# vim /usr/local/my.cnf

[mysqld]

......

secure_file_priv=                     //或者配置成secure_file_priv="/"

[root@localhost ～]# /etc/init.d/mysqld restart

Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

比如导出到/mnt/data目录下，则

[root@localhost ～]# mkdir /mnt/data

[root@localhost ～]# chown -R mysql.mysql /mnt/data/

登录mysql查看secure-file-priv参数的值：

mysql> select version();

+------------+

| version()  |

+------------+

| 5.6.39-log |

+------------+

1 row in set (0.02 sec)

mysql> show global variables like '%secure%';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| secure_auth      | OFF   |

| secure_file_priv | /     |

+------------------+-------+

2 rows in set (0.00 sec)

导出到指定的/mnt/data/data.txt文件中，该文件不能是已存在状态，mysql数据导出的时候会自动创建该文件。

[root@localhost ～]# mysql -p123456

mysql> select concat('drop table ',table_name,';') from information_schema.TABLES where table_schema='kevin' into outfile '/mnt/data/table.txt';

删除kevin库下的所有表

mysql> use kevin;

mysql> source /mnt/data/table.txt;

mysql> show tables;                                 //查询kevin库，发现所有表已经被删除。

2）如果允许mysql数据导出导入，并限制导出到指定的/opt/kevin/data下，则配置：

[root@localhost ～]# vim /usr/local/my.cnf

[mysqld]

......

secure_file_priv= /opt/kevin/data

[root@localhost ～]# mkdir -p /opt/kevin/data

[root@localhost ～]# chown -R mysql.mysql /opt/kevin/data

3）禁止mysql服务进行数据导出导入操作。

[root@localhost ～]# vim /usr/local/my.cnf

[mysqld]

......

secure_file_priv= NULL

[root@localhost ～]# mkdir -p /opt/kevin/data

[root@localhost ～]# chown -R mysql.mysql /opt/kevin/data

mysql> select concat('drop table ',table_name,';') from information_schema.TABLES where table_schema='kevin' into outfile '/opt/data/table.txt';

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

mysql> 

4）如果my.cnf里没有secure_file_priv这个参数配置，则默认是关闭这个功能，即不允许数据导入导出操作。

mysql> select concat('drop table ',table_name,';') from information_schema.TABLES where table_schema='kevin' into outfile '/opt/data/table.txt';

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

相关文章