扩展文件名： CDbBICommand.php ，将文件放于 \protected\components 目录下，确定项目会自动引入这个目录的文件，然后用法：
StatVisitor 是 CActiveRecord 类的数据表模型

$bi = new CDbBICommand(new StatVisitor());
$bi->batchInsert([['name'=>123],['name'=>234]]);

扩展文件代码如下：

<?php

/**
 * class for sql batch insert
 * eg:
 * $bi = new CDbBICommand(new StatVisitor());
 * $bi->batchInsert([['name'=>123],['name'=>234]]);
 */
class CDbBICommand {

    /**
     * CDbBICommand constructor.
     * @param CActiveRecord $acModel
     */
    public function __construct($acModel) {
        $this->ac = $acModel;
        $this->tablename = $this->ac->tableName();
        $this->db = $this->ac->getDbConnection();
        $this->command = $this->db->createCommand();
    }

    public function batchInsert($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->db->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->db->quoteTableName($this->tablename)
                       . ' (' . implode(', ', $names) . ') VALUES ('
                       . implode(', ', $placeholders) . ')';
            } else {
                $sql .= ',(' . implode(', ', $placeholders) . ')';
            }
            $i++;
        }
        return $this->command->setText($sql)->execute($params);
    }
}

框架自带的批量插入方法，很少有网络资料：

$builder = Yii::app()->db->schema->commandBuilder; // 创建builder对象
$command = $builder->createMultipleInsertCommand('{{umeng_message}}', array( // umeng_message为数据库表
    array(// 格式为：'字段' => '值', 不包括主键ID，一个array为一条记录
        'msg_id' => $android['data']['task_id'],
        'detail' => $android['data']['json'],
        'msg_detail_id_fk' => $detailId
    ),
    array(
        'msg_id' => $android['data']['task_id'],
        'detail' => $android['data']['json'],
        'msg_detail_id_fk' => $detailId
    ),
));
$command->execute(); // 执行成功返回true

版权属于：Joyber

本文链接：https://blog.qqvbc.com/default/277.html

转载时须注明出处及本声明

标签: yii

添加新评论