MySQL JOIN中使用BETWEEN配合ORDER BY时返回结果不符合预期

这个问题是典型的字符串类型存储数值+未做显式类型转换导致的规则错乱，和JOIN写法、是否用子查询没有关系，具体触发逻辑：

1. 你的field_secondary_prize_value_value字段是varchar类型，所有字符串类型的大小比较、排序默认按字典序执行，也就是逐字符比对ASCII码：比如字符串'10'和'5'比较，第一个字符'1'的ASCII码比'5'小，所以字典序下'10' < '5'，和数值大小逻辑完全相反。
2. 你写等值条件= 10能正常返回值为10的记录，是因为MySQL遇到等值匹配时，会自动把两侧值转成数值做比较；但写BETWEEN 0 AND 10时，如果没做显式转换，会按字符串规则做范围匹配+排序，就会出现逻辑偏差。
3. 你看到返回结果全是5，本质是按字符串降序排序时，'5'的排序优先级比'10'高，加上大多数数据库客户端默认只展示前10条结果，所以最先返回的10条全是值为5的记录，值为10的记录实际已经被匹配到了，只是排在更靠后的位置，你没翻到而已——这也能解释为什么你改成按t1基表字段排序时，能看到区间内的不同值。
4. 你之前尝试转int没生效，基本是转换位置错了：只在SELECT子句里写了类型转换，JOIN条件、ORDER BY子句里还是用的原始varchar字段，判断和排序依然走的字符串规则。

不需要用子查询，只要在范围条件、排序两个位置统一做显式数值转换即可，参考写法：

SELECT
    t1.id,
    CAST(t2.field_secondary_prize_value_value AS UNSIGNED) AS prize_value
FROM
    table1 t1
INNER JOIN table2 t2 ON
    t1.id = t2.entity_id
    -- 条件处显式转无符号整数，走数值范围匹配
    AND CAST(t2.field_secondary_prize_value_value AS UNSIGNED) BETWEEN 0 AND 10
-- 按转换后的数值降序，值为10的记录会排在最前面
ORDER BY
    prize_value DESC;

长期优化建议 #

如果这个字段本身存的全是数值，且需要频繁做范围查询、数值排序，建议直接把字段类型修改为INT或者对应精度的DECIMAL类型，从根源上避免类型转换问题，同时能让查询用上字段索引，提升执行效率。

内容的提问来源于stack exchange，提问作者awm