mysql - 如何在查询构建器中使用插入插入多条记录？

Question

我正在 CakePHP 3 中尝试以下操作:

   $newUsers = [
        [
            'username' => 'Felicia',
            'age' => 27,
        ],
        [
            'username' => 'Timmy',
            'age' => 71,
        ],
    ];

    $insertQuery = $this->Users->query();

    $insertQuery->insert(array_keys($newUsers[0]))
                ->values($newUsers)
                ->execute();

我收到以下错误:

Error: SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1

SQL Query: INSERT INTO users (username, age) VALUES (:c0, :c1, :c2, :c3) 

我期待 INSERT INTO users (username, age) VALUES (:c0, :c1), (:c2, :c3); 作为查询。

我打开了数据库配置的日志，我看到了:

2014-10-27 16:10:26 Debug: INSERT INTO users (username, age) VALUES (NULL, NULL, 'Array', 'Array')

如果我误解了在 CakePHP 3.x 中使用查询构建器的潜力，请帮助我理解

Answer 1

刚刚测试。这行得通。

   $newUsers = [
        [
            'username' => 'Felicia',
            'age' => 27,
        ],
        [
            'username' => 'Timmy',
            'age' => 71,
        ],
    ];

    $columns = array_keys($newUsers[0]);

警报!有两种方法可以进行批量插入。这是一种方式:

    $insertQuery = $this->Users->query();

    $newUsersValuesExpression = new ValuesExpression($columns, $insertQuery->typeMap()->types([]));
    $newUsersValuesExpression->values($newUsers);

    $insertQuery->insert($columns)
                ->values($newUsersValuesExpression)
                ->execute();

根据 ndm 的建议，我更喜欢这种方式。

    $insertQuery = $this->Users->query();

    $insertQuery->insert($columns);

    // you must always alter the values clause AFTER insert
    $insertQuery->clause('values')->values($newUsers);

    $insertQuery->execute();