php - 纯 SQL 与 PHP While 循环更快地执行更新

Question

我目前正在开发一个系统来管理我的 Magic The Gathering 系列。我已经编写了一个脚本来更新所有卡片的定价，使用 WHILE 循环来执行主要更新，但是在我的 i5 笔记本电脑上更新所有 28,000 行需要大约 9 个小时。我感觉同样的事情可以在没有 While 循环的情况下使用 MySQL 查询来完成，而且速度会更快。

我的脚本首先创建一个与我的主库存表具有相同结构的临时表，然后通过 csv 文件将新价格复制到临时表中。然后我使用 While 循环通过 card_name 和 card_set 将临时表中的卡片与库存表进行比较以进行更新。

我的问题是，纯 mysql 查询会比使用 while 循环更快吗？你能帮我构造一下吗？任何帮助将非常感激。这是我的代码。

<?php

set_time_limit(0);

echo "Prices Are Updating. This can Take Up To 8 Hours or More";

include('db_connection.php');

mysql_query("CREATE TABLE price_table LIKE inventory;");

//Upload Data
mysql_query("LOAD DATA INFILE 'c:/xampp/htdocs/mtgtradedesig/price_update/priceupdate.csv'
INTO TABLE price_table FIELDS TERMINATED BY ',' ENCLOSED BY '\"' (id,     card_name, card_set, price)");

echo mysql_error();

//UPDATE PRICING
//SELECT all from table named price update
$sql_price_table = "SELECT * FROM price_table";
$prices = mysql_query($sql_price_table);

//Start While Loop to update prices. Do this by putting everything from price table into an array and one entry at a time match the array value to a value in inventory and update.

while($cards = mysql_fetch_assoc($prices)){
    $card_name = mysql_real_escape_string($cards['card_name']);
    $card_set = mysql_real_escape_string($cards['card_set']);
    $card_price = $cards['price'];
    $foil_price = $cards['price'] * 2;

    //Update prices for non-foil in temp_inventory
    mysql_query("UPDATE inventory SET price='$card_price' WHERE card_name='$card_name' AND card_set='$card_set' and foil ='0'");
    //Update prices for foil in temp_inventory
    mysql_query("UPDATE inventory SET price='$foil_price' WHERE card_name='$card_name' AND card_set='$card_set' and foil ='1'");

}

mysql_query("DROP TABLE price_table");

unlink('c:/xampp/htdocs/mtgtradedesign/price_update/priceupdate.csv');

header("Location: http://localhost/mtgtradedesign/index.php");

?>

Answer 1

最简单的补救措施是在表之间执行连接，并立即更新所有行。然后您只需要运行两个查询，一个用于箔，一个用于非箔。您可以将其完成，但这会变得更加复杂。

UPDATE inventory i
JOIN price_table pt
ON (i.card_name = pt.card_name AND i.card_set = pt.card_set)
SET i.price = pt.card_price WHERE foil = 0;

实际上并没有对此进行测试，但它通常应该是您要查找的内容。同样在运行这些之前尝试使用 EXPLAIN 来查看连接性能有多糟糕。您可能会受益于向表中添加索引。

附带说明(这不是你的问题)但是 mysql_real_escape_string 已被弃用，通常你不应该使用任何内置的 php mysql 函数，因为它们都是不安全的。 Php docs建议改用 PDO。