推荐用存储过程更新数据,不会的话可以用以下方法。
项目中某个表中的数据需要维护,数据量巨大。
1.首先将要维护的数据筛选出来,编写脚本执行变量的sql更新操作。
2.csv文件中去批量更新数据,保存文件。
- <?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即可
- <?php
- 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;
- }