什么是错误的SQL语法检查表是否存在？

I'm querying a server and iterating through multiple databases using PHP, but for some reason this $sql2 query (which I have read works in countless threads) is returning a syntax error:

我正在查询服务器并使用PHP迭代多个数据库,但由于某种原因,这个$ sql2查询(我读过无数线程的工作)返回语法错误:

$res = mysqli_query($conn,"SHOW DATABASES");

if (!$res){
    // Deal with error
}

while ($d = mysqli_fetch_array($res)){

    $db = $d['Database'];

    $sql1 = "USE $db";
    $query1 = mysqli_query($conn, $sql1);

    if (!$query1){
        // Deal with error
    }

    $sql2 = "IF (EXISTS (SELECT *
                 FROM INFORMATION_SCHEMA.TABLE
                 WHERE TABLE_SCHEMA = '$db'
                 AND TABLE_NAME = 'appusers'))
             BEGIN
                 SELECT * FROM `appusers`
             END";

    $query2 = mysqli_query($conn, $sql2);

    if (!$query2){
        // Deal with error
    }
}

This is the error I receive:

这是我收到的错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE WHERE TABLE_S' at line 1

您的SQL语法有错误;查看与您的MySQL服务器版本对应的手册,以便在'IF附近使用正确的语法(EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE WHERE TABLE_S'在第1行)

My MySQL Server version is 5.6.27 and my PHP interpreter is 5.6

我的MySQL服务器版本是5.6.27,我的PHP解释器是5.6

2 个解决方案

#1

You can't use an IF statement as a query, only in a stored procedure. You'll need to perform two separate queries.

您不能将IF语句用作查询,仅在存储过程中使用。您需要执行两个单独的查询。

$sql = "SELECT COUNT(*) AS count
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = '$db'
        AND TABLE_NAME = 'appusers'";
$result = mysqli_query($conn, $sql) or die(mysqli_error($conn);
$row = mysqli_fetch_assoc($result);
if ($row['count'] != 0) {
    $sql2 = "SELECT * FROM appusers";
    $query2 = mysqli_query($conn, $sql2);
    ...
} else {
    // deal with error
}

#2

There is no if in SQL (although mysql has a function called if)

在SQL中没有if(虽然mysql有一个叫if的函数)

Apparently, you want to run a query if the table exists. The more common way would be to try running the query and check whether the error you get says that the table doesn't exist.

显然,如果表存在,您希望运行查询。更常见的方法是尝试运行查询并检查您获得的错误是否表示该表不存在。

E.g. if you run the query in the mysql command line application, you might get this:

例如。如果您在mysql命令行应用程序中运行查询,您可能会得到:

mysql> SELECT * FROM appusers;
ERROR 1146 (42S02): Table 'test.appusers' doesn't exist

You see two codes:

你看到两个代码:

1146: internal mysql code (obtain this via mysqli_errno)

1146:内部mysql代码(通过mysqli_errno获取)

42S02: the SQL standard error state (obtain this via mysqli_sqlstate)

42S02:SQL标准错误状态(通过mysqli_sqlstate获取)

In your case, checking the SQL state might be better because it covers more cases. E.g., all of these mysql error codes map to SQL state 42S02:

在您的情况下,检查SQL状态可能会更好,因为它涵盖了更多的情况。例如,所有这些mysql错误代码都映射到SQL状态42S02:

1051 - Unknown table '%s'

1051 - 未知表'%s'

1109 - Unknown table '%s' in %s

1109 - %s中的未知表'%s'

1146 - Table '%s.%s' doesn't exist

1146 - 表'%s。%s'不存在

#1