yii批量数据插入

yii框架批量插入数据有两种方法，第一种是循环多次插入和一次批量插入，第一种方法要注意插入数据中间有一次数据插入失败要注意回滚事务

循环插入数据

第一种方法

$model = new User();
$transaction=Yii::app()->db->beginTransaction();
try{
    foreach($data as $attributes)
    {
        $_model = clone $model; //克隆对象
        $_model->setAttributes($attributes);
        $_model->save();
    }
    $transaction->commit();
} catch(Exception $e){
    $transaction->rollBack();
}

第二种方法

$model = new User();
$transaction=Yii::app()->db->beginTransaction();
try{
    foreach($data as $attributes)
    {
        $model->isNewRecord = true;
        $model->setAttributes($attributes);
        $model->save() && $model->id=0;
    }
    $transaction->commit();
} catch(Exception $e){
    $transaction->rollBack();
}

批量插入数据

生成SQL：INSERT INTO users (id, name) VALUES (:id0, :name0),(:id1, :name1)

绑定参数：array(2) {[":id0"]=>int(1)，[":name0"]=>string(4) "John"}

<?php
/**
 * 批量插入
 * @param  String $table         表名
 * @param  Array  $array_columns 插入数据
 * @return Bool                  失败返回false，成功返回true
 */
function insertSeveral($table, $array_columns)
{
    $sql = '';
    $params = array();
    $i = 0;
    foreach ($array_columns as $columns) {
        $names = array();
        $placeholders = array();
        foreach ($columns as $name => $value) {
            if (!$i) {
                $names[] = $this->_connection->quoteColumnName($name);
            }
            if ($value instanceof CDbExpression) {
                $placeholders[] = $value->expression;
                foreach ($value->params as $n => $v)
                    $params[$n] = $v;
            } else {
                $placeholders[] = ':' . $name . $i;
                $params[':' . $name . $i] = $value;
            }
        }
        if (!$i) {
            $sql = 'INSERT INTO ' . $this->_connection->quoteTableName($table)
                . ' (' . implode(', ', $names) . ') VALUES ('
                . implode(', ', $placeholders) . ')';
        } else {
            $sql .= ',(' . implode(', ', $placeholders) . ')';
        }
        $i++;
    }
    return $this->setText($sql)->execute($params);
}
$rows = array(
    array('id' => 1, 'name' => 'John'),
    array('id' => 2, 'name' => 'Mark')
);
echo insertSeveral('users', $rows);

 zend framework实现

/**
     * 批量插入
     * @param  array $rows 记录数组
     * @return boolen      成功返回true，否则返回false
     * @todo insert into t_promotion_plan_span (start_time,end_time,plan_id) 
     * values (:start_time0,:end_time0,:plan_id0), (:start_time1,:end_time1,:plan_id1)
     */
    public function insertAll($rows) {
        try {
            $names = $values = $placs = array();
            $db = $this->getAdapter();
            $sql='';
            $i=0;
            //字段名数组
            $names = array_keys($rows[0]);
            foreach ($names as $key=>$value) {
                $names[$key] = '`'.$value.'`';
            }
            //占位符数组和值数组
            foreach ($rows as $row) {
                foreach ($row as $name=>$val) {
                    $placeholder = ":".$name."_".$i;
                    $temp[]=$placeholder;
                    $values[$placeholder]=$val;
                }
                $placs[] = "(".implode(',',$temp).")";
                $temp=array();
                $i++;
            }
            $sql = sprintf(
                "insert into %s (%s) values %s",
                '`'.$this->_name.'`',
                implode(',',$names),
                implode(',',$placs)
            );
            $db->query($sql,$values);
            return true;
        } catch (Exception $e) {
            return false;
        }
    }

 

ROWS
rows = array(2) {
  [0]=>
  array(2) {
    ["adpos_flag"]=>
    string(5) "pdgl2"
    ["adpos_id"]=>
    string(1) "1"
  }
  [1]=>
  array(2) {
    ["adpos_flag"]=>
    string(4) "pdgl"
    ["adpos_id"]=>
    string(1) "2"
  }
}

SQL
string(125) "insert into `cy_res_web_adpos_group` (`adpos_flag`,`adpos_id`) values (:adpos_flag_0,:adpos_id_0),(:adpos_flag_1,:adpos_id_1)"

VALUES
array(4) {
  [":adpos_flag_0"]=>
  string(5) "pdgl2"
  [":adpos_id_0"]=>
  string(1) "1"
  [":adpos_flag_1"]=>
  string(4) "pdgl"
  [":adpos_id_1"]=>
  string(1) "2"
}