mysql order by跟limit,mysql中order by和limit同时使用时存在的问题及解决方法

今天突然遇到一个问题，同时使用order by 和 limit 时存在问题(order by 列存在重复值);

就是当使用 limit 140,10  和 limit 130,10的数据是一样;

按正常理解，两者之间数据应该是不一样

执行sql如下：[同时，我给了测试sql，测试数据以及测试表结构]

SELECT

p.id,

p.org_name AS orgName,

p.city_id AS cityId,

p.is_cooperation AS isCooperation,

p.create_time AS createTime,

p.maintenancer AS creater,

p.audit_status AS auditStatus

FROMpartner_organization p

WHEREp.partner_type = 1

AND p.city_id IN (1, 2)

ORDER BYp.create_time DESC

LIMIT 140,10

执行后结果集如下：

执行如下sql：

SELECT

p.id,

p.org_name AS orgName,

p.city_id AS cityId,

p.is_cooperation AS isCooperation,

p.create_time AS createTime,

p.maintenancer AS creater,

p.audit_status AS auditStatus

FROMpartner_organization p

WHEREp.partner_type = 1

AND p.city_id IN (1, 2)

ORDER BYp.create_time DESC

LIMIT 130,10

则结果集如下：

两者的结果集明显一样;

因此，我们推测：

在同时使用order by和limit时，MySQL进行了某些优化，

将语句执行逻辑从”where——order by——limit”变成了”order by——limit——where”

那么针对这条语句，我们如何进行优化呢，我们采取两种方式：

方式一：我们将排序列，新增一列，确保唯一性来实现limit

SELECT

p.id,

p.org_name AS orgName,

p.city_id AS cityId,

p.is_cooperation AS isCooperation,

p.create_time AS createTime,

p.maintenancer AS creater,

p.audit_status AS auditStatus

FROM partner_organization p

WHERE p.partner_type = 1

AND p.city_id IN (1, 2)

ORDER BY

p.create_time,id DESC

LIMIT 140,10

方式二：我们用where过滤后形成结果集，作为子查询来处理

SELECT

p.id,

p.org_name AS orgName,

p.city_id AS cityId,

p.is_cooperation AS isCooperation,

p.create_time AS createTime,

p.maintenancer AS creater,

p.audit_status AS auditStatus

FROM(

SELECT     *

FROM partner_organization p

WHERE p.partner_type = 1 AND p.city_id IN (1, 2)

) p

ORDER BY p.create_time DESC

LIMIT 130,10

测试数据下载地址如下：

链接: http://pan.baidu.com/s/1gdy7hQj

密码: icx8

Author: Michael