問題描述
MySQL 手冊位于 MySQL 涵蓋了這一點.
The MySQL manual at MySQL covers this.
通常我只是轉儲數據庫并使用新名稱重新導入它.對于非常大的數據庫，這不是一個選項.顯然 RENAME {DATABASE |SCHEMA} db_name TO new_db_name; 做壞事，只存在于少數版本中，總體來說是個壞主意.
Usually I just dump the database and reimport it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name; does bad things, exist only in a handful of versions, and is a bad idea overall.
這需要與 InnoDB 一起使用，它存儲的東西與 MyISAM.
This needs to work with InnoDB, which stores things very differently than MyISAM.
推薦答案
對于 InnoDB，以下似乎有效:創建新的空數據庫，然后依次將每個表重命名為新數據庫:
For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:
RENAME TABLE old_db.table TO new_db.table;
之后您需要調整權限.
對于在 shell 中編寫腳本，您可以使用以下任一方法:
For scripting in a shell, you can use either of the following:
mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \
do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done
或
for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;
<小時>
注意事項:
Notes:
- 選項
-p和密碼之間沒有空格.如果您的數據庫沒有密碼，請刪除-u username -ppassword部分. 如果某個表有觸發器，則無法使用上述方法將其移動到另一個數據庫(將導致
Trigger in wrong schema錯誤).如果是這種情況，請使用傳統方式克隆數據庫，然后刪除舊數據庫:
- There is no space between the option
-pand the password. If your database has no password, remove the-u username -ppasswordpart. If some table has a trigger, it cannot be moved to another database using above method (will result
Trigger in wrong schemaerror). If that is the case, use a traditional way to clone a database and then drop the old one:
mysqldump old_db |mysql new_db
如果你有存儲過程，你可以在之后復制它們:
If you have stored procedures, you can copy them afterwards:
mysqldump -R old_db |mysql new_db
這篇關于如何快速重命名 MySQL 數據庫(更改架構名稱)?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！