1、数值型字符串的过滤问题
数值型字符串范围过滤时,不能直接用字符串过滤,否则会有bug,而且是不易发现的bug
e.g:sslv_avg是varchar类型
select count(*) from TQXS_SSLV_AVG_STDDEV_WXF where sslv_avg > '0' and sslv_avg <= '2'
过滤时不仅仅会过滤出0~2之间的数字字符串,11, 12这类也会过滤出来,处理方案:
select count(*) from TQXS_SSLV_AVG_STDDEV_WXF where cast(t.sslv as DECIMAL(5,2)) > 0 and cast(t.sslv as DECIMAL(5,2) ) < 2
2、create table:Out of range value for column '(null)' at row 1
有字段值为NULL时,对这个字段进行过滤会触发这个错误,需要先过滤掉为NULL的记录。这里有个比较难发现的问题,如果单纯select,不会报错:
select * from TQXS_DELETE_REPEAT t
where cast(t.sslv as DECIMAL(5,2)) > 0 and cast(t.sslv as DECIMAL(5,2) ) < 20
但如果在create table中就会报上述错误:
create table TQXS_DELETE_REPEAT_CLEAR_WXF1 as (select * from TQXS_DELETE_REPEAT t
where cast(t.sslv as DECIMAL(5,2)) > 0 and cast(t.sslv as DECIMAL(5,2) ) < 20)
这个是因为sslv字段中有异常值,这里是'##############'和NULL,需要注意三点:
- select的容错比crate table要强,本该报异常的却没报;
- 异常过滤要注意是否所有异常情况都过滤了;
- 类型要对应 还是 容易出bug 有点难发现。