mysql 重命名库_MySQL 库名重命名

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 ;