MySQL字符比较大小的小坑
0、前言
最近在帮学校做一个项目管理类的系统,我就大概说明一个哈,我们在t_project表中,存在一个varchar类型的project_state字段,用于记录目前该项目处于哪个阶段。比如说,用‘2’表示“审核中”,用‘3’表示“已审核”,用‘13’表示“已完结”。
然后现在,我们有一个需求是:列出所有“已审核”,但是“未完结”的项目,转化一个就是我们需要过滤出 ”3 <= project_state < 13” 的所有项目即可。
于是当时我们就写了一个SQL语句如下:
SELECT aaa,bbb,ccc
FROM t_project
WHERE xxx = xxx
AND t_project >= '3'
AND t_project < '13';
但是,当我们在调试时却发现结果并非我们想要的。当时刚觉逻辑也没问题呀,于是一直Debug调试,搞了一个多小时呀,最后发现就出在SQL语句上。
其实有些小伙伴已经发现了,就是字符 ‘3’其实是大于‘13’的。当时真的是恍然大悟,其实这个小知识点或者小细节我是知道的,当时开发的时候没注意,所以就在这篇博客记录一下,也算鞭策一下自己。
1、示例
我们先简单地创建一个表:
-- 创建一个表
CREATE TABLE t_project(
INT project_id,
VARCHAR project_state
);
接着我们再插入一些数据,如下图所示。
我们进行查询:
SELECT *
FROM t_project
WHERE project_state >= '3';
结果:
发现和我们想象的不太一样,按理来说,id为8、9、10的项目也应该被查询出来。
这是为何呢?
其实是因为,‘3’与‘13’在比较时,都是从左到右比较每个字符的,此处,‘3’已经是大于‘13’中的‘1’了,所以就直接返回‘3’ > ‘13’ ,即返回true。
其实,不止在MySQL的SQL中,在Java的字符串中都有这样一个问题。
例如,在Java中,我们输出这样一个语句:
public class Main {
public static void main(String[] args) {
System.out.println("3".compareTo("13"));
System.out.println("13".compareTo("3"));
}
}
/*
其输出结果为:
2
-2
*/
compareTo() 的返回值是int, 它是先比较对应字符的大小(ASCII码顺序)
- 如果字符串相等返回值0
- 如果第一个字符和参数的第一个字符不等,结束比较,返回他们之间的差值(ASCII码值)
- 如果第一个字符和参数的第一个字符相等,则以第二个字符和参数的第二个字符做比较,以此类推,直至比较的字符或被比较的字符有一方全比较完,这时就比较字符的长度。
再补充一点,在Java语法中,比较字符串相等不能使用“==”,要使用equals()方法。
string.equals("test");
"test".equals(string); //相较于第一种,更推荐第二种,可以有效防止“空指针异常”
2、问题解决
对于上述需求,我要查询 ”project_state >= 3” 的所有数据,我们这么书写SQL语句。
2.1、字符串+0
SELECT *
FROM t_project
WHERE t_project + 0 >= '3' + 0;
-- WHERE t_project + 0 >= 3; -- 这样也可以
结果:
2.2、CAST函数
SELECT *
FROM t_project
WHERE CAST(project_state AS SIGNED) >= 3;
-- 或者 WHERE CAST(project_state AS SIGNED) >= CAST('3' AS SIGNED)
结果相同,此处不贴图片了。
2.3、CONVERT()函数
SELECT *
FROM t_project
WHERE CONVERT(project_state AS SIGNED) >= 3;
-- 或者 WHERE CONVERT(project_state AS SIGNED) >= CONVERT('3' AS SIGNED)
结果相同,此处不贴图片了。
注意:如果使用MyBatis框架在XML文件中书写SQL语句,要注意“不等号”的方向,例如。
<select id="selectProject" resultMap="xxx">
SELECT *
FROM t_project
WHERE CONVERT(project_state AS SIGNED) >= 3
AND CONVERT(project_state AS SIGNED) < 13;
</select>
其实这个SQL语句并没有什么毛病,只是在XML文件中,可以会将“<”当作是“标签的左括号”而报出“Tag name expected”,此时只需要改变括号方向即可。
<select id="selectProject" resultMap="xxx">
SELECT *
FROM t_project
WHERE CONVERT(project_state AS SIGNED) >= 3
AND 13 > CONVERT(project_state AS SIGNED); <!--改变方向即可-->
</select>
如有错误,敬请指正!