多租户应用中按tenantid统计全表行数的实现方法问询

要实现遍历所有包含tenantid字段的表，并统计指定租户的行数，核心思路是先通过数据库元数据找出符合条件的表，再动态生成并执行计数查询。以下是主流数据库的具体实现方案：

MySQL/MariaDB 方案 #

SET @tenant_id = 64; -- 替换为目标租户ID
SET @sql = NULL;

-- 拼接所有目标表的查询语句
SELECT GROUP_CONCAT(
  CONCAT('SELECT ''', table_name, ''' AS TableName, COUNT(*) AS Count FROM ', table_name, ' WHERE tenantid = ', @tenant_id)
  SEPARATOR ' UNION ALL '
) INTO @sql
FROM information_schema.columns
WHERE column_name = 'tenantid'
AND table_schema = DATABASE(); -- 仅统计当前数据库下的表

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL 方案 #

可以通过PL/pgSQL函数实现遍历统计：

CREATE OR REPLACE FUNCTION count_tenant_rows(p_tenant_id INT)
RETURNS TABLE(table_name TEXT, row_count BIGINT) AS $$
BEGIN
  -- 遍历所有含tenantid字段的表
  FOR table_name IN
    SELECT c.table_name
    FROM information_schema.columns c
    WHERE c.column_name = 'tenantid'
      AND c.table_schema = current_schema()
  LOOP
    -- 动态执行计数查询
    RETURN QUERY EXECUTE format(
      'SELECT %L AS table_name, COUNT(*) AS row_count FROM %I WHERE tenantid = $1',
      table_name, table_name
    ) USING p_tenant_id;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 调用函数统计租户64的数据
SELECT * FROM count_tenant_rows(64);

SQL Server 方案 #

DECLARE @tenant_id INT = 64; -- 替换为目标租户ID
DECLARE @sql NVARCHAR(MAX) = '';

-- 拼接查询语句
SELECT @sql += CONCAT(
  'SELECT ''', t.name, ''' AS TableName, COUNT(*) AS Count FROM ', QUOTENAME(t.name), ' WHERE tenantid = ', @tenant_id, ' UNION ALL '
)
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name = 'tenantid';

-- 移除末尾多余的UNION ALL
SET @sql = LEFT(@sql, LEN(@sql) - 10);

-- 执行动态SQL
EXEC sp_executesql @sql;

注意事项 #

• 执行用户需要具备访问系统元数据表（如information_schema、sys.tables）以及所有业务表的权限
• 若表分布在多个schema中，需调整元数据查询里的schema过滤条件
• 对于数据量较大的表，建议给tenantid字段创建索引，提升COUNT查询的性能

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