mysql表复制

（1）新创建一个数据库db3

mysql> create database db3 charset utf8;
Query OK, 1 row affected (0.00 sec)

（2）使用db3

mysql> use db3;
Database changed

#这是上个创建的db2数据库中的a1表
mysql> select * from db2.a1;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | mjj   |   18 |
|    2 | wusir |   28 |
+------+-------+------+

（3）复制db2.a1的表结构和记录

# 这就是复制表的操作（既复制了表结构，又复制了记录）
mysql> create table b1 select * from db2.a1;
Query OK, 2 rows affected (0.03 sec)

（4）查看db3.b1中的数据和表结构

#再去查看db3文件夹下的b1表发现 跟db2文件下的a1表数据一样
mysql> select * from db3.b1;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | mjj   |   18 |
|    2 | wusir |   28 |
+------+-------+------+
2 rows in set (0.00 sec)

ps1：如果只要表结构，不要记录

#在db2数据库下新创建一个b2表，给一个where条件，条件要求不成立，条件为false，只拷贝表结构
mysql> create table b2 select * from db2.a1 where 1>5;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看表结构：

# 查看表结构
mysql> desc b2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

#查看表结构中的数据，发现是空数据
mysql> select * from b2;
Empty set (0.00 sec)

ps2:还有一种做法，使用like(只拷贝表结构，不拷贝记录)

mysql> create table b3 like db2.a1;
Query OK, 0 rows affected (0.01 sec)

mysql> desc b3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> select * from db3.b3;
Empty set (0.00 sec)