批量修改MySQL表前缀

change_prefix.sql

-- http://blog.itmem.com/?p=1062
-- @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 ;

demo.sql

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

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

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

call change_prefix('', 'added_', 'DB_NAME');
-- 同理，第二个参数留空可以去掉指定的表前缀。