MySQL VARCHAR类型id表查询自动重排的原因及解决方法

首先需要明确一个基础认知：MySQL本身不存在所谓“自动重排表条目”的机制，你观察到的现象是两个数据库基础逻辑叠加导致的，和SQLite的体验差异也并非数据库故意调整规则：

• 无指定排序的全表查询，返回顺序本身就是不固定的
  所有关系型数据库遵循SQL标准，当你执行SELECT * FROM 表名这类不带排序子句的查询时，数据库完全没有义务按照任何固定顺序返回结果——这个返回顺序本质是存储引擎扫描数据页的自然顺序，可能受索引结构、页大小、历史上的增删改操作、数据库版本影响，随时可能变化。
  你之前用SQLite时看到的“和插入顺序一致”，只是SQLite存储引擎实现上的巧合，并非它保证了插入顺序输出，换个版本、对表做过大量删除插入后，这个顺序同样会乱。你现在查询结果里没看到原本id=2的Emma，本质就是无排序扫描时，InnoDB没有把id=2的行放到第二位返回，不是MySQL把你的数据挪走了。
• 你看到的“1之后直接是10”的现象，是VARCHAR类型的排序逻辑导致的
  如果你在查询时手动加了按id列排序的逻辑，那因为你的id列是字符串类的VARCHAR，它的排序是按逐字符字典序匹配的，不是按数值大小排的：在字典序规则下，字符串"10"的第一个字符是"1"，和"1"完全一致，比字符串"2"的第一个字符"2"要小，所以按VARCHAR类型的id排序，结果顺序就会是"1" < "10" < "2" < "100" < "20"，刚好就是你看到的Karen排在Emma前面的效果。
• 额外的存储机制影响
  如果你用的是MySQL默认的InnoDB存储引擎，表是按聚簇索引组织存储的：如果建表时没有明确指定主键，InnoDB会自动选择第一组非空唯一索引作为聚簇键，或者生成系统隐藏的ROWID列来组织数据存储，数据在磁盘上的物理顺序根本不会绑定你的插入顺序，无排序查询时自然不会按照你插入的先后返回。

• 从开发规范层面，永远不要依赖无ORDER BY子句的查询返回顺序，这是跨数据库通用的原则，不存在哪个数据库会永久保证无排序查询的返回稳定。
• 要实现严格按插入顺序返回结果，需要给表补充稳定的排序依据列，不能依赖数据库的默认存储顺序：
  • 最优方案：新增一列insert_time，设置为DATETIME(3)类型，默认值为CURRENT_TIMESTAMP(3)，每次插入数据时自动记录精确到毫秒的插入时间，查询时加上ORDER BY insert_time ASC，就能完全匹配插入先后的顺序，哪怕同一毫秒插入多条数据，也可以通过加排序二级字段避免顺序随机。
  • 轻量方案：如果不需要记录插入时间，直接把当前VARCHAR类型的id列改为BIGINT UNSIGNED类型的自增主键，自增id的生成顺序严格匹配数据插入的先后，查询时ORDER BY id ASC就能按插入顺序返回，同时也不会出现VARCHAR字典序排序的逻辑错位问题。
• 如果你暂时不想修改现有表结构，想让现有VARCHAR类型的id列排序符合数值大小的直觉，查询时可以将id列转为数值类型再排序，写法为ORDER BY CAST(id AS UNSIGNED) ASC，但这种方式无法利用索引做排序优化，数据量较大时查询性能会明显下降，不适合长期使用。

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