更新多行或在数据库中记录

If I have a script like this:

如果我有这样的脚本：

$sql = "SELECT * FROM table WHERE Status='0'";
$query = mysql_query($sql);

    while ($row = mysql_fetch_array($query)) 
       $id = $row['ID'];

           //.........

       if($process->send()) { //after sent
         mysql_query("UPDATE table 
                      SET Status ='1' 
                      WHERE ID = '$id'"); 
       }
    }

So it will update each row when process is done.But if I have more than ten thousand records with the Status='0', the update will become slow .

因此，当进程完成时它将更新每一行。但是如果我有超过一万条状态为“0”的记录，则更新将变慢。

So is there any better way to update the record? I can't update all with one single query since I need to know whether each process is done or not.

那么有更好的方法来更新记录吗？我不能用一个查询更新所有查询，因为我需要知道每个进程是否完成。

Thank you.

谢谢。

4 个解决方案

#1

Add all successful ones to an array and simply commit all of those simultaneously.

将所有成功的数据添加到数组中，然后同时提交所有这些数据。

if ($process->send()) {
    $done[] = $id;
}

and a bit later:

稍后：

mysql_query('UPDATE table SET Status=1 WHERE ID IN ('.implode(',', $done).')');

#2

Depending on the version of MySQL you are using you might consider setting up a function that has the procedure stored that way you can offset this process to the Database so that it doesn't delay your script in running.

根据您使用的MySQL版本，您可能会考虑设置一个存储过程的函数，以便将此过程偏移到数据库，这样它就不会延迟脚本的运行。

Documentation for Create Procedure/Create Function: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

创建过程/创建函数的文档：http：//dev.mysql.com/doc/refman/5.0/en/create-procedure.html

#3

If the UPDATE query is executing slow, you can use the LOW_PRIORITY keyword for MyISAM storage engine:

如果UPDATE查询执行缓慢，则可以对MyISAM存储引擎使用LOW_PRIORITY关键字：

http://dev.mysql.com/doc/refman/5.0/en/update.html

http://dev.mysql.com/doc/refman/5.0/en/update.html

Example:

例：

UPDATE LOW_PRIORITY SET Status = 1 WHERE id = someid;

UPDATE LOW_PRIORITY SET Status = 1 WHERE id = someid;

#4

You have to make a stored procedure for that code. It works very fast

您必须为该代码创建存储过程。它工作得非常快

#1