如何创建无重复且含来源表拼接列的MySQL视图？

需求回顾 #

从TABLEA和TABLEB两张表提取数据，生成视图要求：

• 每个itemId对应唯一记录
• source_tables列拼接该itemId所在的所有表名

现有方案分析 #

你用UNION ALL子查询结合GROUP_CONCAT的方案是可行的，但可以根据实际场景调整优化：

基础可行方案（你的尝试2）

如果需要保留其他字段，这个方案简洁直接：

CREATE VIEW combined_view AS
SELECT 
    itemId,
    -- 根据实际需求选择聚合函数，比如MAX/MIN取任意值，或确保字段一致
    MAX(other_column) AS other_column,
    GROUP_CONCAT(DISTINCT source_table ORDER BY source_table SEPARATOR ', ') AS source_tables
FROM (
    SELECT itemId, other_column, 'TABLEA' AS source_table FROM TABLEA
    UNION ALL
    SELECT itemId, other_column, 'TABLEB' AS source_table FROM TABLEB
) AS union_data
GROUP BY itemId;

注意：如果两张表中同一itemId的其他字段值不一致，必须用聚合函数（如MAX/MIN）处理，否则会触发MySQL的ONLY_FULL_GROUP_BY模式报错。

优化方案选择 #

1. 仅需itemId和来源表时的高效方案

如果不需要其他字段，且itemId在两张表都有索引，可以用关联查询替代全量扫描，性能更优：

CREATE VIEW combined_view AS
-- 处理同时存在于两张表的itemId
SELECT 
    a.itemId,
    'TABLEA, TABLEB' AS source_tables
FROM TABLEA a
JOIN TABLEB b ON a.itemId = b.itemId
UNION
-- 处理仅存在于TABLEA的itemId
SELECT itemId, 'TABLEA' AS source_tables FROM TABLEA
WHERE NOT EXISTS (SELECT 1 FROM TABLEB WHERE itemId = TABLEA.itemId)
UNION
-- 处理仅存在于TABLEB的itemId
SELECT itemId, 'TABLEB' AS source_tables FROM TABLEB
WHERE NOT EXISTS (SELECT 1 FROM TABLEA WHERE itemId = TABLEB.itemId);

这个方案利用EXISTS和索引快速匹配，避免了全表UNION ALL后的分组，数据量越大优势越明显。

2. 字段完全一致时的简化方案

如果TABLEA和TABLEB的结构完全一致，且同一itemId的所有字段值也完全相同，UNION本身就能去重，再关联获取来源表：

CREATE VIEW combined_view AS
SELECT 
    u.itemId,
    u.other_column,
    GROUP_CONCAT(DISTINCT t.source_table SEPARATOR ', ') AS source_tables
FROM (
    SELECT itemId, other_column FROM TABLEA
    UNION
    SELECT itemId, other_column FROM TABLEB
) AS u
JOIN (
    SELECT itemId, 'TABLEA' AS source_table FROM TABLEA
    UNION ALL
    SELECT itemId, 'TABLEB' AS source_table FROM TABLEB
) AS t ON u.itemId = t.itemId
GROUP BY u.itemId, u.other_column;

但这种场景极少，因为如果字段完全一致，UNION不会产生重复itemId，你的最初UNION尝试也不会出现重复问题。

总结 #

• 若需要保留其他字段，且同一itemId的字段值可能不一致，你当前的UNION ALL + GROUP_CONCAT方案已经是最优选择之一，易维护且逻辑清晰。
• 若仅需itemId和来源表，且itemId有索引，优先选择关联查询的方案，性能更出色。

内容的提问来源于stack exchange，提问作者G. Rey