php - 加速此代码的技巧

Question

任何人都可以建议提示或更改以使此代码更清晰、更快吗？这是我在周五晚上想到的唯一方法，但我相信一定有更有效的方法...

我知道正则表达式效率不高，但老实说我不知道​​我还能怎么做，特别是如果邮政编码数据可以是以下任何内容:

e1 2bee1ebee10ebee10 ebeex1 埃贝ex1ebe

等等……

非常感谢任何编码技巧，H

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Amma Gawd! Someone ate our database!');
    mysql_select_db($dbname);
    $result = mysql_query("SELECT * FROM `Consumer`
            WHERE left(`Postcode`,2) = 'E' 
            OR left(`Postcode`,1) = 'N'
            OR left(`Postcode`,1) = 'W'");
    while($row = mysql_fetch_array($result))  { 
        $email =  $row['Email'];
        if (preg_match("/^[Ee]{1}[0-9]{2}/",$row['Postcode'])) {
            mysql_query("UPDATE `Consumer` SET `CONYES` = '1' WHERE `Email` = '$email'") or die ("Bugger");
            $counter = $counter +1;
        } elseif (preg_match("/^[Nn]{1}[0-9]{2}/",$row['Postcode'])) {
            mysql_query("UPDATE `Consumer` SET `CONYES` = '1' WHERE `Email` = '$email'") or die ("Bugger");
            $counter = $counter +1;     
        } elseif (preg_match("/^[Ww]{1}[0-9]{2}/",$row['Postcode'])) {
            mysql_query("UPDATE `Consumer` SET `CONYES` = '1' WHERE `Email` = '$email'") or die ("Bugger");
            $counter = $counter +1; 
        }   
    }

    $result1 = mysql_query("SELECT * FROM `Consumer`
            WHERE left(`postcode`,2) = 'BR' 
            OR left(`postcode`,2) = 'CR'
            OR left(`postcode`,2) = 'EC'
            OR left(`postcode`,2) = 'EN'
            OR left(`postcode`,2) = 'KT'
            OR left(`postcode`,2) = 'NW'
            OR left(`postcode`,2) = 'RM'
            OR left(`postcode`,2) = 'SE'
            OR left(`postcode`,2) = 'SM'
            OR left(`postcode`,2) = 'SW'
            OR left(`postcode`,2) = 'TW'
            OR left(`postcode`,2) = 'WC'
            OR left(`postcode`,2) = 'BD'
            OR left(`postcode`,2) = 'HG'
            OR left(`postcode`,2) = 'LS'
            OR left(`postcode`,2) = 'WF'
            OR left(`postcode`,2) = 'YO'
            OR left(`postcode`,2) = 'HD'
            OR left(`postcode`,2) = 'HX'");
    while($row1 = mysql_fetch_array($result1))  {   
        $email =  $row1['Email'];
        mysql_query("UPDATE `Consumer` SET `CONYES` = '1' WHERE `Email` = '$email'") or die ("Bugger");
        $counter = $counter +1; 
    }
    echo $counter;
    mysql_close($conn);

Answer 1

您将其作为 PHP 问题发布，但我认为最有效的方法是在一个 SQL 查询中完成所有操作并让数据库完成工作。您可以使用关键字'RLIKE' 来让数据库进行正则表达式匹配。您应该仔细阅读语法以获得您想要的内容，但只是为了开始，您需要这样的东西:

UPDATE `Consumer` SET `CONYES` = '1'
    WHERE `Postcode` RLIKE '[EeNnWwBbMm][0-9]{2}'
    OR LEFT(`postcode`,2) IN ('BR', 'CR', 'EC', 'EN', 'KT', 'NW', 'RM'.....

结果是变化的行数，可以直接赋值给$counter。