RDS MySQL排序分页查询数据顺序错乱的原因和解决

  • A+
所属分类:MySQL

1. MySQL 排序分页查询数据顺序错乱的原因

MySQL 排序分页查询某些时候会出现数据顺序错乱的情况,比如:

CREATE TABLE alarm_test (
  id bigint(20) NOT NULL DEFAULT '0',
  detail varchar(255) CHARACTER SET utf8 NOT NULL,
  created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

表内开始的 21 行数据:

RDS MySQL排序分页查询数据顺序错乱的原因和解决

注:第 10 行(id=10)的 created_on 字段值不同。

按照 Created_on 字段值排序,取前 10 行:

RDS MySQL排序分页查询数据顺序错乱的原因和解决

按照 Created_on 字段值排序,取从11行开始的 10 行:

RDS MySQL排序分页查询数据顺序错乱的原因和解决

可以看出,2 次排序分页操作得到的数据是有重合而且无序的(实际上排序分页结果会根据情况的不同而变化,结果不可预料)。

出现这个问题的原因在于 created_on 字段的值在前 21 行记录中有 20 行数据相同。

2. 如何使排序字段结果有序

对于会出现相同数值的情况,有 2 个方法:

2.1 修改排序规则,加入主键字段,使排序字段不存在重复记录,比如:

select id, created_on from alarm_test order by created_on, id limit 0,10;

RDS MySQL排序分页查询数据顺序错乱的原因和解决

2.2 在出现重复值的排序字段上添加索引

CREATE TABLE alarm_test_idx (
  id bigint(20) NOT NULL DEFAULT '0',
  detail varchar(255) CHARACTER SET utf8 NOT NULL,
  created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY created_on (created_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 

RDS MySQL排序分页查询数据顺序错乱的原因和解决

推荐使用第 2 种方法(创建索引),在提供可预期的结果同时,提高查询的执行效率:

RDS MySQL排序分页查询数据顺序错乱的原因和解决

admin
阿里云云服务器ECS-全民云计算/通用版/入门级/个人建站
安全 ssl证书https 网站可信 防劫 防篡改防监听快速签发GeoTrust
轻量应用服务器--云服务器vps 建站/应用/云主机
阿里邮箱 企业邮箱标准版 5账号

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: