MySQL选择表是否存在 [英] MySQL Select If Table Exists

问题描述

我需要在一个表上运行一个计数查询，但前提是该表存在，

I need to run a count query on a table but only if that table exists,

SELECT 
CASE WHEN (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'DATABASENAME' AND TABLE_NAME = 'testtable') < 1 THEN '0' 
ELSE (SELECT COUNT(*) FROM testtable) END;

如果表不存在，上述查询应返回0，但如果存在，则应返回计数.

The above query should return 0 if the table doesn't exist, but if it does it should get the count.

这将返回一条错误消息，说明"testtable"不存在，我们知道它不存在，因为information_schema计数返回0.

This returns an error saying "testtable" doesn't exist, we know it doesn't exist as the information_schema count returns 0.

在MySQL中这可能吗?

Is this possible in MySQL?

推荐答案

您可以尝试以下方法:

       SET @val := CASE (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'DATABASENAME' AND TABLE_NAME = 'testtable')
           WHEN 0 THEN 0
           ELSE (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'DATABASENAME' AND TABLE_NAME = 'testtable')

END;
        SELECT @val;

它将返回0，如果不存在这样的表，并且如果存在这样的表，则它将返回计数，如果将其用作函数可能会更好.

It will return 0, if there is no such table and if such table exists , it will return the count, it may be better if you take it into function.

这篇关于MySQL选择表是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！