php+mysql数据库批量根据条件快速更新、连表更新sql实现

数据库  /  管理员 发布于 1年前   390

php中数据库批量根据条件快速更新：

<?php
$db = new ran_pdo("qx_yisdk_user",true); 
$tables = [
    "yisdk_user",
];
foreach ($tables as $tab) {
    $count_sql = 'select count(*) c from yisdk_user where last_login_time>=1602518400 and uuid!=MD5(concat("new_",CONCAT_WS("_",channel,pf_game_id,user_id)))'; 
    $row = $db->get_one($count_sql);
    // 需要执行的条数
    $count = !empty($row["c"]) ? $row["c"] : 0;
    // 每次执行的条数 1000
    $do_num = 100;
    // 需要执行的次数
    $dc = ceil($count/$do_num);
    if($dc) for ($i=0; $i < $dc; $i++) { 
        $p = $i * $do_num;
        $sql = "select uuid,MD5(concat('new_',CONCAT_WS('_',channel,pf_game_id,user_id))) as new_uuid from yisdk_user where last_login_time>=1602518400 and uuid!=MD5(concat('new_',CONCAT_WS('_',channel,pf_game_id,user_id))) limit {$p},{$do_num}";
        $data = $db->get_all($sql);
        $update_sql = "UPDATE {$tab} a JOIN  (";
        $k = 0;
        foreach ($data as $key => $val) {
                $k = 1;
                $update_sql .= " SELECT '{$val['uuid']}' as uuid,'{$val['new_uuid']}' AS new_uuid UNION";
        }
        $sql = rtrim($update_sql,"UNION"); 
        $sql .= ") b USING(uuid) SET a.uuid=b.new_uuid";
        echo $sql;die;
        if($k){
            $db->query($sql);
        }
        sleep(1);
    }
}
?>

在mysql数据表中连表更新sql：

UPDATE ucenter_user_money a, (
select user_id,openid,unionid from ucenter_user_details where openid!=''
) b 
SET a.openid = b.openid,a.unionid=b.unionid 
WHERE a.user_id = b.user_id

有兴趣的可以自行测试，或者改成你的业务表。