大量结构相同数据库表的合并

Question

这些数据库表都有类似的格式，例如: Prefix_AAA_Postfix, Prefix_BBB_Postfix, Prefix_CCC_Postfix, ...
我的目的是合并这些表成为一个新表，或者创建一个View。这样的任务应当怎样完成? 数据库表非常多(>1000), 不太可能用Union去一个一个合并，有什么可行方法?

Answer 1

mysql> CREATE TABLE t1 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20));
mysql> CREATE TABLE t2 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20));
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (
    ->    a INT NOT NULL AUTO_INCREMENT,
    ->    message CHAR(20), INDEX(a))
    ->    TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
注意，一个列在MERGEN表中被索引，但没有被宣告为一个PRIMARY KEY，因为它是在更重要的MyISAM表中。这是必要的，因为MERGE表在更重要的表中的设置上强制非唯一性。 创建MERGE表之后，你可以发出把一组表当作一体来操作的查询： mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table   |
| 3 | t1      |
| 1 | Testing |
| 2 | table   |
| 3 | t2      |
+---+---------+

Answer 2

15.3. MERGE存储引擎
15.3.1. MERGE表的问题MERGE存储引擎，也被认识为MRG_MyISAM引擎，是一个相同的可以被当作一个来用的MyISAM表的集合。“相同”意味着所有表同样的列和索引信息。你不能合并列被以不同顺序列于其中的表，没有恰好同样列的表，或有不同顺序索引的表。而且，任何或者所有的表可以用myisampack来压缩。请参阅8.2节，“myisampack — 生成压缩的只读MyISAM表”。表选项的差异，
详细情况参照参考手册15.3

Answer 3

实际上还是UNION 操作，用SP取得表名，字符串累加生成SQL语句再执行

Answer 4

简单示例，MYSQL5以上：
DELIMITER $$USE `ee`$$DROP PROCEDURE IF EXISTS `ctor`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `ctor`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE dd2,dd3 VARCHAR(5000);
DECLARE dd1 VARCHAR(5000);
DECLARE DD CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='ee' AND TABLE_NAME='cmp' ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
SET dd3='';
OPEN dd;
FETCH dd INTO dd2;
WHILE done=0 DO
SET dd1='select cmp_name,';
IF LOWER(dd2)<>'cmp_name' THEN 
SET dd3=CONCAT(dd3,dd1,dd2,' from cmp union all ');
END IF;
FETCH dd INTO dd2;
END WHILE;
SELECT LEFT(dd3,LENGTH(dd3)-10);
SET @dd4=LEFT(dd3,LENGTH(dd3)-10);
PREPARE stml FROM @dd4;
EXECUTE stml;
END$$DELIMITER ;

Answer 5

Thank you! 我研究一下，存储过程不太熟

Answer 6

建议你可以用简单一点的方法方案一：
在MYSQL中执行 show tables ; 
得到所有表的列表，然后复制到EXCEL中
利用EXCEL中的公式直接生成一个 UNION ALL 的语句。 可以基于这个语句创建VIEW方案二：
前面步骤相同，在EXCEL生成列表。然后使用 #1楼 zuoxingyu所建议的MERGE存储引擎。（但需要看你的数据是否合适，有所限制。）

Answer 7

"利用EXCEL中的公式直接生成一个 UNION ALL 的语句" 这个我不太理解。 能解释下吗？

Answer 8

当SHOW TABLES之后
你会得到一个这样的列表。Prefix_AAA_Postfix
Prefix_BBB_Postfix
Prefix_CCC_Postfix
Prefix_DDD_Postfix
Prefix_EEE_Postfix
Prefix_FFF_Postfix
Prefix_GGG_Postfix
Prefix_HHH_Postfix
Prefix_III_Postfix
Prefix_JJJ_Postfix
Prefix_KKK_Postfix
Prefix_LLL_Postfix
Prefix_MMM_Postfix
Prefix_NNN_Postfix
Prefix_OOO_Postfix
Prefix_PPP_Postfix
Prefix_QQQ_Postfix
Prefix_RRR_Postfix
把这个贴到EXCEL的A列中。然后B1写入公式 ="select * from "&A1&" union all"
向下填充，得到B列为select * from Prefix_AAA_Postfix union all
select * from Prefix_BBB_Postfix union all
select * from Prefix_CCC_Postfix union all
select * from Prefix_DDD_Postfix union all
select * from Prefix_EEE_Postfix union all
select * from Prefix_FFF_Postfix union all
select * from Prefix_GGG_Postfix union all
select * from Prefix_HHH_Postfix union all
select * from Prefix_III_Postfix union all
select * from Prefix_JJJ_Postfix union all
select * from Prefix_KKK_Postfix union all
select * from Prefix_LLL_Postfix union all
select * from Prefix_MMM_Postfix union all
select * from Prefix_NNN_Postfix union all
select * from Prefix_OOO_Postfix union all
select * from Prefix_PPP_Postfix union all
select * from Prefix_QQQ_Postfix union all
select * from Prefix_RRR_Postfix union all
去掉最后一个union all， 然后加上create view xxx as 就行了。