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

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

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

例如以下的表结构：

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

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

SELECT *
FROM test_table
WHERE int_str > ?;

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

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

例如下面的 SQL 语句：

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

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

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

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

• cast()
  标准语法：

  cast(value as type)

  或者（不是重点）

  cast(value AS CHAR CHARACTER SET encoding)

  使用示例：

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

• convert()

  标准语法：

  convert(value, type)

  或者（不是重点）

  convert(value using charset)

  使用示例：

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

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

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

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

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

SELECT '12' + 0;

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

例如下面的 SQL：

SELECT '9223372036854775807' + 0; #结果是：9223372036854776000

所以说，还是直接直接用数字存储数字最好啦！（＿´ω ｀）