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

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

本文采用 CC BY-NC-SA 4.0 创作共享协议,转载请标注以下信息:
【转载自LiuJason's Blog:https://www.liujason.com/article/186.html

说点什么

Please Login to comment
avatar
  Subscribe  
提醒