Mysqli 简明教程

MySQLi - Clone Tables

可能存在需要一张表的精确副本的情况，而对于 CREATE TABLE …​ SELECT 来说，因为副本必须包含相同的索引、默认值等，它并不适合你的目的。

There may be a situation when you need an exact copy of a table and CREATE TABLE …​ SELECT doesn’t suit your purposes because the copy must include the same indexes, default values, and so forth.

你可以执行以下步骤来处理此类情况−

You can handle this situation by following steps −

  1. Use SHOW CREATE TABLE to get a CREATE TABLE statement that specifies the source table’s structure, indexes and all.

  2. Modify the statement to change the table name to that of the clone table and execute the statement. This way, you will have exact clone table.

  3. Optionally, if you need the table contents copied as well, issue an INSERT INTO …​ SELECT statement, too.

Example

尝试以下示例为 tutorials_inf 创建一个克隆表。

Try out the following example to create a clone table for tutorials_inf.

Step 1

获取表的完整结构。

Get complete structure about table.

mysql> SHOW CREATE TABLE tutorials_inf \G;
*************************** 1. row ***************************
       Table: tutorials_inf
Create Table: CREATE TABLE `tutorials_inf` (
   `id` int(11) NOT NULL,
   `name` varchar(20) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `AUTHOR_INDEX` (`name`),
   UNIQUE KEY `NAME_INDEX` (`name`),
   KEY `id` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = latin1
1 row in set (0.05 sec)

ERROR: No query specified

Step 2

重命名此表并创建另一张表。

Rename this table and create another table.

mysql> CREATE TABLE tutorials_clone(
   → id int(11) NOT NULL,
   → name varchar(20) NOT NULL,
   → PRIMARY KEY (id),
   → UNIQUE KEY AUTHOR_INDEX (name),
   → UNIQUE KEY NAME_INDEX (name),
   →  KEY id (id));
Query OK, 0 rows affected (1.80 sec)

Step 3

在执行步骤 2 后，你将在自己的数据库中创建一张克隆表。如果你想从旧表中复制数据，可以使用 INSERT INTO…​ SELECT 语句来执行此操作。

After executing step 2, you will create a clone table in your database. If you want to copy data from old table then you can do it by using INSERT INTO…​ SELECT statement.

mysql> INSERT INTO tutorials_clone(id,name) SELECT id,name from tutorials_inf;
Query OK, 4 rows affected (0.19 sec)
Records: 4  Duplicates: 0  Warnings: 0

最后，你将获得自己想要的一个精确克隆表。

Finally, you will have exact clone table as you wanted to have.