将具有不同列数/名称的表的多个select *查询组合以导出到不同的数据库中

I have multiple tables which have multiple number of columns and field names. I want to select * data from these tables where there is a specific condition(then insert the data into a different database). For example, if I run below queries separately, each of them returns a different number of rows where number of columns and field's names are totally different. Of course I can export the results of below queries separately in three different files and then insert them into a different database, but my goal is to combine these results so that I can export the data in one file.

我有多个表有多个列和字段名称。我想从这些表中选择*具有特定条件的数据(然后将数据插入到不同的数据库中)。例如,如果我单独在下面运行查询,则每个查询返回不同数量的行,其中列数和字段名称完全不同。当然,我可以将以下查询的结果分别导出到三个不同的文件中,然后将它们插入到不同的数据库中,但我的目标是将这些结果组合起来,以便将数据导出到一个文件中。

Select * From table1 Where id>=500;
Select * From table2 Where id>=200;
Select * From table3 Where id>=1500;

Please note that Union all did not work in this case and MySQL said #1222 - The used SELECT statements have a different number of columns.

请注意,Union all在这种情况下不起作用,并且MySQL说#1222 - 使用的SELECT语句具有不同的列数。

Could you please let me know if you can help on this problem?

如果你可以帮忙解决这个问题,请告诉我吗?

3 个解决方案

#1

I think you're going wrong way, if you axport a union, than you will have problem importing that. Instead try to merge files, or export three tables separatly into one file, for example using tool MYSQLDUMP like this:

我认为你的方法是错误的,如果你是一个工会,那么你输入它会有问题。而是尝试合并文件,或者将三个表分别导出到一个文件中,例如使用工具MYSQLDUMP,如下所示:

mysqldump -u root -pyour_password your_database table1 >> /tmp/mysql_dump.sql
mysqldump -u root -pyour_password your_database table2 >> /tmp/mysql_dump.sql
mysqldump -u root -pyour_password your_database table3 >> /tmp/mysql_dump.sql

EDIT: You stated that you need a where condition - that is also possible, like that:

编辑:你说你需要一个where条件 - 这也是可能的,就像那样:

mysqldump -u root -pyour_password --where="id>=500" your_database table1 >> /tmp/mysql_dump.sql

#2

You can use something like this:

你可以使用这样的东西:

Select 
      ID as ID,
      '' as Name,
      '' as Something 
From table1 
where id>=500;

union all

Select 
     ID as ID, 
     Name as Name, 
     '' as Something 
from table2 
where id>=200;

union all

Select 
     ID as ID,
     '' as Name, 
     Something as Something 
 from table3 
 where id>=1500;

#3

If you have TableA with columns col1 and col2, and TableB with col3 and col for you do a union with the following:

如果TableA包含col1和col2列,而TableB包含col3和col,则使用以下内容进行联合:

Select col1,col2,col3=null,col4=null from TableA
Union all
Select col1=null,col2=null,col3,col4 from TableB

That will make the union statement work and allow you to export the data.

这将使union语句起作用并允许您导出数据。

#1

mysqldump -u root -pyour_password your_database table1 >> /tmp/mysql_dump.sql
mysqldump -u root -pyour_password your_database table2 >> /tmp/mysql_dump.sql
mysqldump -u root -pyour_password your_database table3 >> /tmp/mysql_dump.sql