执行的语句
[SQL] insert into `hxl_hospital_info` (
`id` ,
`latitude` ,
`longitude` ,
`deleted` ,
`create_time` ,
`update_time`
)
select
`id` ,
`latitude` ,
`longitude` ,
0,
`create_time` ,
`update_time`
from hospital_info_bak20231012
where deleted +1=1;
[Err] 1292 - Truncated incorrect DOUBLE value: ''
解决办法1:
where deleted = '\0' or deleted=0
解决办法2:
where deleted = '\0' or deleted=0
where ASCII(deleted) =0 or ASCII(deleted)=48
deleted字段数据分布
mysql> select deleted,count(1) from hospital_info_bak20231012
-> group by deleted;
+---------+----------+
| deleted | count(1) |
+---------+----------+
| | 21 |
| 0 | 140105 |
| 1 | 768 |
+---------+----------+
3 rows in set (0.16 sec)
表结构
CREATE TABLE `hospital_info_bak20231012` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '门诊id',
`latitude` double(9,6) DEFAULT '0.000000' COMMENT '纬度',
`longitude` double(9,6) DEFAULT '0.000000' COMMENT '经度',
`deleted` varchar(1) NOT NULL DEFAULT '\0' COMMENT '删除标识 0-未删除的 1-删除的',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`) USING BTREE,
);