MySQL - 如果表不存在则忽略查询 [英] MySQL - Ignore query if table not exist

问题描述

我有一个名为 tableA 的表，它在某些数据库中可能不存在.当数据库中不存在此 tableA 时，我希望忽略 select 查询语句.

I have this table called tableA where it might not exist in certain databases. I want the the select query statement to be ignored when this tableA does not exist in the databases.

我试图从信息模式中选择计数，但是 if-else 语句之一仍然具有 select * from tableA，它仍然会给我一个错误.

I have tried to select count from the information schema, but one of the if-else statement still having the select * from tableA, which will still give me an error.

    SELECT IF( 
   (SELECT COUNT(*) FROM information_schema.TABLES 
   WHERE TABLE_NAME = 'tableA')>0,
   (SELECT COUNT(*) FROM tableA),
   (SELECT COUNT(*) FROM tableB) )

而且我还需要真假的选择语句来返回超过 1 列，但是当我尝试这样做时，它会给我一个超过 1 个操作数的错误.如何使查询在此 IF 查询中返回超过 1 列.

And also i need the select statement of the true-false to return me more than 1 column, but when i tried that, it will give me an error of more than 1 operands. How doi make the query to return more than 1 columns in this IF query.

推荐答案

我必须同意您的要求似乎很奇怪.无论如何，您的查询不起作用，因为 MySQL(我敢打赌所有其他 DBMS 也是如此)首先评估查询以检查语法错误等......以及现有表.

I must agree that your requirement seems quite odd. Anyway, your query doesn't work, because MySQL (and I bet all other DBMS too) first evaluates the query to check for syntax errors and so on...and for existing tables.

您可以在应用程序代码中进行多次查询，也可以创建存储过程以使用准备好的语句获取数据.代码如下所示:

Either you just make this multiple queries in your application code or you create a stored procedure to get the data using prepared statements. Code for this would look something like this:

DELIMITER $$
CREATE PROCEDURE get_my_data()
BEGIN
SET @table_name = '';
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE SCHEMA_NAME = 'your_db_name' 
           AND TABLE_NAME = 'your_table_name')
THEN SET @table_name = 'tableA';
ELSE SET @table_name = 'tableB';
END IF;

SET @sql = CONCAT('SELECT COUNT(*) FROM ', @table_name, ';');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END $$
DELIMITER ;

一旦创建，您就可以使用

Once created you'd then execute the procedure with

CALL get_my_data();

这篇关于MySQL - 如果表不存在则忽略查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！