用create table as select .......,但索引不会复制。
mysql> select * from table1;
+------+-------+
| id | name1 |
+------+-------+
| 1 | aaa |
| 1 | bbb |
+------+-------+
2 rows in set (0.00 sec)
mysql> select * from table2;
+------+-------+
| id | name2 |
+------+-------+
| 2 | ccc |
| 2 | ddd |
+------+-------+
2 rows in set (0.00 sec)
mysql> create table table3 AS (select a.id,a.name1,b.id as id2,b.name2 from table1 a join table2 b);
Query OK, 4 rows affected (0.10 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from table3;
+------+-------+------+-------+
| id | name1 | id2 | name2 |
+------+-------+------+-------+
| 1 | aaa | 2 | ccc |
| 1 | bbb | 2 | ccc |
| 1 | aaa | 2 | ddd |
| 1 | bbb | 2 | ddd |
+------+-------+------+-------+
4 rows in set (0.00 sec)