mysql - 如何获取从中找到行的表的名称

Question

我正在使用一个 mysql 存储过程来检查 5-6 个表中是否存在 ID。如果这些表中的任何一个包含该 ID，我会将标志设置为 true。最后，我使用 SELECT 选择标志。

SP的参数如下:

->settings_type = "branch"->settings_id (the id that is searched)

If the searched ID is present in any of those 6 tables, how can i know from which table the ID was found?

BEGIN
DECLARE boolStatus BOOL DEFAULT FALSE;

IF settings_type = "branch"
THEN
    IF ((SELECT COUNT(tblbatches.intBranchId)   FROM tblbatches         WHERE tblbatches.intBranchId        = settings_id > 0) OR 
         (SELECT COUNT(tblexams.intBranchId)        FROM tblexams           WHERE tblexams.intBranchId      = settings_id > 0) OR
         (SELECT COUNT(tblquestions.intBranchId)    FROM tblquestions   WHERE tblquestions.intBranchId  = settings_id > 0) OR
         (SELECT COUNT(tblresults.intBranchId)  FROM tblresults     WHERE tblresults.intBranchId        = settings_id > 0) OR
         (SELECT COUNT(tblstudents.intBranchId) FROM tblstudents        WHERE tblstudents.intBranchId   = settings_id > 0) OR
         (SELECT COUNT(tblsubjects.intBranchId) FROM tblsubjects        WHERE tblsubjects.intBranchId   = settings_id > 0)
        )
        THEN
            SET boolStatus := TRUE;
    END IF;
    SELECT boolStatus;
END IF;
END

Answer 1

重写程序的一种方法

DELIMITER $$
CREATE PROCEDURE sp_name(IN settings_type VARCHAR(32), IN settings_id INT)
BEGIN
  IF settings_type = 'branch' THEN
    SELECT COALESCE(SUM(total), 0) > 0 status, 
           GROUP_CONCAT(source) source
      FROM
    (
      SELECT 'tblbatches' source, COUNT(*) total
        FROM tblbatches
       WHERE intBranchId = settings_id
       UNION ALL
      SELECT 'tblexams', COUNT(*)
        FROM tblexams 
       WHERE intBranchId = settings_id
       UNION ALL
      SELECT 'tblquestions', COUNT(*) 
        FROM tblquestions 
       WHERE intBranchId = settings_id
       UNION ALL
      SELECT 'tblresults', COUNT(*)
        FROM tblresults
       WHERE intBranchId = settings_id
       UNION ALL
      SELECT 'tblstudents', COUNT(*)
        FROM tblstudents
       WHERE intBranchId = settings_id
       UNION ALL
      SELECT 'tblsubjects', COUNT(*)
        FROM tblsubjects
       WHERE intBranchId = settings_id
    ) q 
     WHERE total > 0;
  END IF;
END$$
DELIMITER ;

找到匹配记录时的示例输出:

| STATUS |                SOURCE |----------------------------------|      1 | tblbatches,tblresults |

当他们没有的时候

| STATUS | SOURCE |-------------------|      0 | (null) |

这里是SQLFiddle 演示