根本原因是varchar字段按字典序（ascii码）比较而非数值大小，如'10'首字符'1'（ascii 49）

MySQL 里 ORDER BY 数字列却按字符排序，根本原因不是“数据库错了”，而是字段类型本身是字符串（VARCHAR、TEXT 等），而 MySQL 对字符串默认走字典序——也就是逐字符比 ASCII 值。

为什么 '10' 会排在 '2' 前面

这是典型字典序表现：MySQL 拿 '10' 和 '2' 第一个字符比，'1'（ASCII 49）'2'（ASCII 50），所以 '10' 成立。结果就是 <code>1, 10, 11, 2, 3... 这种反直觉顺序。

• 常见错误现象：SELECT version FROM app_logs ORDER BY version; 返回 '1.0', '1.10', '1.2' —— 因为 '1.10' 的第三个字符 '1' 小于 '1.2' 的第三个字符 '2'
• 使用场景：版本号、订单编号、带前缀的 ID（如 'U001', 'U010'）等存储为字符串但需数值逻辑排序
• 不能靠改字段类型一劳永逸：如果业务已存大量 VARCHAR 数据且含非数字字符（如 'v2.1'），直接 ALTER COLUMN 会失败或截断

CAST 或 CONVERT 是最直接的修复方式

对查询层做类型转换，让 MySQL 按数值逻辑比较，而不是字符码点。

• MySQL 推荐写法：ORDER BY CAST(version AS SIGNED)（整数）或 CAST(version AS DECIMAL(10,2))（带小数）
• PostgreSQL 用 ::INTEGER 更简洁：ORDER BY version::INTEGER
• SQL Server 用 CONVERT(INT, version)，注意空值或非法格式会报错，建议套 TRY_CONVERT()
• 性能影响：该操作无法利用原始字符串字段的索引，大表会触发 filesort；若高频查询，应考虑加生成列+索引（如 MySQL 5.7+ 的 STORED 列）

COLLATE 不解决数值字符串排序问题

COLLATE 只影响字符串比较规则（比如大小写、重音、语言习惯），对 '10' 和 '2' 的相对顺序毫无作用。它不能把字符串“当数字看”。

• 错误尝试：ORDER BY version COLLATE utf8mb4_unicode_ci —— 结果仍是字典序，'10' 还在 '2' 前
• 正确用途：解决 'Apple' 和 'apple' 大小写混排，或中文拼音排序（如 COLLATE utf8mb4_pinyin_ci）
• 混淆点：有人看到 utf8mb4_bin 能“精确排序”，但它只是按二进制字节比，'10' 还是排在 '2' 前——因为 0x31 0x30 0x32

真正健壮的方案要分层处理

临时查数据用 CAST 快速修正；长期维护必须从数据建模入手，否则每次查询都扛性能损耗。

• 新表设计：纯数字含义的字段，务必用 INT、BIGINT、DECIMAL 类型，别图省事存 VARCHAR
• 存量数据迁移：可新增数值列 + 触发器同步，或用 UPDATE ... SET num_col = CAST(str_col AS SIGNED) 批量填充（注意 NULL 和非法值兜底）
• 前端/应用层规避：如果 SQL 层无法改，至少在取回结果后用应用代码做 sort(key=int) 或 localeCompare({ numeric: true })，但别在大数据集上这么做
• 最容易被忽略的一点：即使用了 CAST，也要检查隐式转换是否失败——比如字段含 'N/A' 或空格，CAST(' 10' AS SIGNED) 在 MySQL 中会转成 10，但 CAST('abc' AS SIGNED) 直接得 0，导致排序错乱