mysql - 仅当表存在于数据库中时才执行联合

Question

我有一个复合查询，我在多个表之间执行 UNION。现在，当表不存在时，我的查询失败。 (用例是在每个月的 5 日创建一个执行选择查询的表，所以我的查询直到该月的 5 日才会失败)

所以我希望我的查询返回数据，即使 UNION 或 JOIN 由于表不存在错误而失败。

现在我尝试使用 IF EXISTS 但我不确定我应该如何使用它并且它导致了语法错误。

查询失败，其中表名为:THE TABLE WHICH DOES NOT EXISTS

这是我的查询:

SELECT CONCAT(CONVERT_TZ(calldate,'-4:00','+5:30'),' IST') AS calldate,
       src AS clid,
       `column1`,
       `columnn`,
       `columnn`,
       `columnn`,
       `columnn`
FROM `Table1`
WHERE `accocode`='123456'
  AND calleedate > DATE_SUB(NOW(), INTERVAL 7 DAY)
  AND callee LIKE 'Incoming%'
UNION
SELECT CONCAT(CONVERT_TZ(calldate,'-4:00','+5:30'),' IST') AS calleedate,
       (CASE
            WHEN (position('SIP/' IN channel)>0) THEN substring(channel,position('/' IN channel)+1,position('_' IN channel)-position('/' IN channel)-1)
            ELSE substring(channel,position('/' IN channel)+1,position('@' IN channel)-position('/' IN channel)-1)
        END) AS clid,
       (CASE
            WHEN (`callee` = 'Outgoing') THEN CONCAT(`dst`,' - ',`country`)
            ELSE `dst`
        END)AS dst1,
       `columnn`,
       `columnn`,
       `columnn`,
       `columnn`
FROM `Table1`
JOIN
  (SELECT *
   FROM mvc_v2.isd_code
   ORDER BY `code` DESC)isd ON locate(isd.code,`dst`)=1
WHERE `accountcode`='1184372641'
  AND calldate > DATE_SUB(NOW(), INTERVAL 7 DAY)
  AND NOT (calltype LIKE 'Incoming%')
UNION
SELECT CONCAT(CONVERT_TZ(calldate,'-4:00','+5:30'),' IST') AS calldate,
       src AS clid,
       `columnn`,
       `columnn`,
       `columnn`,
       `columnn`,
       `columnn`
FROM `THE TABLE WHICH DOES NOT EXISTS`
WHERE `accountcode`='1184372641'
  AND calldate > DATE_SUB(NOW(), INTERVAL 7 DAY)
  AND calltype LIKE 'Incoming%'
UNION
SELECT CONCAT(CONVERT_TZ(calldate,'-4:00','+5:30'),' IST') AS calldate,
       (CASE
            WHEN (position('SIP/' IN channel)>0) THEN substring(channel,position('/' IN channel)+1,position('_' IN channel)-position('/' IN channel)-1)
            ELSE substring(channel,position('/' IN channel)+1,position('@' IN channel)-position('/' IN channel)-1)
        END) AS clid,
       (CASE
            WHEN (`calleeetype` = 'Outgoing') THEN CONCAT(`dst`,' - ',`country`)
            ELSE `dst`
        END)AS dst1,
       `columnn`,
       `columnn`,
       `columnn`,
       `columnn`
FROM `THE TABLE WHICH DOES NOT EXISTS`
JOIN
  (SELECT *
   FROM mvc_v2.isd_code
   ORDER BY `code` DESC)isd ON locate(isd.code,`dst`)=1
WHERE `accocode`='123456'
  AND calldate > DATE_SUB(NOW(), INTERVAL 7 DAY)
  AND NOT (calleetype LIKE 'Incoming%')
ORDER BY `calleeedate` DESC

Answer 1

您可以使用information_schema 来检查表是否存在

set @tablecount= 0;
select  count(TABLE_NAME) into @tablecount from information_schema.`COLUMNS` where TABLE_NAME = 'tableNameHere';
select @tablecount

你可以在这里使用计数:

if(@tablecount>0)
Your code here.......