为什么 MySQL 在 SELECT 语句中使用 RAND() 时返回相同的结果?

2021-12-25 00:00:00 php mysql mysqli

我打开了许多浏览器窗口，指向同一个自动刷新的 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.

我的研究受到了很大的限制，因为我一直在搜索的关键字(现在已经几天了)似乎与其他问题有关，例如php mysql 在使用 rand() 时返回相同的结果"有太多谷歌响应指向一般使用 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.

相关文章