Mysql 简明教程
MySQL - Clone Tables
可能存在这样的情况：你需要一张表格的准确副本，其中包含相同的列、属性、索引、默认值等。不用花时间使用 Create Table 语句创建现有表的完全相同版本，你可以不干扰原始表而克隆该表。
There may be a situation when you need an exact copy of a table with the same columns, attributes, indexes, default values and so forth. Instead of spending time on creating the exact same version of an existing table using the CREATE TABLE statement, you can clone the table without disturbing the original table.
在 SQL 允许用户使用 Cloning operation 创建现有表的准确副本以及其定义，这完全独立于原始表。因此，如果对克隆的表进行了任何更改，它们都不会反映在原始表中。此操作在测试过程中会派上用场，这时需要使用现有数据库表执行示例测试。
Cloning operation in SQL allows the user to create the exact copy of an existing table along with its definition, that is completely independent from the original table. Thus, if any changes are made to the cloned table, they will not be reflected in the original table. This operation comes in handy during testing processes, where there is a need to perform sample testing using the existing database tables.
在 MySQL RDBMS 中可以使用 SQL 执行三种类型的克隆；它们列在下面：
There are three types of cloning possible using SQL in MySQL RDBMS; they are listed below −
-
Simple Cloning: Creates a new table containing same records of existing table but void of any constraints or indexes etc.
-
Shallow Cloning: Creates a new empty table with the same table definition of an existing table.
-
Deep Cloning: Creates a new table and copies the table structure and data of an existing table to the new table.
Cloning Tables in MySQL
通过以下步骤处理此情况：
You can handle this situation by following the steps given below −
-
Use SHOW CREATE TABLE to get a CREATE TABLE statement that specifies the source table’s structure, indexes and all.
-
Modify the statement to change the table name to that of the clone table and execute the statement. This way, you will have the exact clone table.
-
Optionally, if you need the table contents copied as well, issue an INSERT INTO … SELECT statement, too.
Example
尝试以下示例，为 CUSTOMERS 创建一个克隆表。
Try out the following example to create a clone table for CUSTOMERS.
Step 1 − 首先，使用“CREATE TABLE”语句创建“CUSTOMERS”表。
Step 1 − First of all, create the CUSTOMERS using the CREATE TABLE statement.
CREATE TABLE CUSTOMERS (
ID INT AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);使用以下“INSERT INTO”语句向表中插入一些记录。
Insert some records into it using the following INSERT INTO statement.
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );现在，执行以下“SHOW CREATE TABLE”命令，以显示源表（CUSTOMERS）结构。
Now, execute the following SHOW CREATE TABLE command to display the source table’s (CUSTOMERS) structure.
SHOW CREATE TABLE CUSTOMERS;以下是“CUSTOMERS”表结构。
Following is the CUSTOMERS table structure.
Table: CUSTOMERS
Create Table: CREATE TABLE `customers` (
`ID` int NOT NULL AUTO_INCREMENT,
`NAME` varchar(20) NOT NULL,
`AGE` int NOT NULL,
`ADDRESS` char(25) DEFAULT NULL,
`SALARY` decimal(18,2) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ciStep 2 − 现在，复制以上源表结构，并将名称更改为 copyCUSTOMERS 。
Step 2 − Now, copy the above source table structure and just change the name to copyCUSTOMERS.
CREATE TABLE `copyCUSTOMERS` (
`ID` int NOT NULL AUTO_INCREMENT,
`NAME` varchar(20) NOT NULL,
`AGE` int NOT NULL,
`ADDRESS` char(25) DEFAULT NULL,
`SALARY` decimal(18,2) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;Step 3 − 在执行步骤 2 后，如果我们希望从源表复制数据，我们可以使用 INSERT INTO… SELECT 语句来执行。
Step 3 − After executing step 2, if we want to copy data from the source table then we can do it by using INSERT INTO… SELECT statement.
INSERT INTO copyCUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
SELECT ID,NAME,AGE,ADDRESS,SALARY FROM CUSTOMERS;让我们用以下语句验证克隆表“copyCUSTOMERS”是否具有与“CUSTOMERS”表相同记录：
Let us verify whether the clone table copyCUSTOMERS has the same records as CUSTOMERS table or not using the following query −
SELECT * FROM copyCUSTOMERS;正如我们观察输出，我们有一个和“CUSTOMERS”完全一样的克隆表。
As we observe the output, we have an exact clone table as CUSTOMERS.
但为了让该流程更简单，我们可以尝试使用 CREATE TABLE 和 INSERT INTO 语句执行简单克隆、浅表克隆或深表克隆。让我们在本文档中详细地逐个讨论，并提供适当的示例。
But to make this process simpler, we can try to perform Simple Cloning, Shallow Cloning or Deep Cloning using CREATE TABLE and INSERT INTO statements. Let us discuss them one by one in detail with suitable examples further in this tutorial.
Simple Cloning in MySQL
简单克隆意味着创建一个包含与现有表相同数据的新表。首先，使用 CREATE TABLE 语句创建一个新表。然后，使用 SELECT 语句将现有表中选定列的数据复制到新表。
Simple Cloning means making a new table that contains the same data as an existing one. First, a new table is created using the CREATE TABLE statement. Then, data from selected columns in the existing table is copied into the new table using a SELECT statement.
Syntax
以下是 MySQL RDBMS 中执行简单克隆的基本语法：
Following is the basic syntax to perform simple cloning in MySQL RDBMS −
CREATE TABLE new_table SELECT * FROM original_table;Example
为了执行简单的克隆操作，让我们首先使用以下查询创建一个名为 CUSTOMERS 的表：
To perform the simple cloning operation, let us first create a table named CUSTOMERS table using the below query −
CREATE TABLE CUSTOMERS (
ID INT AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);以下查询在上述创建的表中插入 7 条记录 −
The following query inserts 7 records into the above created table −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );要显示上面创建的表的记录，执行以下查询：
To display the records of the above created table, execute the following query −
SELECT * FROM CUSTOMERS;以下为 CUSTOMERS 表 −
Following is the CUSTOMERS table −
现在，使用以下查询，我们通过 cloning 创建一个新表 CUSTOMERScopy 客户表。
Now, with the following query, we are creating a new table CUSTOMERScopy by cloning the CUSTOMERS table.
CREATE TABLE CUSTOMERScopy SELECT * FROM CUSTOMERS;Shallow Cloning in MySQL
浅克隆操作只将现有表的结构复制到创建的新表中，但它不会复制任何数据。因此，我们最终得到一个新表，该表与现有表的结构相同，但为空。
The Shallow Cloning operation only copies the structure of the existing table into the new table created, but it doesn’t copy any of the data. So, we end up with a new empty table that has the same structure as the existing table.
Syntax
以下是在 MySQL RDBMS 中执行浅克隆的基本语法 -
Following is the basic syntax to perform shallow cloning in MySQL RDBMS −
CREATE TABLE testCUSTOMERS1 LIKE CUSTOMERS;Example
在以下查询中，我们通过克隆 CUSTOMERS 表来创建名为 CUSTOMERScopy1 的新表，从而执行浅克隆。
In the following query, we are performing shallow cloning by creating a new table named CUSTOMERScopy1 by cloning the CUSTOMERS table.
CREATE TABLE CUSTOMERScopy1 LIKE CUSTOMERS;Output
执行上述查询将产生以下输出：
Executing the query above will produce the following output −
Query OK, 0 rows affected (0.03 sec)Verification
要验证新表是否创建，我们可以使用以下 SELECT 查询：
To verify whether the new table is created or not, we can use the following SELECT query −
SELECT * FROM CUSTOMERScopy1;正如我们在输出中看到的，新表 CUSTOMERScopy1 已创建，但未复制任何数据。
As we can see in the output, the new table CUSTOMERScopy1 has been created with no data copied into it.
Empty set (0.00 sec)Deep Cloning in MySQL
深克隆操作是简单克隆和浅克隆的组合。它不仅复制现有表的结构，还复制其数据到新创建的表中。因此，新表将具有现有表的所有属性及其内容。
Deep Cloning operation is a combination of simple cloning and shallow cloning. It not only copies the structure of the existing table but also its data into the newly created table. Hence, the new table will have all the attributes of the existing table and also its contents.
由于它是浅克隆和简单克隆的组合，因此此类克隆将具有两个需要执行的不同查询：一个带有 CREATE TABLE 语句，另一个带有 INSERT INTO 语句。CREATE TABLE 语句将通过包括表的所有属性，创建新表；INSERT INTO 语句将数据从现有表插入到新表中。
Since it is a combination of shallow and simple cloning, this type of cloning will have two different queries to be executed: one with CREATE TABLE statement and one with INSERT INTO statement. The CREATE TABLE statement will create the new table by including all the attributes of existing table; and INSERT INTO statement will insert the data from existing table into new table.
Syntax
以下是在 MySQL RDBMS 中执行深克隆的基本语法 -
Following is the basic syntax to perform deep cloning in MySQL RDBMS −
CREATE TABLE new_table LIKE original_table;
INSERT INTO new_table SELECT * FROM original_table;Example
在以下查询中，我们通过克隆 CUSTOMERS 表来创建一个新表 CUSTOMERScopy2 ，即首先执行 shallow cloning 。
In the following query, we are creating a new table CUSTOMERScopy2 by cloning the CUSTOMERS table, i.e. perform shallow cloning first.
CREATE TABLE CUSTOMERScopy2 LIKE CUSTOMERS;现在，使用以下查询，我们将 CUSTOMERS 表中的数据插入新表 CUSTOMERScopy2 中，即执行 simple cloning 。
Now using the following query, we are inserting data from CUSTOMERS table into new table CUSTOMERScopy2, i.e. performing simple cloning.
INSERT INTO CUSTOMERScopy2 SELECT * FROM CUSTOMERS;Output
执行上述查询将产生以下输出：
Executing the query above will produce the following output −
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0Verification
要验证新表是否创建，其中包含所有现有数据，我们可以使用以下 SELECT 查询：
To verify whether the new table is created or not with all the data present in it, we can use the following SELECT query −
SELECT * FROM CUSTOMERScopy2;正如我们观察到的 ouptut 一样，CUSTOMERScopy2 表具有与 CUSTOMERS 表相同结构和数据。
As we observe the ouptut, the CUSTOMERScopy2 table has the same structure and data as CUSTOMERS table.
Cloning a table Using a Client Program
除了使用 MySQL 查询来克隆现有表之外，您还可以使用客户端程序对表执行克隆操作。
Besides using MySQL queries to clone an existing table, you can also perform the cloning operation on a table using a client program.