常用批量sql操作 - Githubissues

批量查询

$conds = array('order_id' => $value['order_id']);
$purchase_handle = $zerobuy_cmi->get_purchase_handle($conds);
function get_purchase_handle($conds) {
    if (count($conds) < 1) {
        return false;
    }
    $wheres = '';
    foreach ($conds as $key => $value) {
        $wheres .= "AND `$key` = '$value'";
    }
    $sql = "SELECT `order_status`, `purchase_channel` FROM `zero_goods` WHERE 1 = 1 $wheres LIMIT 1";
    return $this->mdb_public->fetch($sql);
}

批量插入

$sql = "INSERT INTO `user_credit` (`id`, `requestno`, `uid`, `loan_id`, `start_amount`, `amount`) VALUES ";
$values = array();
foreach ($creditList as $credit) {
    $values[] = sprintf("(%d,'%s',%d,%d,%f, %f)", $credit['creditId'],$credit['requestNo'], $credit['uid'], $loanId, $credit['startAmount'],$credit['startAmount']);
}
$sql = $sql . join(",", $values);
if (!$this->mdb_public->query_or_rollback($sql, true)) {
    return false;
}
return $this->mdb_public->query_affected_rows();

批量更新

1. 
function update($tablename, $array, $where = '') {
    $this->check_fields($tablename, $array);
    if ($where) {
        $sql = '';
        foreach ($array as $k=>$v) {
            $sql .= ", `$k`='$v'";
        }
        $sql = substr($sql, 1);
        $sql = "UPDATE `$tablename` SET $sql WHERE $where";
    } else {
        $sql = "REPLACE INTO `$tablename`(`".implode('`,`', array_keys($array))."`) VALUES('".implode("','", $array)."')";
    }
    return $this->query($sql);
}

2.
$display_order = array(
    1 => 4,
    2 => 1,
    3 => 2,
);
$ids = implode(',', array_keys($display_order));
$sql = "UPDATE categories SET display_order = CASE id ";
foreach ($display_order as $id => $ordinal) {
    $sql .= sprintf("WHEN %d THEN %d ", $id, $ordinal);
}
$sql .= "END WHERE id IN ($ids)";

diffnest / myblog

常用批量sql操作 #2