• Welcome to LiuJason's Blog!

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

Web项目 Jason 1 years ago (2019-02-03) 338 Views 0 Comments QR code of this page

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


This article is under CC BY-NC-SA 4.0 license.
Please quote the original link:https://www.liujason.com/article/186.html
Like (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址