MariaDB列别名限制疑问及多表同列数据合并方案咨询

需求说明 #

从7个结构相似的Taxo系列表（TaxoDivision、TaxoSousDivision、TaxoClasse、TaxoOrdre、TaxoSousOrdre、TaxoFamille、TaxoGenre）中提取所有Titre列的数据，合并为单列返回，方便PHP逐一处理。

尝试过的查询方案及问题 #

1. 笛卡尔积查询 #

$sql = "
SELECT TD.Titre, TSD.Titre, TC.Titre, TOR.Titre, TSO.Titre, TF.Titre, TG.Titre
FROM TaxoDivision TD, TaxoSousDivision TSD, TaxoClasse TC, TaxoOrdre TOR,
TaxoSousOrdre TSO, TaxoFamille TF, TaxoGenre TG";

• PHP页面执行返回503 Service Unavailable错误，无PHP调试信息；
• phpMyAdmin执行返回7个同名Titre列的结果，不符合单列合并需求。

2. 列别名查询 #

SELECT TD.Titre AS Division, TSD.Titre AS SousDivision,
TC.Titre AS Classe, TOR.Titre AS Ordre, TSO.Titre SousOrdre,
TF.Titre AS Famille, TG.Titre AS Genre 
FROM TaxoDivision TD, TaxoSousDivision TSD, TaxoClasse TC, TaxoOrdre
TOR, TaxoSousOrdre TSO, TaxoFamille TF, TaxoGenre TG

• 直接执行出现#2006 - MySQL server has gone away错误；
• 仅设置5个列别名时查询正常。

疑问与需求 #

• MariaDB是否存在列别名数量限制？
• 如何用简洁SQL将多个无关联表的同列数据合并为单列结果？

现有PHP实现（已满足需求） #

$names = [];
$tables = [];
$tables[] = "Division";
$tables[] = "SousDivision";
$tables[] = "Classe";
$tables[] = "Ordre";
$tables[] = "SousOrdre";
$tables[] = "Famille";
$tables[] = "Genre";

foreach ($tables as $tbName) {
    $sql = "SELECT Taxo$tbName.Titre FROM Taxo$tbName";
    $stmt = $dbPDO->prepare($sql);
    $stmt->execute();
    $groups = $stmt->setFetchMode(PDO::FETCH_ASSOC);
    $groups = $stmt->fetchAll();
    $names = array_merge($names,$groups);
    $stmt->closeCursor();
}

var_dump($names);

解答 #

关于MariaDB列别名数量限制 #

MariaDB本身没有列别名数量的硬性限制，出现MySQL server has gone away错误是因为笛卡尔积导致结果集过大：7个表做交叉连接，结果行数是每个表行数的乘积，数据量会爆炸式增长，超出了MySQL的连接超时或内存限制，跟别名数量无关。只设置5个表时结果集规模较小，所以能正常执行。

单列合并的SQL解决方案 #

使用UNION ALL（不需要去重则用UNION ALL，性能更优；需要去重则用UNION）将多个表的Titre列结果纵向合并：

SELECT Titre FROM TaxoDivision
UNION ALL
SELECT Titre FROM TaxoSousDivision
UNION ALL
SELECT Titre FROM TaxoClasse
UNION ALL
SELECT Titre FROM TaxoOrdre
UNION ALL
SELECT Titre FROM TaxoSousOrdre
UNION ALL
SELECT Titre FROM TaxoFamille
UNION ALL
SELECT Titre FROM TaxoGenre;

说明

• UNION ALL会保留所有重复数据，比UNION（自动去重）性能更高；如果需要去重，替换成UNION即可；
• 该查询返回单列Titre，所有表的Titre值按表顺序依次排列，完全符合需求；
• 避免了笛卡尔积的海量数据问题，执行效率远高于交叉连接。

内容的提问来源于stack exchange，提问作者Alex Vio