/** * 测试 */ private function test() { //update tableTest set field1='fvalue1', num=num+1 where (id='16') ; $update = \HuiLib\Db\Query::update()->table('tableTest')->where(Where::createPair('id', '16')); $update->sets(array('field1' => 'fvalue1', 'field2' => 'fvalue2', 'num' => array('plain' => 'num=num+1'))); //$update->query(); echo $update->toString(); }
$insert->fields(array('field1', 'field2'))->values(array('fvalue1', 'fvalue2')); //insert into test (field1, field2) values ('fvalue1', 'fvalue2'), ('fvalue11', 'fvalue22') ; $insert->values(array('fvalue11', 'fvalue22')); //附加 前面的 //2.2.2 关联数组插入模式 //insert into test (field1, field2) values ('fvalue1', 'fvalue2'), ('fvalue11', 'fvalue22') ; $insert->kvInsert(array('field1' => 'fvalue1', 'field2' => 'fvalue2'))->values(array('fvalue11', 'fvalue22')); //2.2.3 Duplicate Key Update模式 //需要注意dupFields和dupValues的关联性,弱耦合 $insert->enableDuplicate(true); //开启自动Dup更新模式 //insert into test set field1='fvalue1', field2='fvalue2' on duplicate key update field1='newfvalue1', num=num+1 ; $insert->fields(array('field1', 'field2'))->dupFields(array('field1', 'num'))->values(array('fvalue1', 'fvalue2'), array('field2' => 'newfvalue1', array('plain' => 'num=num+1'))); //2.2.4 通过row对象数组或rowset批量插入 $insert->batchSaveRows(); //2.3 更新部分 //获取更新对象 //update tableTest set field1='fvalue1', num=num+1 where (id='16') ; $update = \HuiLib\Db\Query::update('tableTest'); //支持两种更新Set模式,KV模式和Plain模式 $update->sets(array('field1' => 'fvalue1', 'num' => array('plain' => 'num=num+1'))); //绑定条件 $update->where(Where::createPair('id', '16')); //2.4 删除部分 //获取删除对象 //delete from tableTest where (id='2') limit 10 ; $delete = \HuiLib\Db\Query::delete('tableTest'); //设置删除条件 $delete->where(Where::createPair('id', '2')); //设置删除行数 $delete->limit(10);
/** * 通过关联数据插入某表一行数据 * * @param array $setArray 插入数组 */ public function update($setArray, \HuiLib\Db\Query\Where $where) { $update = Query::update(static::TABLE); if ($this->dbAdapter !== NULL) { $update->setAdapter($this->dbAdapter); } return $update->sets($setArray)->where($where)->query(); }
/** * 获取Query更新对象 * * @throws Exception * @return Query */ protected function getSaveQuery() { $tableInstance = $this->tableInstance; if ($tableInstance === NULL || $tableInstance::TABLE === NULL) { throw new Exception('Table class constant TABLE has not been set.'); } $table = $tableInstance::TABLE; if ($this->newRow) { //新行 // PRIMAY_IDKEY 可以设置为默认值0,自动增长的也会自动更新;不然有些非自动增长的会有问题 $insert = Query::insert($table); if ($this->dbAdapter !== NULL) { $insert->setAdapter($this->dbAdapter); } if ($this->duplicateCreate) { $insert->enableDuplicate(); //dup的时候要注意去除主键为0的情况 $duplicate = $this->data; unset($duplicate[static::PRIMAY_IDKEY]); $insert->dupFields(array_keys($duplicate)); } return $insert->kvInsert($this->data); } else { if (!$this->editData) { //无修改,直接返回成功 return FALSE; } $primaryValue = $this->oldPrimaryIdValue === NULL ? $this->data[static::PRIMAY_IDKEY] : $this->oldPrimaryIdValue; $update = Query::update($table); if ($this->dbAdapter !== NULL) { $update->setAdapter($this->dbAdapter); } return $update->sets($this->editData)->where(Where::createPair(static::PRIMAY_IDKEY, $primaryValue))->limit(1); } }