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)