批量修改表名的操作方法，以下操作请用navicat操作即可，快捷方便:
|
1
2 3 |
SELECT CONCAT( ‘ALTER TABLE ‘, TABLE_NAME, ‘RENAME TO ‘, TABLE_NAME,‘;’ )
FROM information_schema.TABLES WHERE TABLE_NAME LIKE ‘uc_%’; |
执行后得到如下的结果：
|
1
2 |
ALTER TABLE uc_aaa RENAME TO uc_aaa;
ALTER TABLE uc_bbb RENAME TO uc_bbb; |
保留如下的数据：
|
1
2 |
ALTER TABLE uc_aaa RENAME TO uc_aaa;
ALTER TABLE uc_bbb RENAME TO uc_bbb; |
然后选择要修改的数据库，执行上面得到的SQL语句就可以修改掉数据库表前缀了。
附：1、批量删除指定前缀的表
|
1
2 3 |
SELECT CONCAT( ‘drop table ‘, TABLE_NAME, ‘;’ )
FROM information_schema.TABLES WHERE TABLE_NAME LIKE ‘uc_%’; |
2、“dbtable_name”改成“db_table_name”
|
1
2 3 |
SELECT CONCAT( ‘ALTER TABLE ‘, TABLE_NAME, ‘RENAME TO db_’, SUBSTRING(TABLE_NAME,3),‘;’ )
FROM information_schema.TABLES WHERE TABLE_NAME LIKE ‘db%’; |