这是我在Windows上写的重命名数据库的批处理脚本:

@echo off
set olddb=olddbname
set newdb=newdbname
SET count=1
SET act=mysql -uroot -e "select table_name from information_schema.tables where table_schema='%olddb%'"
mysql -uroot -e "create database %newdb%"
echo %act%
 FOR /f "tokens=*" %%G IN ('%act%') DO (
  REM echo %count%:%%G
  echo mysql -uroot -e "RENAME TABLE %olddb%.%%G to %newdb%.%%G"
  mysql -uroot -e "RENAME TABLE %olddb%.%%G to %newdb%.%%G"
  set /a count+=1
 )
mysql -uroot -e "drop database %olddb%"

下面是一行Bash代码片段，用于将所有表从一个模式移动到另一个模式:

history -d $((HISTCMD-1)) && mysql -udb_user -p'db_password' -Dold_schema -ABNnqre'SHOW TABLES;' | sed -e's/.*/RENAME TABLE old_schema.`&` TO new_schema.`&`;/' | mysql -udb_user -p'db_password' -Dnew_schema

开始时的history命令只是确保包含密码的MySQL命令不会保存到shell历史记录中。

确保db_user对旧模式具有读/写/删除权限，对新模式具有读/写/创建权限。

这里的大多数答案都是错误的，原因有两个:

不能只使用RENAME TABLE，因为可能存在视图和触发器。如果有触发器，RENAME TABLE将失败 如果你想“快速”(如问题中要求的)重命名一个大数据库，你不能使用mysqldump

Percona有一篇关于如何做到这一点的博客文章: https://www.percona.com/blog/2013/12/24/renaming-database-schema-mysql/

以及由西蒙·R·琼斯(Simon R Jones)发布的脚本(制作的?)我修复了在脚本中发现的一个错误。你可以在这里看到:

https://gist.github.com/ryantm/76944318b0473ff25993ef2a7186213d

以下是它的副本:

#!/bin/bash
# Copyright 2013 Percona LLC and/or its affiliates
# @see https://www.percona.com/blog/2013/12/24/renaming-database-schema-mysql/
set -e
if [ -z "$3" ]; then
    echo "rename_db <server> <database> <new_database>"
    exit 1
fi
db_exists=`mysql -h $1 -e "show databases like '$3'" -sss`
if [ -n "$db_exists" ]; then
    echo "ERROR: New database already exists $3"
    exit 1
fi
TIMESTAMP=`date +%s`
character_set=`mysql -h $1 -e "SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = '$2'" -sss`
TABLES=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='BASE TABLE'" -sss`
STATUS=$?
if [ "$STATUS" != 0 ] || [ -z "$TABLES" ]; then
    echo "Error retrieving tables from $2"
    exit 1
fi
echo "create database $3 DEFAULT CHARACTER SET $character_set"
mysql -h $1 -e "create database $3 DEFAULT CHARACTER SET $character_set"
TRIGGERS=`mysql -h $1 $2 -e "show triggers\G" | grep Trigger: | awk '{print $2}'`
VIEWS=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='VIEW'" -sss`
if [ -n "$VIEWS" ]; then
    mysqldump -h $1 $2 $VIEWS > /tmp/${2}_views${TIMESTAMP}.dump
fi
mysqldump -h $1 $2 -d -t -R -E > /tmp/${2}_triggers${TIMESTAMP}.dump
for TRIGGER in $TRIGGERS; do
    echo "drop trigger $TRIGGER"
    mysql -h $1 $2 -e "drop trigger $TRIGGER"
done
for TABLE in $TABLES; do
    echo "rename table $2.$TABLE to $3.$TABLE"
    mysql -h $1 $2 -e "SET FOREIGN_KEY_CHECKS=0; rename table $2.$TABLE to $3.$TABLE"
done
if [ -n "$VIEWS" ]; then
    echo "loading views"
    mysql -h $1 $3 < /tmp/${2}_views${TIMESTAMP}.dump
fi
echo "loading triggers, routines and events"
mysql -h $1 $3 < /tmp/${2}_triggers${TIMESTAMP}.dump
TABLES=`mysql -h $1 -e "select TABLE_NAME from information_schema.tables where table_schema='$2' and TABLE_TYPE='BASE TABLE'" -sss`
if [ -z "$TABLES" ]; then
    echo "Dropping database $2"
    mysql -h $1 $2 -e "drop database $2"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.columns_priv where db='$2'" -sss` -gt 0 ]; then
    COLUMNS_PRIV="    UPDATE mysql.columns_priv set db='$3' WHERE db='$2';"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.procs_priv where db='$2'" -sss` -gt 0 ]; then
    PROCS_PRIV="    UPDATE mysql.procs_priv set db='$3' WHERE db='$2';"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.tables_priv where db='$2'" -sss` -gt 0 ]; then
    TABLES_PRIV="    UPDATE mysql.tables_priv set db='$3' WHERE db='$2';"
fi
if [ `mysql -h $1 -e "select count(*) from mysql.db where db='$2'" -sss` -gt 0 ]; then
    DB_PRIV="    UPDATE mysql.db set db='$3' WHERE db='$2';"
fi
if [ -n "$COLUMNS_PRIV" ] || [ -n "$PROCS_PRIV" ] || [ -n "$TABLES_PRIV" ] || [ -n "$DB_PRIV" ]; then
    echo "IF YOU WANT TO RENAME the GRANTS YOU NEED TO RUN ALL OUTPUT BELOW:"
    if [ -n "$COLUMNS_PRIV" ]; then echo "$COLUMNS_PRIV"; fi
    if [ -n "$PROCS_PRIV" ]; then echo "$PROCS_PRIV"; fi
    if [ -n "$TABLES_PRIV" ]; then echo "$TABLES_PRIV"; fi
    if [ -n "$DB_PRIV" ]; then echo "$DB_PRIV"; fi
    echo "    flush privileges;"
fi

将它保存到一个名为rename_db的文件中，并使用chmod +x rename_db使脚本可执行，然后像。/rename_db localhost old_db new_db那样使用它

我最近才发现了一个很好的方法，使用MyISAM和InnoDB，而且非常快:

RENAME TABLE old_db.table TO new_db.table;

我不记得我在哪里读到的，但功劳归于别人，而不是我。

我使用以下方法重命名数据库

使用mysqldump或任何数据库工具备份文件，如heidiSQL,mysql管理员等 在某些文本编辑器中打开备份(例如backupfile.sql)文件。 搜索并替换数据库名称并保存文件。

4.恢复已编辑的sql文件

在phpmyadmin中，您可以轻松地重命名数据库

select database 

  goto operations tab

  in that rename Database to :

  type your new database name and click go

要求删除旧表和重新加载表数据单击确定

数据库被重命名