php - 查询停止运行，而不是插入零值

我们有这个MySQL查询，我们要从date_time表中找到price_open和price并将这两个值更新为price_datetime表中的price_open和comment列：

UPDATE comment AS fc
    INNER JOIN price AS p
        ON  p.ticker_id = fc.ticker_id
        AND p.date_time =
            ( SELECT pi.date_time
              FROM price AS pi
              WHERE pi.ticker_id = fc.ticker_id
                AND pi.date_time >= fc.date_time
              ORDER BY pi.date_time ASC
              LIMIT 1
            )  SET
    fc.price_datetime = p.date_time,
    fc.price_open = p.price_open;

我们将其转换为PHP + MySQL，希望获得更高的效率和更快的过程：

<?php
    ob_flush();
    flush();
    usleep(160);

    $tickers =  array();

    $stmt = $mysqli->prepare("SELECT ticker_id, date_time FROM flaggedcomment order by ticker_id, date_time");
    $stmt->execute(); //Execute prepared Query
    $stmt->bind_result($tid, $dt);
    $arr_index = 0;
    while ($stmt->fetch() ) {
        $tickers[$arr_index] = array();
        $tickers[$arr_index]["id"] = $tid;
        $tickers[$arr_index]["dt"] = $dt;
        $arr_index++;
    }
    /* free result set */
    $stmt->free_result();
    $record_index = 0;
    $flaggedcomment_index = 0;
    $sql = "";
        // get total tickers
        $total_tickers = count($tickers);
         echo "Total records: " . $total_tickers . "<br />";

    foreach ($tickers as $ticker) { //fetch values

        $stmt = $mysqli->prepare("SELECT price_open, date_time FROM price WHERE ticker_id =? AND date_time >=? ORDER BY date_time ASC LIMIT 1;");

        $stmt->bind_param("is",$ticker["id"], $ticker["dt"]); // two params: one is integer, and other one is string

        $stmt->execute(); //Execute prepared Query

        $results = $stmt->get_result();
        $myrow =  $results->fetch_row();

            $set_string = "SET";
            // bind values
            $price_open = $myrow[0];
            $date_time = $myrow[1];

            // set initial insert query value
           $set_string .= " price_datetime='". $date_time ."'";
           $set_string .= ", price_open=". $price_open;
           $set_string .= " WHERE ticker_id=". $ticker["id"] ." AND date_time='" . $ticker["dt"] ."'";

            if($set_string != ""){
                $sql .= "UPDATE flaggedcomment ". $set_string . ";";
            }

            $idx = $record_index + 1;

            if(($record_index + 1) % 100 == 0){
            ?>
                    <script>
                        $('#page-wrap > h1').html("Processing Ticker id #" + <?= $ticker["id"]; ?> + " - Record #" + <?= $idx; ?>);
                    </script>
        <?php
                ob_flush();
                flush();

                usleep(160);
            }

            $record_index++;

        /* free result set */
        $stmt->free_result();
    } // end while fetch ticker id


        $update_flaggedcomment_qry = "LOCK TABLES flaggedcomment WRITE; ". $sql . "UNLOCK TABLES; ";
        echo $update_flaggedcomment_qry;
        //echo "<br />";
        if ($mysqli->multi_query($update_flaggedcomment_qry)) {
            // nothing
        } else {
            echo "Error updating record: " . $mysqli->error . "<br />";
            $mysqli->close();
            exit;
       }

    echo "<span style='color:blue;'> <b> Done. </b> </span>";

    ob_end_flush();

    exit;

?>

使用MySQL查询，如果两个表中的ticker_id和date_time都不匹配，则fc.price_datetime和fc.price_open列将显示0000-00-00 00:00:00和0.00值。但是，当执行PHP代码时，它会插入ticker_id和date_time的第一个“不匹配”并且不再继续，而不是插入零值。遗憾的是，我们花了很长时间才弄清楚如何修复它，我们使用的任何方法都无法解决此问题。

社区的任何帮助绝对值得赞赏。

谢谢。 :)

最佳答案

您不检查是否有价格返回的记录。因此，当该数组为null时，您的代码仅尝试选择结果数组的第一个和第二个元素。然后，您可能会出现一条语句，试图为空白price_datetime和price_open字段分配空白。使用price_datetime，您可以在空值附近加上引号，而mysql可能会解决这个问题，但是对于price_open，您不需要在预期数值附近提供引号。因此，您将收到无效的更新语句（类似于以下内容）：

UPDATE flaggedcomment price_datetime='', price_open= WHERE ticker_id=123 AND date_time='2016-01-01 00:00:00';

当您一次执行多个SQL语句进行更新时，我希望它在无效语句之后不会执行任何操作。

快速使用您的代码，然后执行以下操作。这将检查返回的行，如果未找到返回的行，则仅对要更新的2个字段使用默认值（类似零）。

<?php
ob_flush();
flush();
usleep(160);

$tickers =  array();

$stmt = $mysqli->prepare("SELECT ticker_id, date_time FROM flaggedcomment order by ticker_id, date_time");
$stmt->execute(); //Execute prepared Query
$stmt->bind_result($tid, $dt);
$arr_index = 0;
while ($stmt->fetch() )
{
    $tickers[$arr_index] = array();
    $tickers[$arr_index]["id"] = $tid;
    $tickers[$arr_index]["dt"] = $dt;
    $arr_index++;
}
/* free result set */
$stmt->free_result();
$record_index = 0;
$flaggedcomment_index = 0;
$sql = "";
// get total tickers
$total_tickers = count($tickers);
echo "Total records: " . $total_tickers . "<br />";

foreach ($tickers as $ticker)
{ //fetch values

    $stmt = $mysqli->prepare("SELECT price_open, date_time FROM price WHERE ticker_id =? AND date_time >=? ORDER BY date_time ASC LIMIT 1;");

    $stmt->bind_param("is",$ticker["id"], $ticker["dt"]); // two params: one is integer, and other one is string

    $stmt->execute(); //Execute prepared Query

    $results = $stmt->get_result();
    if ($myrow =  $results->fetch_assoc())
    {
        $price_open = $myrow['price_open'];
        $date_time = $myrow['date_time'];
    }
    else
    {
        $price_open = 0.00;
        $date_time = "0000-00-00 00:00:00";
    }

    $sql .= "UPDATE flaggedcomment SET";
    $sql .= " price_datetime='". $date_time ."'";
    $sql .= ", price_open=".$price_open;
    $sql .= " WHERE ticker_id=". $ticker["id"] ." AND date_time='" . $ticker["dt"] ."';";

    $idx = $record_index++;

    if(($record_index + 1) % 100 == 0)
    {
        ?>
        <script>
        $('#page-wrap > h1').html("Processing Ticker id #" + <?= $ticker["id"]; ?> + " - Record #" + <?= $idx; ?>);
        </script>
        <?php
        ob_flush();
        flush();

        usleep(160);
    }

    $record_index++;

    /* free result set */
    $stmt->free_result();
} // end while fetch ticker id


$update_flaggedcomment_qry = "LOCK TABLES flaggedcomment WRITE; ". $sql . "UNLOCK TABLES; ";
echo $update_flaggedcomment_qry;
//echo "<br />";
if ($mysqli->multi_query($update_flaggedcomment_qry)) {
    // nothing
}
else
{
    echo "Error updating record: " . $mysqli->error . "<br />";
    $mysqli->close();
    exit;
}

echo "<span style='color:blue;'> <b> Done. </b> </span>";

ob_end_flush();

exit;

?>

但是我怀疑，围绕一个查询的结果循环并针对php中的每一行执行另一个查询会比构造良好的单个更新查询慢。

如果您确实希望像现在这样循环遍历结果，则可以更快地创建一个tmp表并将行插入其中（因为您可以使用一条语句插入数百行），然后使用将其连接到tmp表的单个更新语句

编辑-如果可以发布表声明和一些示例数据，我将尝试在单个SQL语句中进行操作。

第一次尝试（未经测试）将是：-

UPDATE comment AS fc
INNER JOIN price AS p
ON  p.ticker_id = fc.ticker_id
INNER JOIN
(
    SELECT *
    FROM
    (
        SELECT fc.ticker_id,
                MIN(pi.date_time) AS date_time
        FROM comment AS fc
        INNER JOIN price AS pi
        ON pi.ticker_id = fc.ticker_id
        AND pi.date_time >= fc.date_time
        GROUP BY fc.ticker_id
    ) sub1
) sub0
ON p.ticker_id = sub0.ticker_id
AND p.date_time = sub0.date_time
SET fc.price_datetime = p.date_time,
    fc.price_open = p.price_open;

这是使用多余的看似多余的子查询来希望绕过MySQL对更新也在子查询中使用的表的限制。