遍历MYSQL数据库并替换一列中每条记录中的数据

Question

在清除所有非数字之后，我有一种非常低效的方式来更新我数据库中的电话号码。

$san_phone = mysql_query('SELECT * FROM table');

while ($row = mysql_fetch_array($san_phone)) {
    $row['phone_clean'] = preg_replace('#[^\d]#', '', $row['phone']);
    echo $row['id'] . ' - ' . $row['phone_clean'] . '<br>';

    mysql_query("UPDATE table SET phone = " . $row['phone_clean']  . " WHERE id = " . $row['id']);
}

循环的更新部分导致我在2400条记录中只有400条记录超时。很明显我做错了什么，所以在教育我的时候要温文尔雅。;)

Answer 1

首先，停止使用mysql_函数，因为它们是being deprecated函数。请改用mysqli_或PDO函数。

你用来UPDATE你的记录的方法效率低下。相反，您应该创建一个临时表，在单个查询中对新记录执行INSERT操作，最后运行UPDATE查询来替换数据。

您可以从以下内容开始：

$san_phone = mysql_query('SELECT id, phone FROM table');
$insertArray = array();

while ($row = mysql_fetch_array($san_phone)) {
    $phone_clean = preg_replace('#[^\d]#', '', $row['phone']);
    echo $row['id'] . ' - ' . $row['phone_clean'] . '<br>';

    $insertArray[] = "(" . $row[id] . ", '" . $phone_clean . "')";
}

$insertQuery = "INSERT INTO tempTable (id, phone) VALUES ";
$insertQuery = implode(", ", $insertArray);
mysql_query($insertQuery);

我已经做了一个快速演示来演示这个过程。t1是原始表，t2是包含要替换的数据的临时表。

See it in action

Answer 2

您可以使用类似以下用户定义的函数：

http://www.mysqludf.org/lib_mysqludf_preg/index.php#PREG_REPLACE_SECTION

或https://launchpad.net/mysql-udf-regexp

并将查询重写为：

UPDATE table
SET phone = PREG_REPLACE('#[^\d]#', '', phone);

Answer 3

嗯，对数据库的多次调用会导致速度下降，而且(根据我的经验)，这是一个痛苦的过程。即使是两次访问数据库也会导致一次以上的明显延迟。

要解决此问题，您需要最小化对数据库的调用，这意味着在一次调用中执行尽可能多的操作。为此，请尝试将其重写为单个SQL更新，其中替换逻辑位于SQL本身中。这意味着只需访问数据库一次，速度就会有巨大的提高。

这说起来容易做起来难，因为据我所知，MySQL没有正则表达式字符串替换函数。你可以尝试一个变通方法，或者查看一些UDF，另一个答案提供了其中一个的链接。我建议你调查一下这个问题。