php - 检查表是否存在的 SQL 语法有什么问题？

Question

我正在使用 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
    }
}

这是我收到的错误:

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

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

Answer 1

您不能使用 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
}