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

发布时间：2020-12-13 13:10:14 所属栏目：PHP教程来源：网络整理

导读：我有一个 MySQL查询,直接在我的本地 MySQL数据库上执行时工作正常,但在通过 PHP执行时显示不同的结果. SELECT a.id,a.title,a.public,a.sysstamp,a.password,t.thumbURL,t.countFROM 0_lychee_albums AS aLEFT JOIN (SELECT id,album,thumbURL,@num := IF(@g

我有一个 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;

或作为一个班轮：

SELECT a.id,t.count FROM 0_lychee_albums AS a LEFT JOIN (SELECT id,@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;

结果：

| 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  |

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)计数没有像它应该增加的那样
b)因为a)它显示的行数多于应有的数量(应限制为每个ID 3个)

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

我已经检查了similar questions,但他们没有帮助.以下查询在MySQL和PHP上显示相同的结果：

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

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

编辑更多信息：

$database = new mysqli($host,$user,$password,$database);
$query = "SELECT a.id,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); }

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

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

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

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

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

documentation中的具体说明是：

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:

06001

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:

06002

However,the order of evaluation for expressions involving user variables is undefined.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!