如何使用SQL查询将MySQL数据库克隆到具有相同名称和相同表和行/内容的不同名称下

I know how to clone tables e.g.:

我知道如何克隆表格。

CREATE TABLE recipes_new LIKE production.recipes; 
INSERT recipes_new 
SELECT * FROM production.recipes;

But I don't know how to clone e.g. a database_old to database_new database with all the tables and rows from database_old.

但是我不知道如何克隆，例如database_old到database_new数据库，以及database_old的所有表和行。

So, only the name of the database will change. Everything else stays the same.

因此，只有数据库的名称会改变。其他一切都保持不变。

Right now I am cloning it by exporting the database in phpmyadmin ad then creating a new database and importing it to the new database.

现在我正在克隆它，方法是在phpmyadmin ad中导出数据库，然后创建一个新的数据库并将其导入新的数据库。

But I guess there must be a more efficient way of doing this task via SQL query like that one for cloning tables.

但是我想一定有一种更有效的方法可以通过SQL查询来完成这个任务，就像那个一样，用来克隆表。

IMPORTANT! It need to be done from SQL query window in phpmyadmin and not from a shell command line.

重要!它需要从phpmyadmin的SQL查询窗口中完成，而不是从shell命令行完成。

Thanks in advance for you suggestion how to do that.

提前谢谢你的建议。

4 个解决方案

#1

There is a alternate way for doing things such as:

有一种替代方法可以用来做以下事情:

First create a blank database:

首先创建一个空白数据库:

 CREATE DATABASE `destination` DEFAULT CHARACTER SET 
    latin1 COLLATE latin1_swedish_ci;

Then use the command show tables;

然后使用命令显示表;

 show source.tables;

and then run the command for each DB table (Optimized Create table and inserting rows) as:

然后对每个DB表(优化创建表和插入行)运行命令如下:

 create table destination.table select * from source.table;

and other way is using like command:

另一种方法是使用命令:

  create table destination.table like source.table

and then inserting rows;

然后插入行;

  insert into destination.table select * from source.table

#2

have you tried using MySQL Dump?

你试过使用MySQL转储吗?

$ mysqldump yourFirstDatabase -u user -ppassword > yourDatabase.sql
$ echo "create database yourSecondDatabase" | mysql -u user -ppassword
$ mysql yourSecondDatabase -u user -ppassword < yourDatabase.sql

#3

If phpMyAdmin is available for the database, you can clone it by following these steps:

如果phpMyAdmin可以用于数据库，您可以通过以下步骤进行克隆:

Select required database
选择所需的数据库
Click on the operation tab
单击“操作”选项卡
In the operation tab, go to the "copy database"-option and type your desired clone-db-name into the input field
在“操作”选项卡中，进入“复制数据库”选项，并在输入字段中键入所需的clone- dbname。
Select "Structure and data" (Depends on your requirement)
选择“结构和数据”(取决于您的需求)
Check the boxes, "CREATE DATABASE before copying" and "Add AUTO_INCREMENT value"
勾选“复制前创建数据库”和“添加AUTO_INCREMENT值”
Click "GO"
点击“去”

Tested with phpMyAdmin 4.2.13.3

测试用phpMyAdmin 4.2.13.3

#4

export your chosen database using phpmyadmin (export.sql)
使用phpmyadmin (export.sql)导出所选择的数据库
import it using terminal/cmd: mysql -u username -p databasename < export.sql
导入它使用终端/cmd: mysql -u用户名-p databasename < export.sql。

and get a cup of coffee...

喝杯咖啡……

#1