CodeIgniter查询本地MySQL正常，生产MariaDB排序异常

表结构 #

• Accounts：id, name, email, nivel, verification
• Accounts_Profiles：id, user_id, status
• Accounts_Messages：id, sender (int), receiver (int)

当前查询语句 #

SELECT DISTINCT Accounts.id, Accounts.email, Accounts.name, Accounts.username, Accounts.avatar, Accounts.nivel, Accounts.verification 
FROM Accounts 
JOIN Accounts_Messages ON Accounts.id = Accounts_Messages.receiver 
ORDER BY Accounts_Messages.id DESC;

CodeIgniter实现代码 #

/*
    With the $this->db->distinct() I make sure that the users shown will not be repeated - It works!
*/
$this->db->distinct();
$this->db->select(
    array(
        "Accounts.id id",
        "Accounts.email email",
        "Accounts.name name",
        "Accounts.username username",
        "Accounts.avatar avatar",
        "Accounts.nivel nivel",
        "Accounts.verification verification",
        "Accounts_Profiles.status online",
        "(SELECT IF(COUNT(ACM.id) > 0, COUNT(ACM.id), null) FROM Accounts_Messages ACM WHERE ACM.receiver = '$login_user_id' AND ACM.sender = 'Accounts.id' AND ACM.is_read = '0') unread"
    )
);

/*
    Here I exclude the $login_user_id from the list, the admin users and also those who have not verified the account - It works!
*/
$this->db->join(
    "Accounts_Profiles",
    "Accounts_Profiles.user_id = Accounts.id", 
    "left"
);
$this->db->where(
    array(
        "Accounts.id !=" => $login_user_id,
        "Accounts.nivel !=" => 'administrator',
        "Accounts.verification !=" => 'false'
    )
);

/*
    Here, I sort messages by last sent. I check who the logged in user sent the last messages to and list those users - This only works on localhost.
*/
$this->db->join(
    "Accounts_Messages", 
    "Accounts.id = Accounts_Messages.receiver", 
    "left"
);
$this->db->order_by(
    "Accounts_Messages.id", 'DESC'
);

/*
    Returning the result
*/
return $this->db->limit($filters['limit'])->offset($filters['offset'])->get("Accounts")->result();

问题描述 #

该查询在本地MySQL环境正常运行，但部署到生产MariaDB服务器后排序异常。本地环境中用户列表按最后发送消息的顺序展示，但生产服务器中用户列表却按会话创建时间排序，即使给最早聊天的用户发送新消息，他们仍排在列表末尾。曾尝试添加$this->db->group_by( "Accounts.id" );但无效。

核心问题分析 #

MySQL和MariaDB对DISTINCT与ORDER BY的组合处理存在差异：当ORDER BY的字段不在SELECT列表中时，MariaDB可能无法正确保留排序逻辑，而是默认按表的主键顺序返回结果。此外，原代码中存在一个明显的SQL错误：子查询里的ACM.sender = 'Accounts.id'是把Accounts.id当成字符串匹配，而非字段关联，会导致未读消息计数错误。

修改步骤与代码实现 #

1. 修复子查询字段匹配错误：去掉Accounts.id的单引号，改为字段关联
2. 改用分组获取最新消息ID：先通过子查询获取每个对话接收者的最大消息ID，再以此为基准关联查询，确保排序逻辑稳定

修改后的CodeIgniter代码：

// 先构造子查询：获取当前用户发送消息的每个接收者的最新消息ID
$subquery = $this->db->select('receiver, MAX(id) as last_msg_id')
                     ->from('Accounts_Messages')
                     ->where('sender', $login_user_id)
                     ->group_by('receiver')
                     ->get_compiled_select();

$this->db->select(
    array(
        "Accounts.id id",
        "Accounts.email email",
        "Accounts.name name",
        "Accounts.username username",
        "Accounts.avatar avatar",
        "Accounts.nivel nivel",
        "Accounts.verification verification",
        "Accounts_Profiles.status online",
        // 修复子查询的字段引用错误
        "(SELECT IF(COUNT(ACM.id) > 0, COUNT(ACM.id), null) FROM Accounts_Messages ACM WHERE ACM.receiver = '$login_user_id' AND ACM.sender = Accounts.id AND ACM.is_read = '0') unread"
    )
);

// 关联用户资料表
$this->db->join("Accounts_Profiles", "Accounts_Profiles.user_id = Accounts.id", "left");

// 关联子查询获取的最新消息记录
$this->db->join("($subquery) AS last_msgs", "Accounts.id = last_msgs.receiver", "inner");

// 过滤条件
$this->db->where(
    array(
        "Accounts.id !=" => $login_user_id,
        "Accounts.nivel !=" => 'administrator',
        "Accounts.verification !=" => 'false'
    )
);

// 按最新消息ID倒序排序
$this->db->order_by("last_msgs.last_msg_id", 'DESC');

// 返回分页结果
return $this->db->limit($filters['limit'])->offset($filters['offset'])->get("Accounts")->result();

说明 #

• 改用子查询分组获取每个接收者的MAX(id)，确保每个用户只出现一次，替代原有的DISTINCT，避免排序逻辑被数据库忽略
• 修复子查询中的字段引用错误，保证未读消息计数正常
• 使用inner join关联子查询，确保只返回当前用户有过消息往来的用户，逻辑更严谨

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