mysql数据库改名字

严格意义上说，mysql5.6版本以后是不支持改库名的。我们可以通过rename table的方式，将表从一个库迁移到另一个库，变相完成改名。

方法1：将old_db库的对象迁移到new_db库

#查看old_db库的数据库对象 
use information_schema; 
select table_name from TABLES where TABLE_SCHEMA='old_db'; 
#创建新库
create database new_db; 
#迁移到新库 
rename table old_db.[table_name] to new_db.[table_name];
复制

方法2：存储过程改名

#改名语句

call manager_db.renamedb('old_db','new_db');
复制

执行操作前需要在数据库创建存储过程manager_db.renamedb，语句如下：

#创建库、日志表 
create database if not exists manager_db; 
create table if not exists manager_db.hz_log(id bigint not null auto_increment primary key ,start_time datetime,end_time datetime,log_text varchar(500)); 

#创建存储过程 
DELIMITER //
CREATE PROCEDURE manager_db.renamedb(IN old_dbname VARCHAR(30),IN new_dbname VARCHAR(30))
BEGIN
  DECLARE tab_name VARCHAR(50);
  DECLARE done INT DEFAULT 0;
    DECLARE msg VARCHAR(500) default '';
  DECLARE cursor_tab CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema=old_dbname; 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1 ;
  OPEN cursor_tab;
    
    SET @start_time=NOW();
  SELECT COUNT(*) INTO @num_olddb FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = old_dbname;
    IF @num_olddb=0 THEN
    set msg=concat(msg,old_dbname ,' is not exists!!! 操作已经终止！-->');
        SET done=1;
  ELSE
    SELECT COUNT(*) INTO @num_olddb_tab FROM information_schema.tables WHERE table_schema=old_dbname; 
        IF @num_olddb_tab=0 THEN
      set msg=concat(msg, old_dbname ,' is empty!!! 操作告警！ -->');
    END IF;
   
    SELECT COUNT(*) INTO @num_newdb FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = new_dbname;
    IF @num_newdb=0 THEN
      SET @stmt_cretedb=CONCAT("CREATE DATABASE ",new_dbname," CHARSET UTF8MB4 COLLATE UTF8MB4_GENERAL_CI;");
      PREPARE stmt_cretedb FROM @stmt_cretedb;
      EXECUTE stmt_cretedb;
      set msg=concat(msg,new_dbname,' is created!-->');
      DEALLOCATE PREPARE stmt_cretedb;
    ELSE
      SELECT COUNT(*) INTO @num_newdb_tab FROM information_schema.tables WHERE table_schema=new_dbname; 
      IF @num_newdb_tab>0 THEN
        set msg=concat(msg ,new_dbname ,' is not empty!!! 操作已经终止！-->');
        SET done=1;
      END IF;
    END IF;
  END IF;
  TABLE_LOOP:LOOP
    FETCH cursor_tab INTO tab_name;
    IF done THEN
      IF @num_olddb>0 THEN
        IF @num_olddb_tab>=0 THEN
          SELECT COUNT(*) INTO @num_olddb_tab2 FROM information_schema.tables WHERE table_schema=old_dbname; 
          IF  @num_olddb_tab2 =0 THEN
            SET @stmt_dropdb=CONCAT("DROP DATABASE ",old_dbname,";");
            PREPARE stmt_dropdb FROM @stmt_dropdb;
            EXECUTE stmt_dropdb;
                        set msg=concat(msg,old_dbname,' is droped! -->');
            DEALLOCATE PREPARE stmt_dropdb;
          END IF;
        END IF;
      END IF;
      LEAVE TABLE_LOOP;
    END IF;
    
    SET @stmt_renamedb=CONCAT("RENAME TABLE ",old_dbname,".",tab_name," TO ",new_dbname,".",tab_name);
    PREPARE stmt_renamedb FROM @stmt_renamedb;
    EXECUTE stmt_renamedb;
    DEALLOCATE PREPARE stmt_renamedb;
     
  END LOOP;
  set msg=concat(msg,'rename db ',old_dbname,' to ',new_dbname,' is end');
  INSERT INTO manager_db.hz_log(start_time,end_time,log_text) VALUES(@start_time,NOW(),msg);
  DELETE FROM manager_db.hz_log WHERE start_time <= CURDATE() - INTERVAL 10 DAY;

  CLOSE cursor_tab;
END//
DELIMITER ;
复制