本文以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的哪个数据库，否则造成数据表误删除麻烦就大了。
