创建测试表test:mysql> create table test(
-> id int primary key auto_increment,
-> name varchar(32)
-> );
Query OK, 0 rows affected (0.16 sec)
插入几条数据后复制test到test2中mysql> create table test2 select * from test;
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test2;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
| 2 | test2 |
+----+-------+
2 rows in set (0.00 sec)
上面是复制了整张表,我们下面看复制表的一部分字段,相信一这样说就应该想到怎么做了
创建测试表test3mysql> create table test3(
-> id int primary key auto_increment,
-> name varchar(32),
-> age int,
-> birthday timestamp default now()
-> );
Query OK, 0 rows affected (0.06 sec)
插入几条数据后开始复制test3到test4中mysql> create table test4 select id,name,birthday from test3;
Query OK, 7 rows affected (0.06 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from test4;
+----+------+---------------------+
| id | name | birthday |
+----+------+---------------------+
| 1 | test | 2010-12-30 23:16:08 |
| 2 | test | 2010-12-30 23:16:11 |
| 3 | test | 2010-12-30 23:16:12 |
| 4 | test | 2010-12-30 23:16:13 |
| 5 | test | 2010-12-30 23:16:14 |
| 6 | test | 2010-12-30 23:16:16 |
| 7 | test | 2010-12-30 23:16:19 |
+----+------+---------------------+
7 rows in set (0.00 sec)
从上面可以看出,如果是只创建含有约束条件的值的表副本就简单了直接where就Ok了mysql> create table test5 select * from test4 where id<3;
Query OK, 2 rows affected (0.11 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test5;
+----+------+---------------------+
| id | name | birthday |
+----+------+---------------------+
| 1 | test | 2010-12-30 23:16:08 |
| 2 | test | 2010-12-30 23:16:11 |
+----+------+---------------------+
2 rows in set (0.00 sec)
只复制表结构,不复制表数据
一:(取巧方法)mysql> create table test6 select * from test3 where 0=1;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test6;
Empty set (0.00 sec)
二:(MySQL提供了一个现成的语法)mysql> create table test7 like test6;
Query OK, 0 rows affected (0.06 sec)
mysql> select * from test7;
Empty set (0.00 sec)
mysql> desc test7;
+----------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------------------+-------+
| id | int(11) | NO | | 0 | |
| name | varchar(32) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| birthday | timestamp | NO | | 0000-00-00 00:00:00 | |
+----------+-------------+------+-----+---------------------+-------+
4 rows in set (0.00 sec)
三:(复制旧表部分结构,同时增加新字段)mysql> create table test8 (location varchar(32),position varchar(32)) select id,name,birthday from test7;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test8;
+----------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------------------+-------+
| location | varchar(32) | YES | | NULL | |
| position | varchar(32) | YES | | NULL | |
| id | int(11) | NO | | 0 | |
| name | varchar(32) | YES | | NULL | |
| birthday | timestamp | NO | | 0000-00-00 00:00:00 | |
+----------+-------------+------+-----+---------------------+-------+
5 rows in set (0.00 sec)