MySQL 更新数组非常慢 - mysql update array is very slow

mysql update array is very slow

本文关键字：非常数组更新 MySQL | 更新日期: 2023-09-27

>Mysql base大约有13000行，更新时间大约需要40分钟。我认为

foreach ($result->GetProductBarcodeListResult->ProductBarcodeList->ProductBarcodeas $b)

每次都与远程服务器建立新连接，并将所有结果移动到自己的阵列中，但这无济于事。

请帮忙，如何更新更快？

更新 - mysqli，但它没有提供更多的速度。

public function getProductBarcodes() 
{
    global $db;
    try
    {
        $this->init();
        $request = new GetProductBarcodeListRequest();
        $params = new GetProductBarcodeList();
        $params->GetProductBarcodeListRequest = $request;
        $result = $this->soapClient->GetProductBarcodeList($params);
    if($result->GetProductBarcodeListResult->ProductBarcodeList->ProductBarcode)
    {
        $eanlist= array();
        foreach ($result->GetProductBarcodeListResult->ProductBarcodeList->ProductBarcode as $b)
        {
            $b->SupplierCode = mysqli_real_escape_string($db,"AC".$b->SupplierCode);
            $b->Barcode = mysqli_real_escape_string($db,$b->Barcode);   
            $eanlist[] = array("eancode" => $b->Barcode,"skuean" => $b->SupplierCode);
        }
        foreach ($eanlist as $eanrow) {
            mysqli_query($db, "
                    UPDATE _new_products
                    SET ean = '$eanrow[eancode]'
                    where sku = '$eanrow[skuean]'
                    ; ");  
        }
        echo "EAN UPDATE DONE!'n";
    }
    //echo "<pre>".     print_r($result,1) . '</pre>';
}
catch(SoapFault $e) {
    echo '<xmp>' . $this->soapClient->__getLastRequestHeaders()  .  $this->soapClient->__getLastRequest() .   '</xmp>';
    echo "<pre>".   print_r($e,1) . '</pre>';
}
}

尝试使用以下函数来更新单个列。取自这里

<?php
function bulkUpdateSingleColumn($table, $id_column, $update_column, array &$idstovals){
    $sql = "update $table set $update_column = CASE  ";
    foreach($idstovals as $id=>$val){
        $sql .= " WHEN '$id' THEN '$val' 'n";
    }
    $sql .= " END 
    WHERE $id_column in (" . implode(',', array_keys($idstovals)) . ")";
    //debugging info
    echo '<small>'.$sql.'</small>';
    $idstovals=array();
    //  db_query($sql);
}
$eanlist = array("sku1"=>"ean1","sku2"=>"ean2");
$table = '_new_products';
$id_column = 'sku';
$update_column = 'ean';
bulkUpdateSingleColumn($table, $id_column, $update_column,   $eanlist);
?>

我已经将

sku 从长文本更改为 varchar （255），并添加了索引。这是解决的问题