mysql create table select复制表结构和数据实例讲解

比如现在有一张表，我们要将该表复制一份，以备以后使用，那么如何使用mysql语句来实现呢？其实我们可以直接使用create ... select ... from语句来实现，具体实现方法请看下面实例。

我们先来创建一张Topic表，创建Topic表的SQL语句如下：

mysql> CREATE TABLE Topic(
    ->    TopicID     SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    Name        VARCHAR(50) NOT NULL,
    ->    InStock     SMALLINT UNSIGNED NOT NULL,
    ->    OnOrder     SMALLINT UNSIGNED NOT NULL,
    ->    Reserved    SMALLINT UNSIGNED NOT NULL,
    ->    Department  ENUM(\'Classical\', \'Popular\') NOT NULL,
    ->    Category    VARCHAR(20) NOT NULL,
    ->    RowUpdate   TIMESTAMP NOT NULL
    -> );

向Topic表中插入数据：

mysql> INSERT INTO Topic (Name,          InStock, OnOrder, Reserved, Department,   Category) VALUES
    ->                   (\'Java\',          10,      5,       3,        \'Popular\',    \'Rock\'),
    ->                   (\' \',    10,      5,       3,        \'Classical\',  \'Opera\'),
    ->                   (\'C Sharp\',       17,      4,       1,        \'Popular\',    \'Jazz\'),
    ->                   (\'C\',             9,       4,       2,        \'Classical\',  \'Dance\'),
    ->                   (\'C++\',           24,      2,       5,        \'Classical\',  \'General\'),
    ->                   (\'Perl\',          16,      6,       8,        \'Classical\',  \'Vocal\'),
    ->                   (\'Python\',        2,       25,      6,        \'Popular\',    \'Blues\'),
    ->                   (\'Php\',           32,      3,       10,       \'Popular\',    \'Jazz\'),
    ->                   (\'ASP.net\',       12,      15,      13,       \'Popular\',    \'Country\'),
    ->                   (\'VB.net\',        5,       20,      10,       \'Popular\',    \'New Age\'),
    ->                   (\'VC.net\',        24,      11,      14,       \'Popular\',    \'New Age\'),
    ->                   (\'UML\',           42,      17,      17,       \'Classical\',  \'General\'),
    ->                   (\'www.java2s.com\',25,      44,      28,       \'Classical\',  \'Dance\'),
    ->                   (\'Oracle\',        32,      15,      12,       \'Classical\',  \'General\'),
    ->                   (\'Pl/SQL\',        20,      10,      5,        \'Classical\',  \'Opera\'),
    ->                   (\'Sql Server\',    23,      12,      8,        \'Classical\',  \'General\');
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

现在我们要将这张表复制一份，具体操作如下：

mysql> CREATE TABLE Topic2
    -> (
    ->    TopicID     SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    Name        VARCHAR(50) NOT NULL,
    ->    InStock     SMALLINT UNSIGNED NOT NULL,
    ->    OnOrder     SMALLINT UNSIGNED NOT NULL,
    ->    Reserved    SMALLINT UNSIGNED NOT NULL,
    ->    Department  ENUM(\'Classical\', \'Popular\') NOT NULL,
    ->    Category    VARCHAR(20) NOT NULL,
    ->    RowUpdate   TIMESTAMP NOT NULL
    -> )
    -> SELECT *
    -> FROM Topic

这样表Topic2和Topic表不仅拥有相同的表结构，表数据也是一样的了。

例外，如果我们只需要复制表结构，不需要复制数据，也可以使用create like来实现：

create table a like users;