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

Question

我们有一个 MySQL 查询，我们希望从 price 表中查找 date_time 和 price_open 并将这两个值更新到 comment 表中的 >price_datetime 和 price_open 列:

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 的第一个“不匹配”时停止，并且无法继续。我们花了很长时间来研究如何解决这个问题，不幸的是，我们使用的任何方法都无法解决这个问题。

非常感谢社区的任何帮助。

谢谢。 :)

Answer 1

您不检查是否有从 price 返回的记录。因此，当该数组为空时，您的代码只是尝试选择结果数组的第一个和第二个元素。然后，您可能会得到一条试图为每个 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 表并向其中插入行可能会更快(因为您可以使用一条语句插入数百行)，然后更新您的 < em>flagedcomment 表，带有将其连接到 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 对更新也在子查询中使用的表的限制。