SQLite返回错误最小ID，INTEGER PRIMARY KEY字段排序异常问询

问题根源 #

这个问题的核心是id字段排序时按字符串字节序而非数值大小排序，和你使用的老旧SQLite版本、字段定义或异常写入的异常值有关，具体分析如下：

• 你执行ORDER BY TRIM(id) + 0后排序正常，已经验证了这个结论：TRIM(id) + 0会把id强制转为数值类型再排序，自然就得到了正确的顺序。
• 为什么数值会按字符串排序：
  1. 最常见的原因是你实际建表时id字段的类型并非你以为的INTEGER PRIMARY KEY，而是TEXT/VARCHAR等字符串类型，SQLite对字符串类型的排序默认按逐字符ASCII码比较：
     比如异常的47196实际存储值是带前导空格的字符串' 47196'，空格的ASCII码（32）远小于数字1的ASCII码（49），所以字符串比较时' 47196' < '14421'，自然就排在了第一位，MIN()函数按排序逻辑取值，也会返回这个异常值。
  2. 如果你确认id字段确实定义为INTEGER PRIMARY KEY，那就是你使用的SQLite 3.6.19（2009年发布的老旧版本）的类型校验漏洞导致的：这个版本对INTEGER PRIMARY KEY的强类型校验不完善，当你插入数据时如果绑定参数类型指定为TEXT、或者传入带不可见前导字符的字符串格式数值，会绕过校验直接以字符串类型存入字段，排序时SQLite会按存储的实际类型处理，字符串类型的数值就会按字节序排序。
• 关于数据库损坏的疑问：SQLite的损坏检测只针对物理存储层面的页损坏、校验和错误等问题，这种逻辑层面的类型不符合定义的问题，因为SQLite本身支持动态类型，属于合法存储状态，不会被判定为数据库损坏。

修复方案 #

1. 先确认表结构实际定义，执行语句PRAGMA table_info(myTable);检查id字段的type字段值，确认是否为INTEGER类型，如果是字符串类型，导出数据后重建表指定id为INTEGER PRIMARY KEY再导回数据即可永久解决。
2. 如果字段类型确实是INTEGER，执行语句SELECT id, LENGTH(id), TYPEOF(id) FROM myTable WHERE id = 47196;查看异常id的实际类型和长度：
   • 如果类型为TEXT、长度大于5，说明该值带前导不可见字符，直接修正或删除这条异常记录即可。
3. 建议尽快升级SQLite版本，3.6.19已经有十几年的历史，后续版本修复了大量类型处理相关的漏洞，新版本对INTEGER PRIMARY KEY的强类型校验更严格，不会出现这类异常值写入的问题。

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