推薦用存儲過程更新資料,不會的話可以用以下方法。
項目中某個表中的資料需要維護,資料量巨大。
1.首先将要維護的資料篩選出來,編寫腳本執行變量的sql更新操作。
2.csv檔案中去批量更新資料,儲存檔案。
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5ichR3cf52bjl2LcNXZnFWbp9CXt92YuUWelRXauYjM5cmbvlXZkVHavw1LcpDc0RHaiojIsJye.png)
<?php
//importcsv.php
set_time_limit(60);
$link = mysql_connect("localhost","root","orbit");
mysql_select_db("wms_site_ks01");
$filepath_unite = "./job_item_id.csv";
if (is_file($filepath_unite)) {
$handle_unite = @fopen($filepath_unite,"r");
$key=0;
while ($data_unite = fgetcsv($handle_unite, 1000, ",")) {
if($key>0){
echo "update stock_moves set job_item_id='".$data_unite['9']."' where stock_mv_id=".$data_unite['0'].";"."<br/>";
//mysql_query("update stock_moves set job_item_id='".$data_unite['9']."' where stock_mv_id=".$data_unite['0']) or die("invalid query: " . mysql_error());
}
$key++;
//print "<pre>";print_r($data_unite);die;
}
fclose($handle_unite);
}else {
die("file does not exist");
}
?>
例子二 加處理條數限制防止程式記憶體溢出或處理逾時,再加頁面自己重新整理即可解決,建立導入字段,導入後更新,導完後删除字段import即可
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5ichR3cf52bjl2LcNXZnFWbp9CXt92YuUWelRXauYjM5cmbvlXZkVHavw1LcpDc0RHaiojIsJye.png)
set_time_limit(0);
$sql = "select * from core_catalog where level=3 and import=0 limit 30";
$result = yii::app()->db->createcommand($sql)->queryall();
foreach ($result as $var3 => $key3) {
$url = "http://wbmall.bama555.com/api//props?cid=" . $key3['id'] . "&app_key=5318260b3d8f0036";
$ch = curl_init($url);
curl_setopt($ch, curlopt_returntransfer, true); // 擷取資料傳回
curl_setopt($ch, curlopt_binarytransfer, true); // 在啟用 curlopt_returntransfer 時候将擷取資料傳回
$output = curl_exec($ch);
$data = json_decode($output, true);
foreach ($data as $info) {
$sql = "insert into core_catalog_standard(id, catalog_id, pid, level, name,title,sort)
values({$info['id']}, {$info['gcat_id']}, 0, 1, 'stand_{$info['id']}', '{$info['name']}', {$info['listorder']});";
yii::app()->db->createcommand($sql)->execute();
$sql = 'update core_catalog set import=1 where id=' . $key3['id'];
yii::app()->db->createcommand($sql)->execute();
echo 'complate' . $level . "共:" . count($data);
echo "<script>settimeout(window.location.href=window.location.href, 3000);</script>";
exit;