問(wèn)題描述

我打開(kāi)了許多瀏覽器窗口，指向同一個(gè)自動(dòng)刷新的 PHP 頁(yè)面.它訪問(wèn) MySQL 數(shù)據(jù)庫(kù)以識(shí)別過(guò)時(shí)的客戶信息.專門獲取最后一天未更新的記錄并強(qiáng)制更新.其余代碼似乎處理得很好.

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() 不是很合適，因?yàn)樗幚泶蟊淼乃俣群苈窃谶@個(gè)項(xiàng)目結(jié)束之前我的表不會(huì)增加到超過(guò) 20000.我還有一個(gè)隨機(jī)變量被傳遞到 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".

所以這是我的問(wèn)題:在當(dāng)前的 5000 條奇數(shù)記錄中，如果該腳本同時(shí)在多個(gè)瀏覽器窗口中運(yùn)行，它們會(huì)從 MySQL 返回相同的記錄.誠(chéng)然，所選擇的記錄似乎是隨機(jī)選取的，但如果查詢?cè)谕耆嗤臅r(shí)間運(yùn)行，則會(huì)在所有窗口中返回相同的記錄.

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.

我的研究受到了很大的限制，因?yàn)槲乙恢痹谒阉鞯年P(guān)鍵字(現(xiàn)在已經(jīng)幾天了)似乎與其他問(wèn)題有關(guān)，例如php mysql 在使用 rand() 時(shí)返回相同的結(jié)果"有太多谷歌響應(yīng)指向一般使用 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.

雖然我仍然希望得到任何幫助，但實(shí)際上我更想知道為什么會(huì)這樣.我對(duì) MySQL 內(nèi)部工作原理的了解有限，但就我連接 PHP 和 MySQL 的所有經(jīng)驗(yàn)而言，我也沒(méi)有看到任何類似的情況發(fā)生.

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.

更新:

我還使用包含回調(diào)函數(shù)的 ajax 函數(shù)進(jìn)行了測(cè)試，以再次啟動(dòng)它.每次div內(nèi)容都是相同的記錄——但看起來(lái)還是隨機(jī)選擇了哪條記錄.

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>

推薦答案

您可能在某些結(jié)果集中從 MySQL 查詢緩存接收信息.

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

試試這個(gè):

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

參見(jiàn):http://dev.mysql.com/doc/refman/5.1/en/query-cache.html 它說(shuō)，

查詢緩存將 SELECT 語(yǔ)句的文本與發(fā)送給客戶端的相應(yīng)結(jié)果.如果一個(gè)相同的稍后收到語(yǔ)句，服務(wù)器從查詢緩存，而不是再次解析和執(zhí)行語(yǔ)句.這查詢緩存在會(huì)話之間共享，因此由一個(gè)生成的結(jié)果集可以發(fā)送客戶端以響應(yīng)另一個(gè)發(fā)出的相同查詢客戶.

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.

專業(yè)提示:避免在軟件中使用 SELECT *.給出結(jié)果集中所需列的名稱.

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

這篇關(guān)于為什么 MySQL 在 SELECT 語(yǔ)句中使用 RAND() 時(shí)返回相同的結(jié)果?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！