harryxu · December 9, 2011 04:50 · Dec 9, 2011
diff --git a/change_prefix.sql b/change_prefix.sql
@@ -0,0 +1,41 @@
+-- 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 ;
diff --git a/demo.sql b/demo.sql
@@ -0,0 +1,9 @@
+-- 使用方法很简单，直接导入这个脚本，然后运行
+
+call change_prefix('old_', 'new_', 'DB_NAME');
+
+-- 即可，注意，脚本有删除change_prefix这句，如果你有同样命名的存储过程，注意去掉。用完之后，可以将这个存储过程删除掉。
+-- 我们可以批量给数据库里的所有表加上同样的前缀，第一个参数留即可
+
+call change_prefix('', 'added_', 'DB_NAME');
+-- 同理，第二个参数留空可以去掉指定的表前缀。
No results found