MySQL-克隆表

MySQL-克隆表 (MySQL - Clone Tables)

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.

在某些情况下，您需要精确的表副本和CREATE TABLE ... SELECT不适合您的目的，因为该副本必须包含相同的索引，默认值等。

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.

  使用SHOW CREATE TABLE获得一个CREATE TABLE语句，该语句指定源表的结构，索引和所有内容。

• 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.

  可选地，如果您还需要复制表内容，也可以发出INSERT INTO ... SELECT语句。

例 (Example)

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

试用以下示例为tutorials_tbl创建一个克隆表。

Step 1 − Get the complete structure about the table.

步骤1-获取有关表的完整结构。

mysql> SHOW CREATE TABLE tutorials_tbl \G;
*************************** 1. row ***************************
      Table: tutorials_tbl
Create Table: CREATE TABLE `tutorials_tbl` (
   `tutorial_id` int(11) NOT NULL auto_increment,
   `tutorial_title` varchar(100) NOT NULL default '',
   `tutorial_author` varchar(40) NOT NULL default '',
   `submission_date` date default NULL,
   PRIMARY KEY  (`tutorial_id`),
   UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
) TYPE = MyISAM
1 row in set (0.00 sec)

ERROR:
No query specified

Step 2 − Rename this table and create another table.

步骤2-重命名该表并创建另一个表。

mysql> CREATE TABLE clone_tbl (
   -> tutorial_id int(11) NOT NULL auto_increment,
   -> tutorial_title varchar(100) NOT NULL default '',
   -> tutorial_author varchar(40) NOT NULL default '',
   -> submission_date date default NULL,
   -> PRIMARY KEY  (tutorial_id),
   -> UNIQUE KEY AUTHOR_INDEX (tutorial_author)
-> ) TYPE = MyISAM;
Query OK, 0 rows affected (1.80 sec)

Step 3 − 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.

步骤3-执行步骤2之后，您将在数据库中创建一个克隆表。 如果要从旧表中复制数据，则可以使用INSERT INTO ... SELECT语句来完成。

mysql> INSERT INTO clone_tbl (tutorial_id,
   -> tutorial_title,
   -> tutorial_author,
   -> submission_date)
   
   -> SELECT tutorial_id,tutorial_title,
   -> tutorial_author,submission_date
   -> FROM tutorials_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

最后，您将拥有一个想要的精确克隆表。

翻译自: https://www.tutorialspoint.com/mysql/mysql-clone-tables.htm