mysql> select @@version; +-----------+ | @@version | +-----------+ | 5.5.23 | +-----------+ 1 row in set (0.00 sec)mysql> rename database db1 to db2; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use n r 'database db1 to db2' at line 1 mysql>rename database,此语法还不被支持。 楼主的问题按引擎分为2种情况 1:MYISAM,只需要把库名字的文件夹名称改了就行。 2:INNODB,如果库里面有触发器,需要先删除触发器,库改名后再添加。需要对库里面每个表改名称,存从过程如下:CREATE DEFINER=`root`@`localhost` PROCEDURE `Switch_DB`( ORIGIN_DB_NAME varchar(500), TARGET_DB_NAME varchar(500) ) BEGIN DECLARE done INT DEFAULT 0; DECLARE a varchar(200); DECLARE b varchar(200); DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema=ORIGIN_DB_NAME ; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1; REPEAT FETCH cur1 INTO a; IF NOT done THEN set b=concat('rename table ',ORIGIN_DB_NAME,'.',a,' to ',TARGET_DB_NAME,'.',a); SET @E=b; PREPARE stmt1 FROM @E; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; END IF; UNTIL done END REPEAT; CLOSE cur1;
END;关键点在:rename table db1.tb to db2.tb. 其实这好像不是“改名”了,而且“移表”了。
RENAME DATABASE db_name TO new_db_name;
or
RENAME SCHEMA db_name TO new_db_name;
This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23. 记住了伐?
没有rename database的语法吧?确定吗
5.1版本以下版本的,最好先导出后再建立数据库,后再导入。
+-----------+
| @@version |
+-----------+
| 5.5.23 |
+-----------+
1 row in set (0.00 sec)mysql> rename database db1 to db2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use n
r 'database db1 to db2' at line 1
mysql>rename database,此语法还不被支持。
楼主的问题按引擎分为2种情况
1:MYISAM,只需要把库名字的文件夹名称改了就行。
2:INNODB,如果库里面有触发器,需要先删除触发器,库改名后再添加。需要对库里面每个表改名称,存从过程如下:CREATE DEFINER=`root`@`localhost` PROCEDURE `Switch_DB`(
ORIGIN_DB_NAME varchar(500),
TARGET_DB_NAME varchar(500)
)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a varchar(200);
DECLARE b varchar(200);
DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema=ORIGIN_DB_NAME ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO a;
IF NOT done THEN
set b=concat('rename table ',ORIGIN_DB_NAME,'.',a,' to ',TARGET_DB_NAME,'.',a);
SET @E=b;
PREPARE stmt1 FROM @E;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END;关键点在:rename table db1.tb to db2.tb.
其实这好像不是“改名”了,而且“移表”了。