Выборка последних сообщений пользователя в MySQL

Задача получения последнего сообщения от каждого пользователя - частая проблема при работе с чатами или мессенджерами. В этой статье разберём, как составить корректный SQL-запрос для таблиц users и messages, чтобы получить одно последнее сообщение для каждого отправителя.

Структура базы данных

Предположим, у вас есть две таблицы:

• users - содержит поля id, name, password;
• messages - содержит поля id, from_id, to_id, message.

Вам нужно получить для каждого пользователя (отправителя) только одно самое свежее сообщение. Рассмотрим несколько способов.

Способ 1: Подзапрос с MAX(id)

Самый простой и часто используемый метод - найти максимальный id сообщения для каждого from_id, так как id обычно автоинкрементный и возрастает с каждым новым сообщением.

SELECT m.*
FROM messages m
INNER JOIN (
    SELECT from_id, MAX(id) AS max_id
    FROM messages
    GROUP BY from_id
) latest ON m.from_id = latest.from_id AND m.id = latest.max_id;

Этот запрос группирует сообщения по отправителю, находит максимальный ID в каждой группе и затем присоединяет исходную таблицу, чтобы получить полные данные сообщения.

Способ 2: Использование подзапроса с корреляцией

Если в таблице нет автоинкремента или вы хотите работать с датой, можно использовать коррелированный подзапрос. Для примера добавим поле created_at (тип DATETIME).

SELECT m.*
FROM messages m
WHERE m.id = (
    SELECT m2.id
    FROM messages m2
    WHERE m2.from_id = m.from_id
    ORDER BY m2.created_at DESC
    LIMIT 1
);

Этот запрос для каждой строки внешней таблицы находит ID последнего сообщения того же отправителя и оставляет только те строки, которые совпадают. Работает медленнее на больших объёмах данных, но даёт гибкость.

Способ 3: Использование оконных функций (MySQL 8+)

Если ваша версия MySQL 8.0 или новее, оптимальным решением будет оконная функция ROW_NUMBER().

SELECT *
FROM (
    SELECT m.*,
           ROW_NUMBER() OVER (PARTITION BY from_id ORDER BY id DESC) AS rn
    FROM messages m
) ranked
WHERE rn = 1;

Здесь мы нумеруем сообщения для каждого отправителя в порядке убывания ID, затем оставляем только первую запись (номер 1). Такой подход легко читается и эффективен.

Выборка с информацией о пользователе

Чтобы дополнительно получить имя отправителя, присоедините таблицу users:

SELECT u.name, m.message, m.id
FROM messages m
INNER JOIN users u ON m.from_id = u.id
INNER JOIN (
    SELECT from_id, MAX(id) AS max_id
    FROM messages
    GROUP BY from_id
) latest ON m.from_id = latest.from_id AND m.id = latest.max_id;

Заключение

Выборка последнего сообщения для каждого пользователя реализуется несколькими способами: через подзапрос с MAX(), коррелированный подзапрос или оконные функции. Выбор зависит от версии MySQL и объёма данных. Для современных СУБД рекомендуется использовать ROW_NUMBER() - это наиболее производительный и понятный вариант.