MySQL-mysql字符串转换成数字后如何比较大小

Question

如下图所示，获取的text字段本来是字符串，现在我想将这个字段转化为数字，然后用case when语句在各个数值范围内作比较，获取一个新的字段值filter_id。

我的做法首先是提取text字段的数字部分然后用cast转化为数值，SQL语句如下

SELECT `product_id` ,
CASE
WHEN cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int) <1000
THEN 1
WHEN 1000 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int) <=2000
THEN 2
WHEN 2001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int) <=3000
THEN 3
WHEN 3001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int) <=4000
THEN 4
WHEN 4001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int) <=5000
THEN 5
WHEN 5001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int) <=8000
THEN 6
WHEN 8001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int)
THEN 7
END AS `filter_id`
FROM `jfw_product_attribute`
WHERE `attribute_id` =3

Answer 1

先说那里错了吧.

mysql> select 1 <= 2;
+--------+
| 1 <= 2 |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)

mysql> select 2 <= 1;
+--------+
| 2 <= 1 |
+--------+
| 0 |
+--------+
1 row in set (0.00 sec)

mysql> select 1 <= 2 <= 1;
+-------------+
| 1 <= 2 <= 1 |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)

结论就是 sql 里不能写 a <= b <= c; mysql里应该是取 前面的 <= 来做了判断.

所以按你现在的方式, 写为:

 WHEN 1000 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as UNSIGNED int)
and
cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as UNSIGNED int) <= 2000
THEN 2

不可否认, 很丑, 等有空再想想怎么去做这个了...