带主键 insert into 批量操作,框架运行报错,但 sql 没错,如何解决?(Laravel 5.5)
在数据库中可能存在数据的情况下,获取原有数据,再计算出更新数据和新生数据。对原有数据进行更新,对新生数据进行添加,将更新和插入操作合为同一批量操作。代码片段如下:
生成批量插入或更新 sql 的方法:
运行报错,但是 sql 语句是没有错误的,运行结果如下:
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 放在 运行器中,却运行一切正常:
如何解决这个问题???
急!急!急!求大神帮助!!!!
(laravel 5.5)
推荐文章: