Mysql批量修改表名前缀和批量删除数据表

本文以wordpress数据库为例

wordpress-251x300.png

mysql批量修改表名前缀

1、把全部表名前缀wp_改成wordpress_

SELECT GROUP_CONCAT (CONCAT( ‘ALTER TABLE ‘ , TABLE_NAME, ‘ RENAME TO ‘,
REPLACE( TABLE_NAME,‘wp_’ ,‘wordpress_’)) SEPARATOR ‘;’)
FROM information_schema .TABLES
WHERE table_schema =‘wordpress’ AND table_type =‘BASE TABLE’ AND TABLE_NAME LIKE ‘wp_%’ ;

2、去掉全部表名前缀wordpress_

SELECT GROUP_CONCAT (CONCAT( ‘ALTER TABLE ‘ , TABLE_NAME, ‘ RENAME TO ‘,
REPLACE( TABLE_NAME,‘wordpress_’ ,”)) SEPARATOR ‘;’)
FROM information_schema .TABLES
WHERE table_schema =‘wordpress’ AND table_type =‘BASE TABLE’ AND TABLE_NAME LIKE ‘wordpress_%’ ;

3、给全部表名添加前缀名wp_

SELECT GROUP_CONCAT (CONCAT( ‘ALTER TABLE ‘ , TABLE_NAME, ‘ RENAME TO ‘,‘wp_’, TABLE_NAME ) SEPARATOR ‘;’ )
FROM information_schema .TABLES
WHERE table_schema =‘wordpress’ AND table_type =‘BASE TABLE’;

mysql批量删除数据表

1、批量删除指定前缀名的表

SELECT GROUP_CONCAT (CONCAT( ‘DROP TABLE ‘, TABLE_NAME) SEPARATOR ‘;’)
FROM information_schema .TABLES
WHERE table_schema =‘wordpress’ AND table_type =‘BASE TABLE’ AND TABLE_NAME LIKE ‘wp_%’ ;

2、批量删除数据库全部表

SELECT GROUP_CONCAT (CONCAT( ‘DROP TABLE ‘, TABLE_NAME) SEPARATOR ‘;’)
FROM information_schema .TABLES
WHERE table_schema =‘wordpress’ AND table_type =‘BASE TABLE’;

备注：以上SQL只是生成所需要的SQL，你还需要把生成的字符串复制到查询分析器进行执行，特别提醒，执行之前一定要确定当前use的哪个数据库，否则造成数据表误删除麻烦就大了。