如何用MySQL按用户ID和日期查询最早最晚时间及对应IP

云原生阿岚

2026-8-6

解决方案

核心查询语句

针对你的需求，无需复杂的自连接，可通过分组聚合直接获取每个用户每日的起始和结束记录，适配MySQL 5.7的写法如下：

SELECT 
    userid,
    DATE_FORMAT(STR_TO_DATE(datetime, '%d-%m-%Y %H:%i'), '%d-%m-%Y') AS date,
    -- 获取当日最早时间对应的IP
    SUBSTRING_INDEX(GROUP_CONCAT(ipaddress ORDER BY STR_TO_DATE(datetime, '%d-%m-%Y %H:%i') ASC), ',', 1) AS `start ipaddress`,
    -- 提取当日最早时间的时分部分
    DATE_FORMAT(MIN(STR_TO_DATE(datetime, '%d-%m-%Y %H:%i')), '%H:%i') AS `start time`,
    -- 获取当日最晚时间对应的IP
    SUBSTRING_INDEX(GROUP_CONCAT(ipaddress ORDER BY STR_TO_DATE(datetime, '%d-%m-%Y %H:%i') DESC), ',', 1) AS `end ipaddress`,
    -- 提取当日最晚时间的时分部分
    DATE_FORMAT(MAX(STR_TO_DATE(datetime, '%d-%m-%Y %H:%i')), '%H:%i') AS `end time`
FROM your_table
GROUP BY userid, date;

说明

日期转换：原datetime字段是字符串格式，需用STR_TO_DATE转换为MySQL可识别的日期时间类型，再用DATE_FORMAT提取纯日期部分用于分组。
起始/结束IP获取：通过GROUP_CONCAT按时间排序拼接IP，再用SUBSTRING_INDEX取第一个值，分别对应最早和最晚时间的IP。
时间提取：用MIN/MAX获取当日最早/最晚时间，再提取时分部分。

百万级数据性能优化

针对大表场景，需从存储和索引层面优化：

优化字段类型：将datetime字符串字段改为DATETIME类型，避免每次查询都做格式转换。若无法修改原字段，可创建存储型虚拟列：
```
ALTER TABLE your_table ADD COLUMN dt_datetime DATETIME AS (STR_TO_DATE(datetime, '%d-%m-%Y %H:%i')) STORED;
```
创建联合索引：基于userid和虚拟列（或原生DATETIME字段）创建联合索引，大幅提升分组聚合效率：
```
CREATE INDEX idx_userid_dt ON your_table(userid, dt_datetime);
```
调整GROUP_CONCAT长度：若单用户单日记录极多，需调整group_concat_max_len参数避免截断（默认值1024），可在会话或全局配置：
```
SET SESSION group_concat_max_len = 102400;
```