Yii: 批量插入数据的扩展类简单实现

来源：互联网发布：桌面办公软件编辑：程序博客网时间：2024/04/28 00:30

MySQL INSERT语句允许插入多行数据，如下所示：

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

那么要实现批量插入，主要的任务就是按照列顺序，把数据组装成上述格式即可，可以使用sprintf和vsprintf函数来实现。

下面是一个实现批量插入的Yii扩展类的简单示例（支持VARCHAR类型数据）：

<?php/** * class for sql batch insert */class CDbBICommand extends CDbCommand{    /** @var CActiveRecord $class */    private $class;    /** @var string $insert_tpl */    private $insert_tpl = "insert into %s(%s) ";    /** @var string $value_tpl */    private $value_tpl = "(%s)";    /** @var string $query */    public $query;    /** @var CDbColumnSchema[] $columns */    private $columns;    /** @var boolean $fresh */    private $fresh;    /** @param CActiveRecord $class     *  @param CDbConnection $db     */    public function __construct($class,$db){      $this->class = $class;      $this->createtpl();      parent::_construct($db);    }    private function createtpl(){      $this->fresh = true;      $value_tpl = "";      $columns_string = "";      $this->columns = $this->class->getMetaData()->tableSchema->columns;      $counter = 0;      foreach($this->columns as $column){        /** @var CDbColumnSchema $column */        if($column->autoIncrement){          $value_tpl .= "0";        }else{          $value_tpl .= "\"%s\"";        }        $columns_string .= $column->name;        $counter ++;        if($counter != sizeof($this->columns)){          $columns_string .= ", ";          $value_tpl .= ", ";        }      }      $this->insert_tpl = sprintf($this->insert_tpl, $this->class->tableName(), $columns_string);      $this->value_tpl = sprintf($this->value_tpl, $value_tpl);    }    /**      *  @param CActiveRecord $record     */    public function add($record){      $values = array();      $i = 0;      foreach($this->columns as $column){        if($column->autoIncrement){          continue;        }        $values[$i] = $this->class->{$column->name};        $i ++;      }      if(!$this->fresh){        $this->query .= ",";      }else{        $this->query = "values";      }      $this->fresh = false;      $this->query .= vsprintf($this->value_tpl, $values);      return true;    }    public function execute(){      $this->setText($this->insert_tpl." ".$this->query);          return parent::execute();    }}

使用方法是通过add方法逐个加入数据，然后调用execute执行。

by iefreer

0 0