MySQL复制表结构_数据

复制表内的数据用:
create table bak select * from table;

复制表的结构:
create table bak like table;
这个可以用于快速创建空表,这个空表的结构与原来的表也是一样的!

mysql> create table test(id INT auto_increment primary key, name VARCHAR(20))engine=Innodb;
Query OK, 0 rows affected (0.37 sec)

mysql> show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.13 sec)

mysql>
mysql> insert into test(name) values('kenthyzhang');
Query OK, 1 row affected (0.08 sec)

mysql> select * from test;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | kenthyzhang |
+----+-------------+
1 row in set (0.00 sec)

mysql> insert into test(name) values('jeremy');
Query OK, 1 row affected (0.03 sec)

mysql> select * from test;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | kenthyzhang |
|  2 | jeremy      |
+----+-------------+
2 rows in set (0.00 sec)

mysql> create table test1 select * from test;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table test2 like test;
Query OK, 0 rows affected (0.04 sec)

mysql> show create table test1;
+-------+------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                       |
+-------+------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table test2;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | kenthyzhang |
|  2 | jeremy      |
+----+-------------+
2 rows in set (0.00 sec)

mysql> select * from test1;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | kenthyzhang |
|  2 | jeremy      |
+----+-------------+
2 rows in set (0.00 sec)

mysql> select * from test2;
Empty set (0.00 sec)

mysql>