For example,
php
$sql = $queryBuilder->batchInsert('user', ['name', 'age'], [
['Tom', 30],
['Jane', 20],
['Linda', 25],
]);
Note that the values in each row must match the corresponding column names.
The method will properly escape the column names, and quote the values to be inserted.
/** * Generates a batch INSERT SQL statement. * For example, * * ```php * $connection->createCommand()->batchInsert('user', ['name', 'age'], [ * ['Tom', 30], * ['Jane', 20], * ['Linda', 25], * ])->execute(); * ``` * * Note that the values in each row must match the corresponding column names. * * @param string $table the table that new rows will be inserted into. * @param array $columns the column names * @param array $rows the rows to be batch inserted into the table * @return string the batch INSERT SQL statement */ public function batchInsert($table, $columns, $rows) { if (empty($rows)) { return ''; } // SQLite supports batch insert natively since 3.7.11 // http://www.sqlite.org/releaselog/3_7_11.html $this->db->open(); // ensure pdo is not null if (version_compare($this->db->pdo->getAttribute(\PDO::ATTR_SERVER_VERSION), '3.7.11', '>=')) { return parent::batchInsert($table, $columns, $rows); } $schema = $this->db->getSchema(); if (($tableSchema = $schema->getTableSchema($table)) !== null) { $columnSchemas = $tableSchema->columns; } else { $columnSchemas = []; } $values = []; foreach ($rows as $row) { $vs = []; foreach ($row as $i => $value) { if (!is_array($value) && isset($columnSchemas[$columns[$i]])) { $value = $columnSchemas[$columns[$i]]->dbTypecast($value); } if (is_string($value)) { $value = $schema->quoteValue($value); } elseif ($value === false) { $value = 0; } elseif ($value === null) { $value = 'NULL'; } $vs[] = $value; } $values[] = implode(', ', $vs); } foreach ($columns as $i => $name) { $columns[$i] = $schema->quoteColumnName($name); } return 'INSERT INTO ' . $schema->quoteTableName($table) . ' (' . implode(', ', $columns) . ') SELECT ' . implode(' UNION SELECT ', $values); }
/** * This command will update auth tables with data from auth configs */ public function actionSyncDeploy() { $queryBuilder = new QueryBuilder(Yii::$app->db); Yii::$app->db->createCommand("SET FOREIGN_KEY_CHECKS=0;")->execute(); if (file_exists($this->authItemConfig)) { Yii::$app->db->createCommand()->delete('AuthItem')->execute(); $authItem = (require $this->authItemConfig); $insertAuthItemQuery = $queryBuilder->batchInsert('AuthItem', ['name', 'type', 'description', 'rule_name', 'data'], $authItem); Yii::$app->db->createCommand($insertAuthItemQuery)->execute(); } if (file_exists($this->authItemChildConfig)) { Yii::$app->db->createCommand()->delete('AuthItemChild')->execute(); $authItemChild = (require $this->authItemChildConfig); $insertAuthItemChildQuery = $queryBuilder->batchInsert('AuthItemChild', ['parent', 'child'], $authItemChild); Yii::$app->db->createCommand($insertAuthItemChildQuery)->execute(); } if (file_exists($this->authRuleConfig)) { Yii::$app->db->createCommand()->delete('AuthRule')->execute(); $authRule = (require $this->authRuleConfig); $insertAuthRuleQuery = $queryBuilder->batchInsert('AuthRule', ['name', 'data'], $authRule); Yii::$app->db->createCommand($insertAuthRuleQuery)->execute(); } Yii::$app->db->createCommand("SET FOREIGN_KEY_CHECKS=1;")->execute(); Yii::$app->db->createCommand("DELETE aa FROM `AuthAssignment` aa LEFT JOIN AuthItem ai ON(aa.item_name = ai.name) WHERE ai.name IS NULL;")->execute(); Yii::$app->cache->flush(); }
/** * This command will update auth tables with data from auth configs */ public function actionSyncDeploy() { $queryBuilder = new QueryBuilder(Yii::$app->db); $authItemTable = Yii::$app->authManager->itemTable; if ('pgsql' == Yii::$app->db->driverName) { Yii::$app->db->createCommand("ALTER TABLE \"{$authItemTable}\" DISABLE TRIGGER ALL")->execute(); } else { Yii::$app->db->createCommand("SET FOREIGN_KEY_CHECKS=0;")->execute(); } if (file_exists($this->authItemConfig)) { Yii::$app->db->createCommand()->delete($authItemTable)->execute(); $authItem = (require $this->authItemConfig); if (!empty($authItem)) { $insertAuthItemQuery = $queryBuilder->batchInsert($authItemTable, ['name', 'type', 'description', 'rule_name', 'data'], $authItem); Yii::$app->db->createCommand($insertAuthItemQuery)->execute(); } } if (file_exists($this->authItemChildConfig)) { Yii::$app->db->createCommand()->delete(Yii::$app->authManager->itemChildTable)->execute(); $authItemChild = (require $this->authItemChildConfig); if (!empty($authItemChild)) { $insertAuthItemChildQuery = $queryBuilder->batchInsert(Yii::$app->authManager->itemChildTable, ['parent', 'child'], $authItemChild); Yii::$app->db->createCommand($insertAuthItemChildQuery)->execute(); } } if (file_exists($this->authRuleConfig)) { Yii::$app->db->createCommand()->delete(Yii::$app->authManager->ruleTable)->execute(); $authRule = (require $this->authRuleConfig); if (!empty($authRule)) { $insertAuthRuleQuery = $queryBuilder->batchInsert(Yii::$app->authManager->ruleTable, ['name', 'data'], $authRule); Yii::$app->db->createCommand($insertAuthRuleQuery)->execute(); } } if ('pgsql' == Yii::$app->db->driverName) { Yii::$app->db->createCommand("ALTER TABLE \"{$authItemTable}\" ENABLE TRIGGER ALL")->execute(); } else { Yii::$app->db->createCommand("SET FOREIGN_KEY_CHECKS=1;")->execute(); } $this->processAssignments(); Yii::$app->cache->flush(); }