вставка данных с апдейтом прямым запросом SQL
@param $data - массив вставляемых данный, вставка будет прозводится пакетами размером указанным в константе BATCH
@throws \yii\db\Exception
421482
public function manualInsertWithUpdate( $data )
    {
        $owner = $this->owner;
        $table_name = $owner::tableName();
        $keys_arr = array_keys($data[0]);
        // найдем те поля которые не являются ключами. Их нужно будет при дубляже апдейтить
        $fields_arr_to_update = array_diff( $keys_arr, $this->keys );

        $query_update = ' on duplicate key update ';
        foreach ($fields_arr_to_update as $field) {
            $query_update .= "[[{$field}]] = values([[{$field}]]),";
        }
        // удалим последнюю запятую
        $query_update = substr($query_update, 0, strlen($query_update) - 1);

        // запросы будем выполнять пакетами
        // размер пакета установлен в константе
        // разобъем массив на пакеты и будем их проходить
        $data = array_chunk($data, $this->batch);
        foreach ($data as $current_batch_array) {

            //воспользуемся пакетной вставкой от фреймворка
            $query_insert = Yii::$app->db->createCommand()->batchInsert($table_name, $keys_arr, $current_batch_array)->sql;

            // добавим фрагмент с апдейтом при дубляже
            $query = "{$query_insert} {$query_update}";
            // \common\components\CustomVarDamp::dumpAndDie($query);
            Yii::$app->db->createCommand($query)->execute();

        }
    }
996562
public function query(string $sql, $data = null) {
		$connection = $this->pool->getConnection();
		$result = null;
		$tryAgain = true;
SQL_START_EXECUTE:
		if (is_array($data) && count($data) > 0) {
			if (strpos($sql, ':') !== false) {
				$bind = [];
				$sql = preg_replace_callback('/:(\w+)\b/', function ($matches) use (&$data, &$bind) {
					$bind[] = $data[$matches[1]];
					return '?';
				}, $sql);
				$data = &$bind;
			}
			try {
				$st = $connection->prepare($sql);
				if ($st === false) {
					goto SQL_TRY_AGAIN;
				}
				if (is_object($st)) {
					$result = $st->execute($data);
				} else {
					$result = $connection->execute($data);
				}
			} catch (\Throwable $e) {
				goto SQL_TRY_AGAIN;
			}
			if ($result === false) {
				goto SQL_TRY_AGAIN;
			}
			goto SQL_SUCCESS_RETURN;
		} else {
			try {
				$result = $connection->query($sql);
			} catch (\Throwable $e) {
				goto SQL_TRY_AGAIN;
			}
			if ($result === false) {
				goto SQL_TRY_AGAIN;
			}
			goto SQL_SUCCESS_RETURN;
		}
SQL_TRY_AGAIN:
		if (($connection->errno === 2006 || $connection->errno === 2013) && $tryAgain) {
			$tryAgain = false;
			$connection = $this->pool->reconnect($connection);
			goto SQL_START_EXECUTE;
		} else {
			$error = $connection->error;
			$errno = $connection->errno;
			$this->pool->freeConnection($connection);
			throw new DBException($error, $errno);
		}
SQL_SUCCESS_RETURN:
		if ($result === true) {
			$result = [
				'_affected_rows' => $connection->affected_rows
			];
			if (stripos($sql, 'insert') === 0) {
				$result['_insert_id'] = $connection->insert_id;
			}
		}
		$this->pool->freeConnection($connection);
		return $result;
	}
602963
public function sets($data, $params)
    {
        if (empty($params))
        {
            throw new \Exception("Model sets params is empty!");
        }
        $selectdb = new SelectDB($this->db);
        $selectdb->from($this->table);
        $selectdb->put($params);
        return $selectdb->update($data);
	}
1132133
public function query($sql)
    {
        if (!$this->DB) {
            throw  new Exception('您还没有连接数据库', Exception::CODE_DATABASE_ERROR);
        }
        $sql = str_replace('@#_', $this->prefix, $sql);
        /**
         * 只获取sql
         */
        if ($this->justSql) {
            return true;
        }
        $this->lastQueryAt = time();
        return $this->DB->query($sql);
    }
1137179
public function run($id, $data)
    {
        $data = (new RunBeforeSave($this->dic))->run($data);

        $check_auth_id = ORM::for_table('users')
            ->select('id')
            ->where_equal('auth_id', $data['auth_id'])
            ->where_not_equal('id', $id)
            ->find_one();
        if (is_object($check_auth_id)) {
            return Response::error400('Пользователь с такими данными для входа уже существует');
        }

        $user = ORM::for_table('users')
            ->find_one($id);
        if (is_object($user)) {
            $user->auth_id = $data['auth_id'];
            $user->save();
            if (is_object($user) && isset($user->id)) {
                $info = ORM::for_table('info_users')
                    ->where_equal('user_id', $id)
                    ->find_one();
                if (is_object($info)) {
                    if (isset($data['info_users']) && is_array($data['info_users']) && count($data['info_users']) > 0) {
                        foreach ($data['info_users'] as $key => $value) {
                            $info[$key] = $value;
                        }
                    }

                    if (!isset($data['roles']) || !is_array($data['roles'])) {
                        $data['roles'] = [];
                    }

                    $info->roles = json_encode($data['roles']);
                    $info->updated_at = $this->dic['datetimenow'];
                    $info->save();
                    if (is_object($info) && isset($info->id)) {
                        /** @var DataChangeLog $data_change_log */
                        $data_change_log = $this->dic['datachangelog'];
                        $data_change_log->insert('users', 'update', $id, $data);

                        $data['id'] = $id;
                        $data['last_action_date'] = $this->getLastActionDate($id);

                        return (new RunAfterSave($this->dic))->run($data);
                    } else {
                        return Response::error500('Произошла ошибка при изменении информации пользователя, попробуйте ещё раз');
                    }
                } else {
                    return Response::error404('Информация о пользователе не найдена');
                }
            } else {
                return Response::error500('Произошла ошибка при изменении пользователя, попробуйте ещё раз');
            }
        } else {
            return Response::error404('Пользователь не найден');
        }
    }
447302
public static function insert(array $data)
    {
        $self = static::getInstance();

        $data = static::filterColumns($data);

        if (!\count($data)) {
            throw new DbException(
                "Invalid field names of table `{$self->name}`. Please check use of `insert()` method"
            );
        }

        $table = DbProxy::quoteIdentifier($self->name);

        $sql = "INSERT INTO $table SET " . implode(',', self::prepareStatement($data));
        $result = DbProxy::query($sql, array_values($data));
        if (!$result) {
            return null;
        }

        /**
         * If a sequence name was not specified for the name parameter, PDO::lastInsertId()
         * returns a string representing the row ID of the last row that was inserted into the database.
         *
         * If a sequence name was specified for the name parameter, PDO::lastInsertId()
         * returns a string representing the last value retrieved from the specified sequence object.
         *
         * If the PDO driver does not support this capability, PDO::lastInsertId() triggers an IM001 SQLSTATE.
         */
        return DbProxy::handler()->lastInsertId($self->sequence);
    }
528201
protected function transform($data)
    {
        if (!$data instanceof GetSchema\Schema) {
            throw new Exception('Data is not a Schema.');
        }

        $definition = $data->getTableDefinition($this->fromExpr);
        $defSchema = $definition->getSchema();
        $parser = new PHPSQLParser(false, false, [Options::CONSISTENT_SUB_TREES => true]);
        $query = new CreateQuery(new Query($defSchema, $parser->parse($defSchema)));
        $createExpr = $query->getObjectExpression();
        $indices = $createExpr->getIndices();

        $returnData = [];

        /* @var  $index Query\IndexExpression */
        foreach ($indices as $index) {
            foreach ($index->getColumns() as $columnIndex => $column) {
                $row = self::$tplData;
                $row[self::KEY_TABLE] = $row[0] = $definition->getName();
                $row[self::KEY_NON_UNIQUE] = $row[1] = $index->getNonUnique();
                $row[self::KEY_NAME] = $row[2] = $index->getKeyName();
                $row[self::KEY_SEQ_IN_INDEX] = $row[3] = $columnIndex + 1;
                $row[self::KEY_COLUMN_NAME] = $row[4] = $column->getColumnName();
                $row[self::KEY_NULL] = $row[9] = $column->getColumnType()->isNullable() ? 'YES' : '';

                $returnData[] = $row;
            }
        }

        return $returnData;
    }
161638
public function run($data)
    {
        $data = (new RunBeforeInsert($this->dic))->run($data);

        $check_auth_id = ORM::for_table('users')
            ->select('id')
            ->where_equal('auth_id', $data['auth_id'])
            ->find_one();
        if (is_object($check_auth_id)) {
            return Response::error400('Пользователь с такими данными для входа уже существует');
        }

        $new_user = ORM::for_table('users')->create();
        $new_user->auth_id = $data['auth_id'];
        $new_user->created_at = $this->dic['datetimenow'];
        $new_user->save();
        if (is_object($new_user) && isset($new_user->id)) {
            $new_user_info = ORM::for_table('info_users')->create();

            if (isset($data['info_users']) && is_array($data['info_users']) && count($data['info_users']) > 0) {
                foreach ($data['info_users'] as $key => $value) {
                    $new_user_info[$key] = $value;
                }
            }

            if (!is_array($data['roles'])) {
                $data['roles'] = [];
            }

            $new_user_info->user_id = $new_user->id;
            $new_user_info->roles = json_encode($data['roles']);
            $new_user_info->created_at = $this->dic['datetimenow'];
            $new_user_info->save();
            if (is_object($new_user_info) && isset($new_user_info->id)) {
                /** @var DataChangeLog $data_change_log */
                $data_change_log = $this->dic['datachangelog'];
                $data_change_log->insert('users', 'insert', $new_user->id, $data);

                $data['id'] = $new_user->id;
                $data['last_action_date'] = 'отсутствует';

                return (new RunAfterInsert($this->dic))->run($data);
            } else {
                return Response::error500('Произошла ошибка при добавлении информации о пользователе, попробуйте ещё раз');
            }
        } else {
            return Response::error500('Произошла ошибка при добавлении пользователя, попробуйте ещё раз');
        }
    }
190886
public function update(array $data = [])
    {
        $sql = 'update `' . $this->table . '` set ';
        $sql .= Util::rangeField(Util::add2points(array_keys($data)));

        if (!is_null($this->where)) {
            $sql .= ' where ' . $this->where;

            $this->where = null;

            $data = array_merge($data, $this->where_data_binding);

            $this->where_data_binding = [];
        }

        $stmt = $this->connection->prepare($sql);

        $data = Sanitize::make($data, true);

        $this->bind($stmt, $data);

        // Execution of the request
        $stmt->execute();

        $r = $stmt->rowCount();

        return (int) $r;
    }
870359
public function syncData()
    {
        if (Yii::$app->controller->interactive && $this->getRows() > self::LARGE_TABLE_PROMPT) {
            if (Console::confirm("{$this->getName()} has {$this->getRows()} entries. Do you want continue table sync?", true) === false) {
                return;
            }
        }

        $sqlMode = $this->prepare();

        try {
            Yii::$app->db->createCommand()->truncateTable($this->getName())->execute();

            $this->syncDataInternal();
        } finally {
            $this->cleanup($sqlMode);
        }
    }
1193708
protected function execute($sql, array $data = array())
    {
        try{
            foreach ($data as $key => $value) {
                $sql = str_replace(":".$key, "'".$value."'", $sql);
            }

            $execute = mssql_query($sql,$this->raw);

        }catch(\Exception $e){
            return null;
        }
        

        return $execute;
    }
1341657
public function insert($data)
    {
        if(empty($data))
            throw new Exception("Can't insert an empty row");

        return $this->_connection->execute(sprintf(
            'INSERT INTO %s SET %s',
            $this->_name->quoted(),
            $this->_buildSet($data)
            ), array_values($data)
        );
    }
521915
public function createPermission(array $data = [])
    {
        $model = new Permission($this->db);
        $model->data($data);
        try{
            $res = $model->savePermission();
            return $res;
        } catch (Exception $e) {
            throw new Exception($e->getMessage());
        }
    }
966934
public function callback($data = null)
    {
        $data = $data ?: $_POST;
        if ($data['ReturnCode'] != 1) {
            throw new \Exception("交易查询失败：" . $data['ReturnMsg']);
        }
        Datasheet::set($data);
        if (isset($data['Hash']) && $data['Hash'] != Helper::signature(Helper::SIGN_CALLBACK)) {
            throw new \Exception("回调签名验证失败");
        }

        if ($data['PayResult'] != 3) {
            throw new \Exception("交易不成功：" . $data['ReturnMsg']);
        }
        return $data;
    }
852232
public function setDbData(array $data)
    {
        foreach (array_keys(static::$dbProperties) as $propertyName) {
            if (array_key_exists($propertyName, $data)) {
                $this->setDbValue($propertyName, $data[$propertyName], true);
            }
        }
    }
134857
public function delete($data = null)
    {
        // 分析查询表达式
        $options = $this->parseExpress();
        $pk      = $this->getPk($options);
        if (isset($options['cache']) && is_string($options['cache']['key'])) {
            $key = $options['cache']['key'];
        }

        if (!is_null($data) && true !== $data) {
            if (!isset($key) && !is_array($data)) {
                // 缓存标识
                $key = 'think:' . $options['table'] . '|' . $data;
            }
            // AR模式分析主键条件
            $this->parsePkWhere($data, $options);
        } elseif (!isset($key) && is_string($pk) && isset($options['where']['AND'][$pk])) {
            $key = $this->getCacheKey($options['where']['AND'][$pk], $options, $this->bind);
        }

        if (true !== $data && empty($options['where'])) {
            // 如果条件为空 不进行删除操作 除非设置 1=1
            throw new Exception('delete without condition');
        }
        // 生成删除SQL语句
        $sql = $this->builder->delete($options);
        // 获取参数绑定
        $bind = $this->getBind();
        if ($options['fetch_sql']) {
            // 获取实际执行的SQL语句
            return $this->connection->getRealSql($sql, $bind);
        }

        // 执行操作
        $result = $this->execute($sql, $bind);
        if ($result) {
            if (!is_array($data) && is_string($pk) && isset($key) && strpos($key, '|')) {
                list($a, $val) = explode('|', $key);
                unset($a);
                $item[$pk]     = $val;
                $data          = $item;
            }
            $options['data'] = $data;
            $this->trigger('after_delete', $options);
        }
        return $result;
    }
181171
public function execute($data)
    {
        $client = null;
        $bindId = $data['bind_id'] ?? null;
        if ($bindId != null) { // 绑定
            $client = $this->bindPool[$bindId]['client'] ?? null;
            $sql = strtolower($data['sql']);
            if ($sql != 'begin' && $client == null) {
                throw new Exception('error mysql affairs not begin.');
            }
        }
        if ($client == null) {
            if (count($this->pool) == 0) { // 代表目前没有可用的连接
                $this->prepareOne();
                $this->commands->push($data);
                return;
            } else {
                $client = $this->pool->shift();
                if ($client->isClose ?? false) {
                    $this->reconnect($client);
                    $this->commands->push($data);
                    return;
                }
                
                if ($bindId != null) { // 添加绑定
                    $this->bindPool[$bindId]['client'] = $client;
                }
            }
        }
        
        $sql = $data['sql'];
        $client->query($sql, function ($client, $result) use ($data) {
            if ($result === false) {
                if ($client->errno == 2006 || $client->errno == 2013) { // 断线重连
                    $this->reconnect($client);
                    if (! isset($data['bind_id'])) { // 非事务可以重新执行
                        $this->commands->unshift($data);
                    }
                    return;
                    // 发生错误
                } else {
                    // 事务出错不需要自动回滚，否则会带来新问题，调整为手工回滚（modified by xudianyang）
                    // if (isset($data['bind_id'])) {
                    // $data['sql'] = 'rollback';
                    // $this->commands->push($data);
                    // }
                    // 设置错误信息
                    $data['result']['error'] = "[mysql]:" . $client->error . "[sql]:" . $data['sql'];
                }
            }
            $sql = strtolower($data['sql']);
            if ($sql == 'begin') {
                $data['result'] = $data['bind_id'];
            } else {
                $data['result']['client_id'] = $client->client_id;
                $data['result']['result'] = $result;
                $data['result']['affected_rows'] = $client->affected_rows;
                $data['result']['insert_id'] = $client->insert_id;
            }
            // 给worker发消息
            $this->asynManager->sendMessageToWorker($this, $data);
            
            // 不是绑定的连接就回归连接
            if (! isset($data['bind_id'])) {
                $this->pushToPool($client);
            } else { // 事务
                $bindId = $data['bind_id'];
                if ($sql == 'commit' || $sql == 'rollback') { // 结束事务
                    $this->freeBind($bindId);
                }
            }
        });
    }