php - 在 PHP 和 MySql 中，一些记录不会随着批量更新而更新

Question

我有一个用于批量更新的 php 脚本，在我的数据库中存在 90,585 行但只更新了 85,282 行，我不知道为什么，这是我的脚本:

//limit the select to 100
//Bulk update is very slow, is you set a limit very high server crash
$limit = 100;
//$messages = array();

$updated_posts = 0;

//maybe there are better solutions for this
for ($i=0;$i<=$totalMsg;$i+=$limit)
{

    $get_posts = mysqli_query($conn,
    "SELECT id_msg, body
    FROM " . $to_prefix ."messages
    WHERE id_msg != 0
    LIMIT " . $i ."," . $limit);

    //The post Array
    $messages = array();
    while($row = mysqli_fetch_assoc($get_posts))
    {
        $messages[$row['id_msg']] = array(
            'body' => fixBBCTags($row['body']),
        );
    }

    //update data!, good luck!!
    bulkUpdate($conn,$to_prefix."messages","id_msg","body",$messages);

    $updated_posts += mysqli_affected_rows($conn);

}// for loop

这是 bulkUpdate() 函数:

function bulkUpdate($conn, $table, $id_column, $update_column, array &$idstovals)
{
global $error; 

//prepare the Bulk Update SQL
$sql = "update " . $table . " set " . $update_column . " = CASE " . $id_column ;

foreach($idstovals as $id => $val)
{
    $sql .= " WHEN " . "'" . $id . "'" . " THEN " . "'" . mysqli_real_escape_string($conn,$val[$update_column]) . "'" . " \n";
}

$sql .= " END 
WHERE " . $id_column. " in (" . implode(',', array_keys($idstovals)) . ")";

//reset the array
//$idstovals=array();

//try update the bulk
$update = mysqli_query($conn,$sql);

if(mysqli_error($conn))
    $error = mysqli_error($conn);
}

必须更新所有行，有更好的解决方案吗？

问候。

Answer 1

当你连续写入相同的数据时，MySQL不写入这条记录

MariaDB [test]> create table r (id integer, PRIMARY KEY (id) );
Query OK, 0 rows affected (0.15 sec)

MariaDB [test]> insert into r (id)  VALUES (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> update r set id=4 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> update r set id=4 where id=4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0