如何将多个MySQL表合并为一个？

Question

我有一个包含121个不同结构的Mysql数据库表，我需要将所有这些表合并成1个表。

这些表中有3个字段(email、base_name和location)在所有表中都相同，但某些表中的其他字段是相同的，而在其他表中则不同。

是否存在合并表格并保留所有字段(即使存在空字段也无妨)的障碍？

- nmarti

---

通过合并，您的意思是根据这121个表的结构创建一个新表，并将它们的数据插入到新创建的表中？ - Shef

我不认为这会变得可行，但出于兴趣，这个核弹的用例是什么？ - Shef

Answer 1

首先，您需要手动创建一个表格，其中包含所有表中唯一列的信息，并在电子邮件、基本名称和位置上设置主键。

这种方法的主要问题是，由主键匹配的行，如果包含不同的数据，最近的更新将覆盖这些行。

您可以为每个表生成插入语句，以下查询将为您提供列的列表，以便通过比较表1和新表进行选择，并更新列（如果不为空）。

（更改下面的表号以生成每个表的数据）

SELECT GROUP_CONCAT(NVL(b.COLUMN_NAME,CONCAT('NULL AS ',a.column_name))) as sel_cols
  FROM INFORMATION_SCHEMA.COLUMNS a
  LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON a.column_name = b.column_name and b.table_name='table1' and b.table_schema = b.table_schema
  WHERE a.table_name = 'new_table' AND b.table_schema = database()

获取要更新的列列表

SELECT GROUP_CONCAT(CASE WHEN b.column_name IS NOT NULL THEN CONCAT(b.column_name,'=VALUES(',b.column_name,')') ELSE END) as upd_cols
  FROM INFORMATION_SCHEMA.COLUMNS a
  LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON a.column_name = b.column_name and b.table_name='table1' and b.table_schema = b.table_schema
  WHERE a.table_name = 'new_table' AND b.table_schema = database()

e.g:

col1, col2, NULL as col3

col1 = VALUES(col1), col2 = VALUES(col2)

现在将列列表和表名粘贴到插入语句中...

INSERT INTO new_table (select col1,col2,NULL as col3 FROM table1)
ON DUPLICATE KEY UPDATE col1 = VALUES(col1), col2 = VALUES(col2)

更改SQL以生成所需的所有表格的确切语句应该很容易。也许可以添加一个列来显示覆盖发生的事实以及原始列来自哪里，这样您就可以手动解决冲突。

- Kevin Burton

---

这将只创建一个空表。你的意思是 insert into newtable select .... union ....。 - Johan

您可以通过在CREATE TABLE语句末尾添加SELECT语句来从另一个表中创建一个表：请参见http://dev.mysql.com/doc/refman/5.1/en/create-table.html，这是有效的DDL。 - Kevin Burton

Answer 2

当然可以合并表格，这甚至不难。
以下是三个表格的示例，请确保在union中强制所有选择输出相同数量的列：

  SELECT 'table1' as tablename
         , email, basename, location, field1, field2, null, null
  FROM table1
UNION
  SELECT 'table2' as tablename
         , email, basename, location, field1, field2, field3, null
  FROM table2
UNION
  SELECT 'table3' as tablename
         , email, basename, location, field1, null, null, null
  FROM table3
UNION
  ....

- Johan

Answer 3

我不确定我是否理解得正确，但我猜你是想要连接表而不是合并它们（如果我误解了，对不起）。我给你提供一个左连接的例子，但是如果你想要保留每个表中的每条记录，你需要一个全外连接（你需要在 MySQL 中模拟它，因为它没有实现）。

CREATE TABLE table_name AS (

SELECT * 
  FROM table1 t1
  LEFT JOIN table2 t2 ON t1.email=t2.email AND t1.base_name=t2.base_name AND t1.location=t2.location
  ...
  LEFT JOIN tableN tN ON tN-1.email=tN.email AND tN-1.base_name=tN.base_name AND tN-1.location=tN.location

)

如果你想模拟一个完全外连接，你应该像这样使用 union：

SELECT * 
  FROM table1 t1
  LEFT JOIN table2 t2 ON t1.email=t2.email AND t1.base_name=t2.base_name AND t1.location=t2.location
  ...
  LEFT JOIN tableN tN ON tN-1.email=tN.email AND tN-1.base_name=tN.base_name AND tN-1.location=tN.location

UNION

SELECT * 
  FROM tableN tN
  LEFT JOIN tableN-1 tN-1 ON tN.email=tN-1.email AND tN.base_name=tN-1.base_name AND tN-1.location=tN.location
  ...
  LEFT JOIN table1 t1 ON t2.email=t1.email AND t2.base_name=t1.base_name AND t1.location=t2.location

- Packet Tracer