将行从一个表移动到另一个表(插入值列表与列列表不匹配) [英] Moving a row from one table to another (Insert value list does not match column list)

问题描述

我正在使用Laravel PHP框架的Fluent查询生成器将行从一个表移动到另一个表.正在使用PDO.使用原始查询DB::query()时，出现错误:

I am using Laravel PHP framework's Fluent query builder to move rows from one table to another. PDO is being used. While using the raw query DB::query(), I get the error:

错误

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

SQL: INSERT IGNORE into listings_archive VALUES (?, ?)

查询

// Get rows from first table
$rows = DB::table('table_1')
        ->where('id', '>', '12345')
        ->get();

// Copy rows to second table
foreach($rows as $row) {
    $listing = get_object_vars($row);
    DB::query('INSERT IGNORE into table_2 VALUES (?, ?)', $row);
}

$ row的var_dump

array(39) {
    ["id"]=>
    string(7) "2511877"
    ["name"]=>
    string(2) "AB"
    ["color"]=>
    NULL
    ["type"]=>
    NULL
    ...

是什么原因导致该错误，以及如何解决该错误?我尝试用NULL s删除元素，但仍然收到相同的错误！

What is causing the error and how can it be fixed? I tried removing the elements with NULLs but still get the same error!

这可能是将数组传递到DB::query()中的问题.这些非常简单的示例给出了类似的错误:

This is possibly a problem with the array being passed into DB::query(). These very simple example gave similar errors:

$row = array('id', 123);
DB::query('INSERT IGNORE into table_2 VALUES (?, ?)', $row);

和

$row = array('id' => 123);
DB::query('INSERT IGNORE into table_2 VALUES (?, ?)', $row);

错误

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

推荐答案

好吧，列数与值的数量不匹配. 如果您不指定哪些列，则默认为所有列，因此您需要类似

Well the column count doesn't match the number of values. If you don't specify which columns, it defaults to all, so you want something like

Insert IGNORE into table_2(Column1,Column2) Values (?, ?)

Column1，Column2，是要将Table_1中的值放入到table_2中的两列的名称.

Column1, Column2, being the names of the two columns in table_2 you want to put the values from Table_1 into.

这篇关于将行从一个表移动到另一个表(插入值列表与列列表不匹配)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！