带主键 insert into 批量操作，框架运行报错，但 sql 没错，如何解决？（Laravel 5.5）

问答 / 2128 / 8 / 创建于 6年前 / 更新于 6年前

在数据库中可能存在数据的情况下，获取原有数据，再计算出更新数据和新生数据。对原有数据进行更新，对新生数据进行添加，将更新和插入操作合为同一批量操作。代码片段如下：
file
生成批量插入或更新 sql 的方法：
file
运行报错，但是 sql 语句是没有错误的，运行结果如下：
file

QueryException {#1180
  #sql: "insert into `house_manage_fee` (`id`,`house_id`,`manage_at`,`manage_fee`,`created_at`,`updated_at`) values ('','2','2018-01','100.00','2018-03-23 12:05:48','2018-03-23 12:05:48'),('','2','2018-02','100.00','2018-03-23 12:05:48','2018-03-23 12:05:48'),('','2','2018-03','100.00','2018-03-23 12:05:48','2018-03-23 12:05:48'),('','3','2018-03','10.00','2018-03-23 12:05:48','2018-03-23 12:05:48') on duplicate key update  `created_at` = values(`created_at`) , `house_id` = values(`house_id`) , `id` = values(`id`) , `manage_at` = values(`manage_at`) , `manage_fee` = values(`manage_fee`) , `updated_at` = values(`updated_at`) "
  #bindings: []
  #message: "SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'id' at row 1 (SQL: insert into `house_manage_fee` (`id`,`house_id`,`manage_at`,`manage_fee`,`created_at`,`updated_at`) values ('','2','2018-01','100.00','2018-03-23 12:05:48','2018-03-23 12:05:48'),('','2','2018-02','100.00','2018-03-23 12:05:48','2018-03-23 12:05:48'),('','2','2018-03','100.00','2018-03-23 12:05:48','2018-03-23 12:05:48'),('','3','2018-03','10.00','2018-03-23 12:05:48','2018-03-23 12:05:48') on duplicate key update  `created_at` = values(`created_at`) , `house_id` = values(`house_id`) , `id` = values(`id`) , `manage_at` = values(`manage_at`) , `manage_fee` = values(`manage_fee`) , `updated_at` = values(`updated_at`) )"
  #code: "HY000"
  #file: "D:\PHPWAMP\wwwroot\ts_rms\vendor\laravel\framework\src\Illuminate\Database\Connection.php"
  #line: 664
  -previous: PDOException {#1184
    #message: "SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'id' at row 1"
    #code: "HY000"
    #file: "D:\PHPWAMP\wwwroot\ts_rms\vendor\laravel\framework\src\Illuminate\Database\Connection.php"
    #line: 458
    +errorInfo: array:3 [
      0 => "HY000"
      1 => 1366
      2 => "Incorrect integer value: '' for column 'id' at row 1"
    ]

生成的 sql （ id 是主键，自增）【重点：这是可更新数据的批量插入 sql 语句，不懂自行百度（on duplicate key update ）】:

"insert into 
`house_manage_fee` 
(`id`,`house_id`,`manage_at`,`manage_fee`,`created_at`,`updated_at`) 
values 
('','2','2018-01','100.00','2018-03-23 09:33:56','2018-03-23 09:33:56')
,('','2','2018-02','100.00','2018-03-23 09:33:56','2018-03-23 09:33:56')
,('','2','2018-03','100.00','2018-03-23 09:33:56','2018-03-23 09:33:56')
,('','3','2018-03','10.00','2018-03-23 09:33:56','2018-03-23 09:33:56') 
on duplicate key update  
`created_at` = values(`created_at`) 
, `house_id` = values(`house_id`)
, `id` = values(`id`) 
, `manage_at` = values(`manage_at`) 
, `manage_fee` = values(`manage_fee`) 
, `updated_at` = values(`updated_at`) "

sql 放在运行器中，却运行一切正常：
file
file