php mysql 游标

Question

我需要创建一个 MySQL 游标来跟踪我在遍历“巨大”（数百万个整体）表时当前所处的行号。

示例数据库表：

CREATE TABLE  test (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
someText TEenter code hereXT NOT NULL
) ;

如果该表有 1,000,000 个条目；我执行以下查询：

select * from test where id >= 50;

然后我根据需要在 php 脚本中处理数据（限制为 1 分钟）。我如何跟踪我已经遍历“测试”表的哪一行？

Answer 1

// use a PHP session to store the id (could also use cookies...)
session_start();

// your 1 minute timeout
set_time_limit(60);

// query your results (may even put a known-out-of-reach limit on the
// query just to make sure you're not always pulling all the entries every
// reload (that would each up your timeout alone, depending)
$lastID = 0; // lowest possible ID value (e.g. MIN(id) )
if (session_is_registered('last_select_id'))
{
  $lastID =(int)$_SESSION['last_select_id'];
}
else
{
  session_register('last_select_id');
  $_SESSION['last_select_id'] = $lastID;
}
$dbResult = mysql_query("SELECT * FROM test WHERE id>{$lastID} ORDER BY id"/* LIMIT 10000 */);
if ($dbResult)
{
  while (($row = mysql_fetch_row($dbResult)) !== false)
  {
    // perform processing

    // mark as last processed (depending how many steps,
    // you may decide to move this from end to somewhere
    // else, just sh*t luck where your timeout occurs)
    $_SESSION['last_select_id'] = $row['id'];
  }
}

// it reached the end, save to assume we can remove the session variable
session_unregister('last_select_id');

我只能按照你告诉我的去做，尽管我觉得这应该本地受到限制，而不仅仅是等待 PHP 超时。

编辑另一种扩展批处理思想的方法是更改​​表并添加可以更新的“已处理”列。

EDIT2 另外，要小心。我确实设置/取消设置会话值。因此，如果您刷新，即使页面加载成功而不是超时错误，也可能会陷入无限循环（$lastID 不会看到会话变量，它将再次从 1 开始，然后继续执行再次）。