合并两个表在MySQL和自动填充丢失的列。

I have a little problem, I would like to merge 2 tables (not all the columns are the same), and to put NULL where the columns are different...

我有一个小问题，我想合并两个表(不是所有的列都是相同的)，并在列不同的地方放置NULL…

It's quite hard to explain this with words, so here's an example of what I'm trying to do :

很难用语言来解释，这是我想做的一个例子

If I had this table :

如果我有这张桌子:

+------+------+------+------+
| col1 | col2 | col3 | type |
+------+------+------+------+
| 1    | NULL | 1    | A    |
| NULL | 1    | NULL | A    |
+------+------+------+------+

And this table :

和这个表:

+------+------+------+------+
| col2 | col3 | col4 | type |
+------+------+------+------+
| 1    | NULL | 1    | B    |
| NULL | 1    | NULL | B    |
+------+------+------+------+

I would like to create a table like this one :

我想创建一个这样的表:

+------+------+------+------+------+
| col1 | col2 | col3 | col4 | type |
+------+------+------+------+------+
| 1    | NULL | 1    | NULL | A    |
| NULL | 1    | NULL | NULL | A    |
| NULL | 1    | NULL | 1    | B    |
| NULL | NULL | 1    | NULL | B    |
+------+------+------+------+------+

Is it possible ? ^.^

是可能的吗?^ ^。

Edit : In the real tables, I have around 40 columns for the first table and 80 for the second one

编辑:在实际的表中，我有大约40列用于第一个表，80列用于第二个表。

1 个解决方案

#1

Sure, just Union the record sets together with nulls in the columns that don't exist in each table:

当然，只需将记录集与在每个表中不存在的列中的空值组合在一起:

insert into YourMergeTable
select col1,col2,col3,null col4,type from YourFirstTable
union all
select null col1,col2,col3,col4,type from YourSecondTable

http://www.sqlfiddle.com/#!2/16110/1

http://www.sqlfiddle.com/ ! 2/16110/1

#1