MySQL的数据库不能重命名问题处理方法

对于MySQL数据库，并未提供修改库名的SQL方法，那如何修改库名呢？处理方法如下：

1、重命名旧库下边的所有表到新库：

create databases `test2`;

RENAME TABLE `test1`.`t_user` TO `test2`.`t_user`;

使用如下SQL导出上述SQL：

SELECT CONCAT('RENAME TABLE `test1`.`',TABLE_NAME,'` TO `test2`.`',TABLE_NAME,'`;') as statement 
from `information_schema`.`TABLES` where  TABLE_SCHEMA='test1';

使用shell脚本如下：

#!/bin/bash

HOST='localhost'
PORT='3306'
USER='root'
PASS='123456'
OLD_DB='tang'
NEW_DB='test'

if [ -z "$PASS" ]
then
     mysqlconn="mysql -h ${HOST} -P ${PORT} "
else
    mysqlconn="mysql -u\"${USER}\" -p\"${PASS}\"  -h ${HOST} -P ${PORT} "
fi

echo "$mysqlconn -e 'CREATE DATABASE if not exists ${NEW_DB};'"
eval "$mysqlconn -e 'CREATE DATABASE if not exists ${NEW_DB};'"

echo "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='${OLD_DB}' and TABLE_TYPE!='VIEW' "
params=$($mysqlconn -BN -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='${OLD_DB}' and TABLE_TYPE!='VIEW' ")
for name in $params; do
    echo "$mysqlconn -e 'RENAME TABLE ${OLD_DB}.${name} to ${NEW_DB}.${name};'"
        eval "$mysqlconn -e 'RENAME TABLE ${OLD_DB}.${name} to ${NEW_DB}.${name};'"
done;

echo "$mysqlconn -e 'DROP DATABASE ${OLD_DB};'"
eval "$mysqlconn -e 'DROP DATABASE ${OLD_DB};'"

echo "over!"

2、建新库，使用mysqldump工具导出老库数据并导入新库

mysqldump -u user -ppass -h host -P port --single-transaction old_db >old_db.sql
mysql  -u user -ppass -h host -P port -e "create database new_db"
mysql  -u user -ppass -h host -P port new_db < old_db.sql
mysql  -u user -ppass -h host -P port -e "drop database old_db"