统计指定ID在多少个不同MySQL表中出现的最优方法

不存在完全跳过所有地点表查询的方案，你的表结构是按地点分表存储，MySQL原生没有自动维护跨表ID出现频次的元数据，所以必须对所有目标表做存在性校验，但可以避免多表关联操作，实现复杂度和性能都比全表JOIN好很多。

可选方案 #

方案1：手动拼UNION ALL查询（适合表数量固定的场景） #

不需要做表关联，每个子查询只做单表存在性校验，加LIMIT 1匹配到目标ID就停止扫描该表，性能损耗极低。
示例代码：

SELECT COUNT(*) AS location_count
FROM (
    SELECT 1 FROM location_chengdu WHERE user_id = 你的目标ID LIMIT 1
    UNION ALL
    SELECT 1 FROM location_guangzhou WHERE user_id = 你的目标ID LIMIT 1
    -- 剩余23张地点表按上述格式补充即可
) AS temp;

方案2：动态SQL存储过程（适合表数量会新增的场景） #

如果后续会不断新增地点表，可通过查询INFORMATION_SCHEMA自动获取所有地点表，动态生成查询逻辑，不需要每次手动修改SQL。
示例代码：

DELIMITER //
CREATE PROCEDURE CalcIdAppearCount(IN target_id INT, OUT res INT)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE cur_table VARCHAR(255);
    -- 游标匹配所有符合命名规则的地点表，这里假设地点表统一前缀为location_
    DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE 'location_%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET @count = 0;
    OPEN cur;
    loop_label: LOOP
        FETCH cur INTO cur_table;
        IF done THEN
            LEAVE loop_label;
        END IF;
        -- 单表存在性校验
        SET @query_sql = CONCAT('SELECT EXISTS(SELECT 1 FROM ', cur_table, ' WHERE id = ', target_id, ') INTO @is_exist');
        PREPARE stmt FROM @query_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        IF @is_exist = 1 THEN
            SET @count = @count + 1;
        END IF;
    END LOOP;
    CLOSE cur;
    SET res = @count;
END //
DELIMITER ;

调用方式：

CALL CalcIdAppearCount(1001, @result);
SELECT @result;

方案3：新增汇总表+触发器（适合高频查询场景） #

如果这类统计是高频需求，可以新增一张汇总表同步所有ID的出现地点，后续查询直接扫单表即可，不需要每次遍历25张表。

1. 汇总表结构：

CREATE TABLE id_location_map (
    id INT AUTO_INCREMENT PRIMARY KEY,
    target_id INT NOT NULL,
    location_table VARCHAR(255) NOT NULL,
    UNIQUE KEY uk_target_loc (target_id, location_table)
);

2. 给每个地点表加INSERT/UPDATE/DELETE触发器，数据变更时自动同步到汇总表，后续查询直接执行：

SELECT COUNT(*) FROM id_location_map WHERE target_id = 你的目标ID;

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