mysql - 如何创建一个由两个不同表组成的表

Question

我有两个结构不同的表。像这些:

表1:

+-----+--------+---------+
| id  |  name  |  color  |
+-----+--------+---------+
|  1  |  peter |   red   |
|  2  |  john  |   blue  |
|  3  |  jack  |   balck |
+-----+--------+---------+

表2:

+-----+--------+--------+
| id  |  name  |   age  |
+-----+--------+--------+
|  1  |  alvin |   12   |
|  2  |  caden |   34   |
|  3  |  bacon |   17   |
+-----+--------+--------+

现在我想创建一个具有以下结构的新表:(我想要这个结构)

+-----+--------+----------+-------+
| id  |  name  |   color  |  age  |
+-----+--------+----------+-------+
|  1  |  peter |   red    |       |  
|  2  |  john  |   blue   |       |
|  3  |  jack  |   black  |       |
|  4  |  alvin |          |   12  |
|  5  |  caden |          |   34  |
|  6  |  bacon |          |   17  |
+-----+--------+----------+-------+

我可以通过join来做到这一点，但有一个问题:重复的列。

这是我的尝试:

create table newtable as
select table1.id, table1.name, table1.color, table2.id, table2.name, table2.age
from table1 t1 inner join
     table2 t2
     on t1.id = t2.id;

但是输出将是这样的:

// This is not what I want ...
+-----+--------+----------+------+--------+-------+
| id  |  name  |   color  |  id  |  name  |  age  |
+-----+--------+----------+------+--------+-------+

我也可以使用union all，但在这种情况下，我需要color和age。不管怎样，有什么建议吗？

Answer 1

查看此link如果需要，可以从手册页查看。

create table table3
(
    id int auto_increment primary key,
    name varchar(50) not null,
    color varchar(50) null,
    age int null
);

选项1:

insert into table3 (name,color,age)
select t1.name, t1.color, null
from table1 t1
union
select t2.name, null, t2.age
from table1 t2

选项2:

insert into table3 (name,color,age)
select distinct inr.name,inr.color,inr.age
from
    (
    select t1.name, t1.color, null as age
    from table1 t1
    union
    select t2.name, null as color, t2.age
    from table1 t2
    ) inr

类似的事情。