总所周知,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 changedmysql> 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 restartStopping 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 restartStopping 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 -p123456mysql> 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/data3)禁止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/datamysql> 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 statementmysql> 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