php - 语法错误或访问冲突 : 1064 - PDO refuses to execute multiple queries (including CREATE TEMPORARY TABLE)

Question

我正在尝试在 PHP 中使用 PDO 执行以下查询。在 PhpMyAdmin 中应用查询时，查询工作正常并且没有任何提到的问题。然而，它在 PHP 中执行时显示以下错误:

 Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'CREATE TEMPORARY TABLE IF NOT EXISTS temp2 AS SELECT COUNT(*) AS total_visits, s' at line 1

代码简单，不知道是否需要表结构，但会在本题最后提供

    $sql = "CREATE TEMPORARY TABLE IF NOT EXISTS temp1 AS SELECT COUNT(*) AS total_views, series_id FROM my_app_stats d1 WHERE 1 = 1 AND d1.stats_type = 0 AND d1.created_at_text = DATE_FORMAT(NOW(), '%Y-%m-%d') GROUP BY series_id; CREATE TEMPORARY TABLE IF NOT EXISTS temp2 AS SELECT COUNT(*) AS total_visits, series_id FROM my_app_stats d2 WHERE 1 = 1 AND d2.stats_type = 1 AND d2.created_at_text = DATE_FORMAT(NOW(), '%Y-%m-%d') GROUP BY series_id; ALTER TABLE `temp1` ADD INDEX `temp1_idx_id` (`series_id`); ALTER TABLE `temp2` ADD INDEX `temp2_idx_id` (`series_id`); SELECT database_series.id, (SELECT dusers.user_name FROM my_app_users dusers WHERE dusers.id = database_series.series_owner) user_name, database_series.series_title, temp1.total_views, temp2.total_visits FROM my_app_series database_series LEFT JOIN temp1 ON temp1.series_id = database_series.id LEFT JOIN temp2 ON temp2.series_id = database_series.id ORDER BY total_views DESC, total_visits DESC LIMIT 10";
    $query = $this->conn->prepare($sql);
    $query->execute();
    $data = $query->fetchAll(PDO::FETCH_ASSOC);
    $query->closeCursor();
    return $data;

我的应用程序用户

id | user_name

我的应用统计

id | series_id | created_at (timestamp) | created_at_text (text, formatted as y-m-d) 

我的应用系列

id | series_owner

Answer 1

您不能将 prepare() 与一系列多重语句一起使用。 MySQL 不支持它。

这几乎是 PHP MySQLi multi_query prepared statement 的重复但那是针对 mysqli 的。没关系，这是 MySQL 的限制，而不是任何一个 PHP 接口(interface)的限制。

无论如何，没有理由在一次调用中准备多个 SQL 语句。

您应该一次执行一条语句。

关于您的评论:是的，它们都是相关的，只要您使用与 $this->conn 中相同的连接查询它们，您就可以依赖仍然可用的临时表。除非您关闭连接(或者当然是故意删除临时表)，否则临时表不会消失。

例子:

$sql = "CREATE TEMPORARY TABLE IF NOT EXISTS temp1 AS 
  SELECT COUNT(*) AS total_views, series_id FROM my_app_stats d1 
  WHERE 1 = 1 AND d1.stats_type = 0 
    AND d1.created_at_text = DATE_FORMAT(NOW(), '%Y-%m-%d') 
  GROUP BY series_id";
$this->conn->exec($sql);

$sql = "CREATE TEMPORARY TABLE IF NOT EXISTS temp2 AS 
  SELECT COUNT(*) AS total_visits, series_id FROM my_app_stats d2 
  WHERE 1 = 1 AND d2.stats_type = 1 
    AND d2.created_at_text = DATE_FORMAT(NOW(), '%Y-%m-%d') 
  GROUP BY series_id";
$this->conn->exec($sql);

$sql = "ALTER TABLE `temp1` ADD INDEX `temp1_idx_id` (`series_id`)";
$this->conn->exec($sql);

$sql = "ALTER TABLE `temp2` ADD INDEX `temp2_idx_id` (`series_id`)";
$this->conn->exec($sql);

$sql = "SELECT 
    database_series.id,
    (SELECT dusers.user_name FROM my_app_users dusers 
     WHERE dusers.id = database_series.series_owner) AS user_name, 
    database_series.series_title, 
    temp1.total_views, 
    temp2.total_visits 
  FROM my_app_series database_series 
  LEFT JOIN temp1 ON temp1.series_id = database_series.id 
  LEFT JOIN temp2 ON temp2.series_id = database_series.id 
  ORDER BY total_views DESC, total_visits DESC LIMIT 10";
$query = $this->conn->prepare($sql);
$query->execute();
$data = $query->fetchAll(PDO::FETCH_ASSOC);
return $data;