PHP和MySQL使用相同的查询显示不同的结果

I have a MySQL query which works fine when executed directly on my local MySQL Database, but shows a different result when executed via PHP.

我有一个MySQL查询,直接在我的本地MySQL数据库上执行时工作正常,但在通过PHP执行时显示不同的结果。

SELECT a.id, a.title, a.public, a.sysstamp, a.password, t.thumbURL, t.count
FROM 0_lychee_albums AS a
LEFT JOIN   (SELECT id, album, thumbURL,
                @num := IF(@group = album, @num + 1, 0) AS count,
                @group := album AS dummy
        from 0_lychee_photos
        WHERE album != 0
        ORDER BY album, star DESC) AS t ON a.id = t.album
WHERE count <= 2 OR count IS NULL;

or as a one-liner:

或作为一个班轮:

SELECT a.id, a.title, a.public, a.sysstamp, a.password, t.thumbURL, t.count FROM 0_lychee_albums AS a LEFT JOIN (SELECT id, album, thumbURL, @num := IF(@group = album, @num + 1, 0) AS count, @group := album AS dummy FROM 0_lychee_photos WHERE album != 0 ORDER BY album, star DESC) AS t ON a.id = t.album WHERE count <= 2 OR count IS NULL;

The result:

| id | title             | public  | sysstamp   | password | thumbURL                              | count |
| 71 | [Import] 01       | 0       | 1415091268 | NULL     | cad008943372d984a9b74378874128f8.jpeg | 0     |
| 72 | [Import] 9n401238 | 0       | 1415091268 | NULL     | 7b832b56f182ad3403521589e2815f67.jpeg | 0     |
| 72 | [Import] 9n401238 | 0       | 1415091268 | NULL     | f058f379ce519f1d8a2ff8c0f5003631.jpeg | 1     |
| 72 | [Import] 9n401238 | 0       | 1415091268 | NULL     | a4d59377bed059e3f60cccf01a69c299.jpeg | 2     |
| 73 | Untitled          | 0       | 1415114200 | NULL     | NULL                                  | NULL  |

The PHP result:

PHP结果:

| id | title             | public  | sysstamp   | password | thumbURL                              | count |
| 71 | [Import] 01       | 0       | 1415091268 | NULL     | cad008943372d984a9b74378874128f8.jpeg | 0     |
| 72 | [Import] 9n401238 | 0       | 1415091268 | NULL     | 7b832b56f182ad3403521589e2815f67.jpeg | 0     |
| 72 | [Import] 9n401238 | 0       | 1415091268 | NULL     | f058f379ce519f1d8a2ff8c0f5003631.jpeg | 0     |
| 72 | [Import] 9n401238 | 0       | 1415091268 | NULL     | a4d59377bed059e3f60cccf01a69c299.jpeg | 0     |
| 72 | [Import] 9n401238 | 0       | 1415092318 | NULL     | 7b832b56f182ad3403521589e2815f67.jpeg | 0     |
| 72 | [Import] 9n401238 | 0       | 1415092369 | NULL     | cad008943372d984a9b74378874128f8.jpeg | 0     |
| 72 | [Import] 9n401238 | 0       | 1415092369 | NULL     | 84030a64a1f546e223e6a46cbf12910f.jpeg | 0     |
| 73 | Untitled          | 0       | 1415114200 | NULL     | NULL                                  | NULL  |

a) count isn't increasing like it should
b) because of a) it shows more rows than it should (should be limited to 3 per id)

a)计数没有增加,因为它应该b)因为a)它显示的行数多于应该的数量(应该限制为每个id 3个)

I checked it multiple times, both queries are exactly the same. There's no user input or any difference in PHP.

我多次检查它,两个查询完全相同。 PHP中没有用户输入或任何差异。

I already checked similar questions, but non of them helped. The following queries are showing the same result on both MySQL and PHP:

我已经检查了类似的问题,但是他们没有帮助。以下查询在MySQL和PHP上显示相同的结果:

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

Is anyone aware of an issue casing this difference?

是否有人意识到存在这种差异的问题?

Edit with further information:

编辑更多信息:

$database = new mysqli($host, $user, $password, $database);
$query = "SELECT a.id, a.title, a.public, a.sysstamp, a.password, t.thumbURL, t.count FROM 0_lychee_albums AS a LEFT JOIN (SELECT id, album, thumbURL, @num := IF(@group = album, @num + 1, 0) AS count, @group := album AS dummy FROM 0_lychee_photos WHERE album != 0 ORDER BY album, star DESC) AS t ON a.id = t.album WHERE count <= 2 OR count IS NULL";
$albums = $database->query($query);
while ($album = $albums->fetch_assoc()) { print_r($album); }

I also tried it with and without the following before executing the query:

在执行查询之前,我也尝试过使用和不使用以下内容:

$database->set_charset('utf8');
$database->query('SET NAMES utf8;');

2 个解决方案

#1

Yup. The order of evaluation of expressions in a select clause is not guaranteed. So, the variable assignments can happen in different orders, depending on how the query is invoked.

对。不保证select子句中表达式的计算顺序。因此,变量赋值可以按不同的顺序发生,具体取决于调用查询的方式。

You can fix this by putting all the variable assignments into a single expression. Try using this subquery for t:

您可以通过将所有变量赋值放入单个表达式来解决此问题。尝试将此子查询用于t:

   (SELECT id, album, thumbURL,
            (@num := IF(@group = album, @num + 1,
                        if(@group := album, 0, 0)
                       )
            ) as count
    FROM 0_lychee_photos CROSS JOIN
         (SELECT @num := 0, @group := NULL) vars
    WHERE album <> 0
    ORDER BY album, star DESC
   ) t

The specific explanation in the documentation is:

文档中的具体说明是:

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. For example, to increment a variable, this is okay:

作为一般规则,除了在SET语句中,您不应该为用户变量赋值并在同一语句中读取值。例如,要增加变量,这没关系:
SET @a = @a + 1;
For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

对于其他语句,例如SELECT,您可能会得到您期望的结果,但这不能保证。在下面的语句中,您可能会认为MySQL将首先评估@a,然后再进行一次分配:
SELECT @a, @a:=@a+1, ...;
However, the order of evaluation for expressions involving user variables is undefined.

但是,涉及用户变量的表达式的评估顺序是未定义的。

#2

A simple way to solve this is set variables mysql in your PHP doc. Like this: $var = mysql_query("SET @nun := 0;");

解决这个问题的一个简单方法是在PHP文档中设置变量mysql。像这样:$ var = mysql_query(“SET @nun:= 0;”);

#1