同一SQL数据库中如何跨动态增减的team前缀schema查询数据？

核心思路 #

因为schema会动态增删，无法硬编码名称，不需要提前枚举所有schema，直接通过数据库内置的元数据系统表动态筛选符合前缀规则的schema，再自动拼接查询语句执行即可，流程固定为3步：

• 从系统元数据表查询所有名称以team开头的schema
• 为每个匹配到的schema生成SELECT * FROM <schema名>.info的查询片段，用UNION ALL拼接为完整查询语句
• 执行动态生成的语句，拿到所有目标表的汇总结果

主流数据库具体实现代码 #

PostgreSQL #

DO $$
DECLARE
    query_text TEXT;
BEGIN
    -- 拼接所有符合规则的查询，自动做标识符转义
    SELECT string_agg(
        format('SELECT * FROM %I.info', schema_name),
        ' UNION ALL '
    )
    INTO query_text
    FROM information_schema.schemata
    WHERE schema_name LIKE 'team%';

    -- 结果存入临时表，方便后续查询
    DROP TABLE IF EXISTS temp_team_info;
    CREATE TEMP TABLE temp_team_info AS EXECUTE query_text;
END $$;

-- 查询最终汇总结果
SELECT * FROM temp_team_info;

MySQL 8.0+ #

SET @query_text = NULL;
-- 拼接查询片段，反引号转义标识符
SELECT GROUP_CONCAT(
    CONCAT('SELECT * FROM `', schema_name, '`.info')
    SEPARATOR ' UNION ALL '
) INTO @query_text
FROM information_schema.SCHEMATA
WHERE schema_name LIKE 'team%';

-- 预处理并执行动态语句
PREPARE stmt FROM @query_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

MySQL 5.7及更低版本需要先调大group_concat_max_len参数，避免拼接的SQL过长被截断。

SQL Server #

DECLARE @query_text NVARCHAR(MAX)
-- 拼接查询片段，方括号转义标识符
SELECT @query_text = STRING_AGG(
    'SELECT * FROM [' + name + '].info',
    ' UNION ALL '
)
FROM sys.schemas
WHERE name LIKE 'team%'

-- 执行动态语句
EXEC sp_executesql @query_text

避坑说明 #

• 建议在筛选schema时增加表存在性校验，关联存储表元数据的系统视图，过滤掉schema名匹配但不存在info表的条目，避免执行时报「表不存在」错误
• 需保证所有目标schema下的info表列数、对应列数据类型兼容，否则UNION ALL查询会报列不匹配错误
• 如果单表数据量较大，建议给每个查询片段增加业务过滤条件，避免全表扫描引发性能问题

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