MySQL 库名重命名

vansky 2021-11-29 原文

MySQL ( RENAME database olddbname TO newdbname ) 对库名的重命名上会出现一些奇怪的错误。有丢失数据的风险。

所以如何去重命名呢：

1 用mysqldump出原来库数据再导入到新的库。这当然是笨拙的方法

2 使用rename table olddbname.tablename to newdbname.tablename 的方法来重命名库（更改完表明之后把久的库删掉）。可以安全快速的重命名库。

注意：Mysql 的rename table 对于分区表数据存在与单独表空间且表空间不为默认目录。即把分区放在的别的目录下。这时候rename是不成功的。

下面是重新rename库的一个存储过程。方便大家使用：

delimiter //
set session sql_log_bin=OFF//
DROP PROCEDURE IF EXISTS renamedb //
use mysql //
CREATE  DEFINER=`root`@`localhost`  PROCEDURE `renamedb`(SCHEMANAME VARCHAR(128), NEW_SCHEMANAME VARCHAR(128),sure int )
COMMENT \'数据库重命名 call  renamedb(dbname,new_dbname,0/1)   0表示提醒如果新的库名已经存在不会把表rename过去,1表示强制  \'
label:BEGIN
DECLARE Done INT DEFAULT 0;
DECLARE MY_TABLE_NAME  VARCHAR(128);
DECLARE OLD_TABLE_NAME VARCHAR(128);
DECLARE NEW_TABLE_NAME VARCHAR(128);

DECLARE rs CURSOR FOR select TABLE_NAME  from information_schema.tables where table_schema=SCHEMANAME AND table_schema NOT IN(\'mysql\',\'performance_schema\',\'information_schema\',\'sys\');
DECLARE CONTINUE HANDLER FOR SQLSTATE \'02000\' SET Done = 1;/* 异常处理 */

IF  NOT EXISTS (select SCHEMA_NAME from information_schema.SCHEMATA where SCHEMA_NAME=SCHEMANAME AND SCHEMA_NAME NOT IN(\'mysql\',\'performance_schema\',\'information_schema\',\'sys\')) THEN
select concat(SCHEMANAME,"库名不存在或为系统库");
leave label;
END IF;

IF  EXISTS (select SCHEMA_NAME from information_schema.SCHEMATA where SCHEMA_NAME=NEW_SCHEMANAME ) AND sure<>1 THEN
select concat(NEW_SCHEMANAME,"库名已存在,强制rename请call(xxx,new_xxx,1)");
leave label;
END IF;
set @crtdb=concat("create database IF NOT EXISTS  ",NEW_SCHEMANAME);
PREPARE stmtcrtdb FROM  @crtdb;
EXECUTE stmtcrtdb;
OPEN rs;
FETCH NEXT FROM rs INTO MY_TABLE_NAME;
REPEAT
IF NOT Done THEN
select concat(SCHEMANAME,".",MY_TABLE_NAME) into OLD_TABLE_NAME;
select concat(NEW_SCHEMANAME,".",MY_TABLE_NAME) into NEW_TABLE_NAME;
set @rename_table=concat("rename table ",OLD_TABLE_NAME," to ",NEW_TABLE_NAME);
select concat(@rename_table,";");
PREPARE stmtrename_table FROM @rename_table;
EXECUTE stmtrename_table;
END IF;
FETCH NEXT FROM rs INTO MY_TABLE_NAME;
UNTIL Done END REPEAT;
CLOSE rs;
set @oldtable=concat(\'show tables from \',SCHEMANAME);
set @newtable=concat(\'show tables from \',NEW_SCHEMANAME);
PREPARE stmtoldtable FROM @oldtable;
EXECUTE stmtoldtable;
PREPARE stmtnewtable FROM @newtable;
EXECUTE stmtnewtable;
END //
delimiter ;