php - yii2 从数据库类覆盖 batchinsert

Question

我想覆盖 batchinsert，因为我想添加 ON DUPLICATE KEY UPDATE

$result = Yii::$app->db->createCommand()->batchInsert('product', ['asin', 'title', 'image', 'url', 'price'], $results)->execute();


public function batchInsert($table, $columns, $rows)
{

    if (empty($rows)) {
        return '';
    }

    $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 (isset($columns[$i], $columnSchemas[$columns[$i]]) && !is_array($value)) {
                $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) . ')';
    }

    $query = 'INSERT INTO ' . $schema->quoteTableName($table);
    $duplicate = ' ON DUPLICATE KEY UPDATE ';

    $last = end($columns);
    reset($columns);

    foreach ($columns as $i => $name) {
        $columns[$i] = $schema->quoteColumnName($name);
        $duplicate .= $schema->quoteColumnName($name) . ' = VALUES(' . $schema->quoteColumnName($name) . ')';

        if ($name <> $last) {
            $duplicate .= ', ';
        }
    }

    $query .= ' (' . implode(', ', $columns) . ') ';
    $query .= ' VALUES ' . implode(', ', $values);
    $query .= $duplicate;

    return $query;
}

这是可行的，但现在我需要覆盖 yii\db\QueryBuilder如果找到这个:Yii::$app->db->commandClass = new common\models\Command();

我可以覆盖命令类，但是 yii\db\Schema 中的代码也应该更新

public function createQueryBuilder()
{
    return new \common\models\QueryBuilder($this->db);
}

或者我需要做一些完全不同的事情，我该如何解决这个问题？

Answer 1

根据 this discussion about non standard sql commands您可以执行以下操作的开发人员(未测试):

class MyQueryBuilder extends yii\db\mysql\QueryBuilder
{
    public function batchInsert($table, $columns, $rows)
    {
        $sql = parent::batchInsert($table, $columns, $rows);
        $sql .= 'ON DUPLICATE KEY UPDATE';
        return $sql;
    }
}

或者你可以这样做:

$db = Yii::$app->db;
$sql = $db->queryBuilder->batchInsert($table, $fields, $rows);
$db->createCommand($sql . ' ON DUPLICATE KEY UPDATE')->execute();