MySQL复制表

一、复制表结构及数据

首先查看原表的创建语句

(root@localhost) [dbt3]> show create table nation;    
+--------+-----------------------------------------------------
| Table  | Create                                                                           
+--------+-----------------------------------------------------

nation | CREATE TABLE `nation` (
  `n_nationkey` int(11) NOT NULL,
  `n_name` char(25) DEFAULT NULL,
  `n_regionkey` int(11) DEFAULT NULL,
  `n_comment` varchar(152) DEFAULT NULL,
  PRIMARY KEY (`n_nationkey`),
  KEY `i_n_regionkey` (`n_regionkey`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

然后用语句创建复制表

(root@localhost) [dbt3]> create table nation_a as (select * from nation);
Query OK, 25 rows affected (0.03 sec)
Records: 25  Duplicates: 0  Warnings: 0

查看复制的表

(root@localhost) [dbt3]> show create table nation_a;
+----------+---------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                   
+----------+---------------------------------------------------------------------------------------------------------+
| nation_a | CREATE TABLE `nation_a` (
  `n_nationkey` int(11) NOT NULL,
  `n_name` char(25) DEFAULT NULL,
  `n_regionkey` int(11) DEFAULT NULL,
  `n_comment` varchar(152) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

发现复制的表没有主键及索引；

二、只复制表结构

方法一：

(root@localhost) [dbt3]> create table nation_b as select * from nation where 1=2;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看创建表，没有主键及索引

(root@localhost) [dbt3]> show create table nation_b;
+----------+-----------------------------------------------------------------------------------------------+
| Table    | Create                                                                                                                                                    |
+----------+-----------------------------------------------------------------------------------------------+
| nation_b | CREATE TABLE `nation_b` (                                 
  `n_nationkey` int(11) NOT NULL,                                      
  `n_name` char(25) DEFAULT NULL,                                      
  `n_regionkey` int(11) DEFAULT NULL,                                  
  `n_comment` varchar(152) DEFAULT NULL                                
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |                               
+----------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)   

方法二：

(root@localhost) [dbt3]> create table nation_c like nation;
Query OK, 0 rows affected (0.02 sec)

查看创建表的结构，发现和原表一样

root@localhost) [dbt3]> show create table nation_c;         
+----------+---------------------------------------------------------------------------------------+
| Table    | Create                                                                                                                                          |
+----------+---------------------------------------------------------------------------------------+
| nation_c | CREATE TABLE `nation_c` (                      
  `n_nationkey` int(11) NOT NULL,                           
  `n_name` char(25) DEFAULT NULL,                           
  `n_regionkey` int(11) DEFAULT NULL,                       
  `n_comment` varchar(152) DEFAULT NULL,                    
  PRIMARY KEY (`n_nationkey`),                              
  KEY `i_n_regionkey` (`n_regionkey`)                       
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |      

复制数据

(root@localhost) [dbt3]> insert into nation_c select * from nation;
Query OK, 25 rows affected (0.01 sec)
Records: 25  Duplicates: 0  Warnings: 0