問題描述

我打開了許多瀏覽器窗口，指向同一個自動刷新的 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.

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