Mysql分页子查询问题

Mysql数据库

技术开发

发布日期: 2023-11-28

更新日期: 2023-11-28

文章字数: 537

阅读时长: 2 分

默认排序

oracle：默认没有排序规则
mysql：
InnoDB引擎：默认查询按照id正序排序
MyISAM引擎：默认按照插入时间正序排序排序

子查询后默认排序失效

没有使用子查询的时候，mysql默认排序正常，无论是否使用ORDER BY。使用子查询之后，mysql默认排序在使用ORDER BY之后失效(ORDER BY指定的排序正常，但是ORDER BY之后的默认排序失效，会导致查询结果每次不同)，不使用ORDER BY仍为默认主键升序。

没有子查询每次查询一致

SELECT
    `t_employee_info`.`c_id`,
    `t_employee_info`.`c_job_level_sort`,
    `t_employee_info`.`c_emp_name`,
    `t_employee_info`.`c_job_level_name`
FROM
    `t_employee_info` 
WHERE
    (
    `t_employee_info`.`c_is_delete` = FALSE 
    AND `t_employee_info`.`c_company_id` = '93c6bff9c6154e469338c42d93a6ba92' 
    AND `t_employee_info`.`c_work_status` IN ( 1, 2, 3 ) 
    AND `t_employee_info`.`c_company_id` = '93c6bff9c6154e469338c42d93a6ba92' 
    ) 
ORDER BY
    `t_employee_info`.`c_job_level_sort` ASC,
    `t_employee_info`.`c_job_level_name` ASC;

没有子查询主键IN 每次查询一致

SELECT
    `t_employee_info`.`c_id`,
    `t_employee_info`.`c_job_level_sort`,
    `t_employee_info`.`c_emp_name`,
    `t_employee_info`.`c_job_level_name`
FROM
    `t_employee_info` 
WHERE
    (
    `t_employee_info`.`c_is_delete` = FALSE 
    AND `t_employee_info`.`c_company_id` = '93c6bff9c6154e469338c42d93a6ba92' 
    AND `t_employee_info`.`c_work_status` IN ( 1, 2, 3 ) 
    AND `t_employee_info`.`c_company_id` = '93c6bff9c6154e469338c42d93a6ba92' 
    AND `t_employee_info`.`c_id` IN (
'003e91414db04c5bb9d76960ee895c13','022b138df784466e82956e67747e7f56','033d230fe5b14c5da2ed19c735dc4ac3','03dc77f15e8845a6b3addca420f7c73c'
    ) 
    ) 
ORDER BY
    `t_employee_info`.`c_job_level_sort` ASC,
    `t_employee_info`.`c_job_level_name` ASC;

存在子查询每次查询不一致

SELECT
    `t_employee_info`.`c_id`,
    `t_employee_info`.`c_job_level_sort`,
    `t_employee_info`.`c_emp_name`,
    `t_employee_info`.`c_job_level_name`
FROM
    `t_employee_info` 
WHERE
    (
    `t_employee_info`.`c_is_delete` = FALSE 
    AND `t_employee_info`.`c_company_id` = '93c6bff9c6154e469338c42d93a6ba92' 
    AND `t_employee_info`.`c_work_status` IN ( 1, 2, 3 ) 
    AND `t_employee_info`.`c_company_id` = '93c6bff9c6154e469338c42d93a6ba92' 
    AND `t_employee_info`.`c_id` IN (
SELECT
    U0.`c_employee_id` 
FROM
    `t_company_organization_scope_employee` U0 
WHERE
    ( U0.`c_is_enable` = TRUE AND U0.`c_scope_id` = 'c2fb4268fe88a81ed8e89892a28a14f3' AND U0.`c_company_id` = '93c6bff9c6154e469338c42d93a6ba92' ) 
    ) 
    ) 
ORDER BY
    `t_employee_info`.`c_job_level_sort` ASC,
    `t_employee_info`.`c_job_level_name` ASC;

解决

ORDER BY
    `t_employee_info`.`c_job_level_sort` ASC,
    `t_employee_info`.`c_job_level_name` ASC,
    `t_employee_info`.`c_id` ASC;

分页偏移后默认排序失效

因上次子查询出现问题后，本次分页也出现问题。直接怀疑order by指定排序后会因不同条件打乱默认排序(姑且认为有) order by指定排序后一定要指定id升序

不同偏移排序不一致

SELECT
    `c_id`,
    `c_emp_name`,
    `c_department_id`,
    `c_hire_dt`,
    `c_add_dt`
FROM
    `t_employee_info` 
WHERE
    ( `t_employee_info`.`c_is_delete` = FALSE AND `t_employee_info`.`c_company_id` = '93c6bff9c6154e469338c42d93a6ba92' ) 
ORDER BY
    `t_employee_info`.`c_add_dt` DESC,
    `t_employee_info`.`c_hire_dt` DESC
LIMIT 7 -- 4,5,6,7排序打乱
OFFSET 0;

解决

SELECT
    `c_id`,
    `c_emp_name`,
    `c_department_id`,
    `c_hire_dt`,
    `c_add_dt`
FROM
    `t_employee_info` 
WHERE
    ( `t_employee_info`.`c_is_delete` = FALSE AND `t_employee_info`.`c_company_id` = '93c6bff9c6154e469338c42d93a6ba92' ) 
ORDER BY
    `t_employee_info`.`c_add_dt` DESC,
    `t_employee_info`.`c_hire_dt` DESC,
    `t_employee_info`.`c_id` ASC
LIMIT 7 -- 4,5,6,7排序一致
OFFSET 0;