当在SELECT语句中使用RAND()时，为什么MySQL返回相同的结果? [英] Why is MySQL returning the same results while using RAND() in the SELECT statement?

问题描述

我打开了许多浏览器窗口，这些窗口指向相同的自动刷新PHP页面.它访问MySQL数据库以标识过时的客户信息.特别是获取前一天未更新的记录并强制更新.其余代码似乎都可以正常处理.

I have a number of browser windows open pointing to the same auto-refreshing PHP page. It accesses a MySQL database to identify customer information that is out of date. Specifically getting records that haven't been updated in the last day and forces an update. The rest of the code seems to be processing fine.

这是我的MySQLi查询:

Here is my MySQLi query:

$query = "SELECT *
          FROM customers
          WHERE customer_group='consumables' AND customer_updated < DATE_SUB(NOW(), INTERVAL 1 DAY)
          ORDER BY RAND()
          LIMIT 10";

我被告知RAND()不适用于大型表，因为它处理大型表的速度很慢，但是在该项目结束之前，我的表不会增加到20000以上.我还将一个随机变量传递给URL，例如"clientdataupdates.php?nocachepls = 1541231".

I have been informed that RAND() is not very suitable due to it's slow processing of large tables, but my tables will not increase to over 20000 before the end of this project. I also have a random variable being passed to the URL like "clientdataupdates.php?nocachepls=1541231".

这是我的问题:在当前的5000条奇数记录中，如果此脚本同时在多个浏览器窗口中运行，它们将获得从MySQL返回的相同记录.诚然，所选记录似乎是随机选择的，但是如果查询是在同一时间运行的，则所有窗口中都会返回相同的记录.

So here is my problem: Out of the current 5000 odd records, if this script is run in multiple browser windows at the same time, they are getting the same records returned from MySQL. Admittedly the chosen record seems to be picked at random, but the same record is returned in all of the windows if the query is run at the exact same time.

由于我一直在搜索的关键字(现在已经过几天)似乎与其他问题有关，因此我的研究受到了很大的限制. 使用rand()时php mysql返回相同结果"的Google响应太多，指向通常使用rand().

My research has been quite limited by the fact that they keywords I have been searching for (over a few days now) seem to relate to other problems e.g. "php mysql returning same result while using rand()" has too many google responses that point to using rand() in general.

尽管我仍然希望获得任何帮助，但实际上我更想知道为什么会这样.我对MySQL的内部运作的了解是有限的，但是根据我与PHP和MySQL交互的所有经验，我也没有看到类似的事情发生.

Whilst I would still appreciate any assistance, I would actually more like to know why this is happening. My knowledge of the inner workings of MySQL is limited, but for all my experience interfacing PHP and MySQL I have not seen anything similar occur either.

已更新:

我也测试过使用ajax函数，该函数包括一个回调函数以再次启动它.每次div内容都是相同的记录-但看起来仍然是随机选择的记录.

I have also tested using an ajax function that includes a callback function to kick it off again. Every time the div contents are the same record - but it still looks like which record is selected at random.

<div id='worker1' class='workerDiv'>worker: waiting..</div>
<div id='worker2' class='workerDiv'>worker: waiting..</div>
<div id='worker3' class='workerDiv'>worker: waiting..</div>
<div id='worker4' class='workerDiv'>worker: waiting..</div>
<div id='worker5' class='workerDiv'>worker: waiting..</div>
<script>
 function nextWorker(thisWorker){
  setTimeout(function(){ ajaxpage('customerdata_worker.php',thisWorker,nextWorker(thisWorker)); }, 10000);
 }
 setTimeout(nextWorker('worker1'), 100);
 setTimeout(nextWorker('worker2'), 100);
 setTimeout(nextWorker('worker3'), 100);
 setTimeout(nextWorker('worker4'), 100);
 setTimeout(nextWorker('worker5'), 100);
</script>

推荐答案

在某些结果集中，您可能正在从MySQL查询缓存中接收信息.

You are probably receiving information from the MySQL query cache in some result sets.

尝试一下:

SELECT SQL_NO_CACHE *
       /* etc */

注意:将SQL_NO_CACHE词与SELECT和*(或您选择的第一列的名称)放在同一行.

BEWARE: Put the SQL_NO_CACHE word on the same line as the SELECT and the * (or the name of the first column you are selecting).

请参见: http://dev.mysql.com /doc/refman/5.1/en/query-cache.html 它说，

查询缓存将SELECT语句的文本与 发送给客户端的相应结果.如果相同 稍后收到该语句，服务器从检索结果. 查询缓存，而不是再次解析并执行该语句.这 查询缓存在会话之间共享，因此由一个生成的结果集 可以发送客户端以响应另一个发出的相同查询 客户.

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

专业提示:避免在软件中使用SELECT *.输入结果集中所需列的名称.

Pro tip: Avoid SELECT * in software. Give the names of the columns you need in the result set.

这篇关于当在SELECT语句中使用RAND()时，为什么MySQL返回相同的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！