mysql - 如何在mysql中检查带有前导零的varchar

Question

以下查询中的条件返回真结果，但我想要假结果。请给我建议。

select *
from test    
where 
'00160001' between '0013001' and '0023000' OR 
'00200000' between '0013001' and '0023000'  

Answer 1

没有前导零你会得到相同的结果。
使用整数数据类型来比较整数值。
您还可以使用 cast 即时转换值:

http://dev.mysql.com/doc/refman/5.7/en/cast-functions.html

你的 Cast 示例:

select if( cast('00160001' as unsigned) between cast('0013001' as unsigned)  
and cast('0023000' as unsigned),1,0) as test_a,  
if( cast('00200000' as unsigned) between cast('0013001' as unsigned)   
and cast('0023000' as unsigned),1,0) as test_b;