MySQL不支持将整个数据库(db schema)重命名,我们可以通过自己写一个存储过程来实现。
实现思路:
1)创建目标数据库。
2)执行命令:rename table 原库.表 to 目标库.表,将表重命名。
3)删除原库。
-- 说明:出于安全考虑,删除原库的操作在下述存储过程中已经注释掉,如果需要启用,去掉语句前的#号即可。
DELIMITER $$
CREATE DATABASE IF NOT EXISTS dba $$
USE dba $$
DROP PROCEDURE IF EXISTS rename_database_from_to $$
CREATE DEFINER = 'root'@'127.0.0.1' PROCEDURE rename_database_from_to(IN database_from VARCHAR(32), IN database_to VARCHAR(32))
SQL SECURITY DEFINER
BEGIN
DECLARE sql_string VARCHAR(128);
DECLARE tmp_table_name VARCHAR(64);
DECLARE found_no_data INT DEFAULT 0;
DECLARE cursor_1 CURSOR FOR SELECT table_name FROM information_schema.tables where TABLE_TYPE = 'BASE TABLE' AND table_schema = database_from;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET found_no_data = 1;
SET sql_string = CONCAT('CREATE DATABASE IF NOT EXISTS ', database_to);
SET @sql = sql_string;
PREPARE sql_stmt from @sql;
EXECUTE sql_stmt;
DEALLOCATE PREPARE sql_stmt;
OPEN cursor_1;
end_loop: LOOP
FETCH cursor_1 INTO tmp_table_name;
IF found_no_data = 1 THEN
LEAVE end_loop;
END IF;
SET sql_string = CONCAT('RENAME TABLE ', database_from, '.', tmp_table_name, ' TO ', database_to, '.', tmp_table_name);
SET @sql = sql_string;
PREPARE sql_stmt from @sql;
EXECUTE sql_stmt;
DEALLOCATE PREPARE sql_stmt;
END LOOP end_loop;
CLOSE cursor_1;
# 删除原库,如果要启用,请去掉语句前的(#)号,出于安全考虑,请谨慎使用
# SET sql_string = CONCAT('DROP DATABASE IF EXISTS ', database_from);
# SET @sql = sql_string;
# PREPARE sql_stmt from @sql;
# EXECUTE sql_stmt;
# DEALLOCATE PREPARE sql_stmt;
END $$
DELIMITER ;
call dba.rename_database_from_to('testZ','testa');