php - mysql_query 表示 sql 语法错误，但相同的查询在 mysql 客户端中工作正常

Question

当我尝试发送以下查询时:

SET @attempts=0; 
SELECT mt1.TimeStamp, mt1.ReadAttempts - @attempts AS delta, 
       @attempts := mt1.ReadAttempts ReadAttempts 
FROM OneWireStats mt1 
WHERE SensorIndex=1 ORDER BY mt1.TimeStamp;

为了获取表 OneWireStats 中行之间的增量值，我收到以下错误:

Query failed 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 'SELECT mt1.TimeStamp, mt1.ReadAttempts - @attempts AS delta, @attempts := mt1.Re' at line 1 Query = SET @attempts=0; SELECT mt1.TimeStamp, mt1.ReadAttempts - @attempts AS delta, @attempts := mt1.ReadAttempts ReadAttempts FROM OneWireStats mt1 WHERE SensorIndex=1 ORDER BY mt1.TimeStamp;, Query no: 0

当我将上面错误打印输出中的查询粘贴到 mysql 客户端时，它工作正常。

mysql>  SET @attempts=0; SELECT mt1.TimeStamp, mt1.ReadAttempts - @attempts AS delta, @attempts := mt1.ReadAttempts ReadAttempts FROM OneWireStats mt1 WHERE SensorIndex=1 ORDER BY mt1.TimeStamp LIMIT 10; Query OK, 0 rows affected (0.01 sec)+---------------------+-------+--------------+ | TimeStamp           | delta | ReadAttempts |+---------------------+-------+--------------+ | 2013-12-23 07:55:01 |  5532 |         5532 | | 2013-12-23 08:00:00 |   302 |         5834 | | 2013-12-23 08:05:01 |   302 |         6136 | | 2013-12-23 08:10:00 |   302 |         6438 | | 2013-12-23 08:15:00 |   302 |         6740 | | 2013-12-23 08:20:01 |   302 |         7042 | | 2013-12-23 08:25:00 |   302 |         7344 | | 2013-12-23 08:30:01 |   302 |         7646 | | 2013-12-23 08:35:00 |   302 |         7948 | | 2013-12-23 08:40:01 |   302 |         8250 |+---------------------+-------+--------------+ 10 rows in set (0.07 sec)

I use the following php code:

$query = mysql_real_escape_string(trim($queryobj[$counter]));

$sqlresult = mysql_query($query) or die("Query failed with error: ".mysql_error() . 
             " Query = " . $query . ", Query no: " . $counter);

我在查询中没有使用任何单引号或双引号，所以我还不明白错误打印输出。

非常适合提示。

Answer 1

您需要单独运行每个查询，可能像

mysql_query( "SET @attempts=0" );
mysql_query( "SELECT mt1.TimeStamp, mt1.ReadAttempts - @attempts AS delta, @attempts := mt1.ReadAttempts ReadAttempts FROM OneWireStats mt1 WHERE SensorIndex=1 ORDER BY mt1.TimeStamp" );

或使用 mysqli的 multi_query() ，比如:

$query  = "SET @attempts=0;";
$query .= "SELECT mt1.TimeStamp, mt1.ReadAttempts - @attempts AS delta, @attempts := mt1.ReadAttempts ReadAttempts FROM OneWireStats mt1 WHERE SensorIndex=1 ORDER BY mt1.TimeStamp";
mysqli_multi_query($connection, $query);
mysqli_next_result($connection);
if ($result = mysqli_store_result($connection)) {
  //while loop here
}