在执行大型循环时，我总是遇到 PHP 问题。我将它分解成非常小的循环，并在我们谈论的大量循环中执行数百次。

这是我现在正在处理的一个例子:

$stmt = $GLOBALS['link']->prepare('SELECT * FROM tracking_redirect');
$stmt->execute();
$tracking = new tracking();
while($click = $stmt->fetch()){
    $tracking->checkClickIP($click['blogid'], $click['campaignid'], $click['id'], $click['ipaddress']);
}

tracking_redirect 是一个表，其中包含有关将哪个用户重定向到哪个站点的大量信息。我保存用户的 ip 地址、cookie 数据、日期和 ID 等数据。该表目前有大约 60,000 行。

checkClickIP() 是一个检查点击/重定向是否合法或有效的函数。确保同一用户不会点击太多次的示例。

public function checkClickIP($userid, $campaignid, $clickid, $ipaddress = null) {
        if(!isset($ipaddress)){
            $ipaddress = $_SERVER['REMOTE_ADDR'];
        }

        $datestmt = $GLOBALS['link']->prepare('SELECT crdate FROM tracking_redirect WHERE id=:clickid');
        $datestmt->execute(array('clickid' => $clickid));
        $datestmt = $datestmt->fetch();

        $stmt = $GLOBALS['link']->prepare('SELECT COUNT(*) as total FROM tracking_redirect WHERE ipaddress=:ipaddress AND campaignid=:campaignid AND blogid=:userid AND crdate<:clickdate');
        $stmt->execute(array(
            'ipaddress' => $ipaddress, 
            'campaignid' => $campaignid, 
            'userid' => $userid, 
            'clickdate' => $datestmt['crdate']
        ));
        $totalclicks = $stmt->fetch();

        //Same computer has clicked more than 5 times on the same campaign. ALERT WARNING!
        if($totalclicks['total'] >= 5){
            //Disable the click
            $disable = $GLOBALS['link']->prepare('UPDATE tracking_redirect SET disabled=:disabled WHERE id=:clickid');
            $disable->execute(array(
                'disabled' => 1, 
                'clickid' => $clickid
            ));

            //Send a warning to the user if this person clicked 5 times on the same campaign.
            if($totalclicks['total'] == 5){
                $stmt = $GLOBALS['link']->prepare('SELECT * FROM user_login_history WHERE userid=:userid AND usertype=:usertype AND ipaddress=:ipaddress AND date(visitdate) BETWEEN :startdate AND :currentdate LIMIT 1');
                $stmt->execute(array(
                    'userid' => $userid, 
                    'usertype' => 1, 
                    'ipaddress' => $ipaddress, 
                    'startdate' => date('Y-m-d', strtotime('-3 months')), //If user logged in 3 months ago or closer from this IP
                    'currentdate' => date('Y-m-d')
                ));
                //The computer belongs to the blogger who published the ad. ALERT WARNING! USER CLICKING HIS OWN ADS
                if($loginhistory = $stmt->fetch()){
                    //Send warning to user.
                }
                //The computer does not belong to the blogger, but it's suspicious behavior. ALERT WARNING! CHECK POST
                else{
                    //Send warning to user.
                }
            }
        }
    } 

我知道最好在 While 循环之外准备语句。但我仍然希望能够使用函数并拥有干净的代码。这段代码只是一个例子，说明我无法运行 while 循环而不让服务器返回错误，因为它超时等等。

所以在这种情况下请不要太在意细节。但是请提供也可以应用于其他代码的一般信息。我该怎么做才能执行这种循环 ~10,000 或 ~100,000 次？

最佳答案

当我们处理大量数据时，这总是一个问题。我目前正在开发一种产品，有时会在一个 GO 中更新 20K 条记录。该软件的另一部分使用简单的 LINQ 查询，但在需要上传或更新甚至获取批量数据的地方，我们总是使用存储过程。

在这方面，您可能需要查看此链接 MySQL Stored Procedure vs. complex query

关于php - 循环查询 10'000 次以上的最佳方法？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/20468300/