MySQL 字符串数字比较大小的问题以及解决方案

本文最后更新于:2020年12月30日 下午

为什么要写这篇文章呢?因为吾辈碰到了 MySQL 中字符串数字比较起来的坑。

字符串数字:明明是数字却在数据库中使用 varchar 等类型进行存储的数字。

例如以下的表结构:

1
2
3
4
5
6
7
CREATE TABLE test_table (
id BIGINT NOT NULL
COMMENT 'id',
int_str VARCHAR(200) NOT NULL
COMMENT '使用 varchar 保存数字的列'
)
COMMENT '测试用的数据表';

当吾辈想要查询 big_int_column 大于某个值的数据时,最初是这样写的:

1
2
3
SELECT *
FROM test_table
WHERE int_str > ?;

但后来发现一个问题。。。

其实很简单,因为字符串的比较规则和数字并不一样,虽然很多时候看起来好像没什么不同。

例如下面的 SQL 语句:

1
2
SELECT 2 > 12; #结果为 0(false)
SELECT '2' > '12'; #结果为 1(true)

只是类型变化了,但结果却截然相反,因为字符串是按照字符依次进行对比,而数字则是按照大小直接比较。

按照字母依次进行对比的意思是:
例如上面的 ‘2’ 和 ‘12’,首先会比较字符 ‘2’ 和 ‘1’ 的大小,如果不是相等,就立刻得出结果,否则继续比较下一位,直到得到结果或没有可以继续比较的情况为止,所以就会得到看似荒谬的 1(true)。

所以后来去稍微查询了一下,发现了 MySQL 中的两个函数:

  • cast()
    标准语法:

    1
    cast(value as type)

    或者(不是重点)

    1
    cast(value AS CHAR CHARACTER SET encoding)

    使用示例:

    1
    2
    3
    4
    # 转换为整数(值区间 [-9223372036854775808 ~ 9223372036854775807])
    cast('12' AS SIGNED)
    # 或者转换为无符号整数(值区间 [0 ~ 18446744073709551615])
    cast('12' AS UNSIGNED)
  • convert()

    标准语法:

    1
    convert(value, type)

    或者(不是重点)

    1
    convert(value using charset)

    使用示例:

    1
    2
    3
    4
    # 转换为整数
    convert('12', SIGNED)
    # 或者转换为无符号整数
    convert('12', UNSIGNED)

    所以吾辈将查询的 SQL 语句修改成如下样子:

1
2
3
SELECT *
FROM test_table
WHERE int_str > cast(? AS UNSIGNED);

算是解决了一些问题,然而如果 int_str 的值大于 UNSIGNED 类型的最大值又会发生问题。。。

然后从网络上看到了一种推荐的 SQL 的写法:

1
SELECT '12' + 0;

但经过了测试,甚至比上面的 cast()convert() 表现更糟。

例如下面的 SQL:

1
SELECT '9223372036854775807' + 0; #结果是:9223372036854776000

所以说,还是直接直接用数字存储数字最好啦!(_´ω `)


本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 协议 ,转载请注明出处!