批量修改MySQL表前缀

http://www.jzbk.org/blog/2011-10/547.html

在数据库设计中，对于某个特定的项目，一般对其所有的数据表指定相同的表前缀，如WordPress的数据表都是以wp_开头的，Discuz的数据表都是以dz_开头的。这样不但便于区分，更重要的作用在于可以避免将多个项目部署到同一个数据库时可能出现的表同名冲突。

那么，有如下这种情况时：

用户A和用户B都使用wordpress程序，现在他们想将程序放在一起，但是主机上只分配了一个数据库给他们，意味着他们的数据需要存放在一个数据库，问题是他们先前已经创建好了wordpress的数据库，并且使用了相同的表前缀，都使用了wp_，那么，怎样批量修改表前缀呢。

通过PHP脚本显然可以做到，在MySQL中修改表名的语句为

RENAME TABLE oldname TO newname

这里，我尝试采用纯SQL语句来实现，捣鼓了一阵子，于是有了下面这个存储过程：

-- @author xueyu
-- @desc modify the prefix of table name in db
-- @example
--
-- use DBNAME;
-- show tables;
-- source ~/change_prefix.sql;
--
-- call change_prefix('old_', 'new_', 'DBNAME');
--
-- show tables;
-- drop procedure if exists change_prefix;
--
 
delimiter //
DROP procedure IF EXISTS change_prefix //
 
CREATE procedure change_prefix(IN oldpre VARCHAR(200), IN newpre VARCHAR(200), IN dbname VARCHAR(200))
begin
  declare done INT DEFAULT 0;
  declare oldname VARCHAR(200);
  declare cur CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema= dbname AND table_name LIKE concat(oldpre,'%');
  declare continue handler FOR NOT found SET done = 1;
  open cur;
 
  repeat
    fetch cur INTO oldname;
    IF NOT done then
        SET @newname = concat(newpre, trim(LEADING oldpre FROM oldname));
        SET @sql = concat('rename table ',oldname,' to ',@newname);
        prepare tmpstmt FROM @sql;
        execute tmpstmt;
        deallocate prepare tmpstmt;
    end IF;
  until done end repeat;
 
  close cur;
end //
 
delimiter ;

使用方法很简单，直接导入这个脚本，然后运行

call change_prefix('old_', 'new_', 'DB_NAME');

即可，注意，脚本有删除change_prefix这句，如果你有同样命名的存储过程，注意去掉。用完之后，可以将这个存储过程删除掉。
我们可以批量给数据库里的所有表加上同样的前缀，第一个参数留即可

call change_prefix ( '' , 'added_' , 'DB_NAME' );

同理，第二个参数留空可以去掉指定的表前缀。