无ID表每1-2秒获取最新记录的SQL查询方案咨询

核心思路是通过Date和Time列组合出完整的时间戳，然后筛选出最近1-2秒内新增的记录，或基于上次查询的时间获取增量记录。以下分不同数据库给出具体实现：

一、通用逻辑说明 #

由于Time列每日重置，必须结合Date才能唯一确定每条记录的时间点。首先需要将Date（日-月-年）和Time（毫秒）转换为数据库可识别的完整时间类型，再根据当前时间或上次查询的时间范围筛选记录。

二、分数据库实现 #

MySQL #

1. 直接查询最近2秒内的记录

SELECT *
FROM your_table
WHERE STR_TO_DATE(
    CONCAT(Date, ' ', 
           CONCAT(
               Time DIV 3600000, ':', 
               (Time MOD 3600000) DIV 60000, ':', 
               (Time MOD 60000) DIV 1000, '.', 
               Time MOD 1000
           )
    ), '%d-%m-%Y %H:%i:%s.%f'
) >= NOW() - INTERVAL 2 SECOND
ORDER BY STR_TO_DATE(
    CONCAT(Date, ' ', 
           CONCAT(
               Time DIV 3600000, ':', 
               (Time MOD 3600000) DIV 60000, ':', 
               (Time MOD 60000) DIV 1000, '.', 
               Time MOD 1000
           )
    ), '%d-%m-%Y %H:%i:%s.%f'
) DESC;

2. 高效增量查询（推荐）

如果需要避免重复查询已获取的记录，可以在应用中保存上次查询到的最大时间，下次查询仅获取该时间之后的记录：

-- 假设@last_fetched_time是应用中存储的上次查询的最大时间（格式为DATETIME(3)）
SELECT *
FROM your_table
WHERE STR_TO_DATE(
    CONCAT(Date, ' ', 
           CONCAT(
               Time DIV 3600000, ':', 
               (Time MOD 3600000) DIV 60000, ':', 
               (Time MOD 60000) DIV 1000, '.', 
               Time MOD 1000
           )
    ), '%d-%m-%Y %H:%i:%s.%f'
) > @last_fetched_time
ORDER BY STR_TO_DATE(
    CONCAT(Date, ' ', 
           CONCAT(
               Time DIV 3600000, ':', 
               (Time MOD 3600000) DIV 60000, ':', 
               (Time MOD 60000) DIV 1000, '.', 
               Time MOD 1000
           )
    ), '%d-%m-%Y %H:%i:%s.%f'
) ASC;

3. 性能优化：创建生成列和索引

为避免每次查询都计算完整时间，可添加生成列并创建索引：

-- 添加存储生成列
ALTER TABLE your_table
ADD COLUMN full_time DATETIME(3) AS (
    STR_TO_DATE(
        CONCAT(Date, ' ', 
               CONCAT(
                   Time DIV 3600000, ':', 
                   (Time MOD 3600000) DIV 60000, ':', 
                   (Time MOD 60000) DIV 1000, '.', 
                   Time MOD 1000
               )
        ), '%d-%m-%Y %H:%i:%s.%f'
    )
) STORED;

-- 创建索引
CREATE INDEX idx_full_time ON your_table(full_time);

之后查询可简化为：

SELECT *
FROM your_table
WHERE full_time >= NOW() - INTERVAL 2 SECOND
ORDER BY full_time DESC;

SQL Server #

1. 查询最近2秒内的记录

SELECT *
FROM your_table
WHERE 
    CONVERT(DATETIME2(3), CONVERT(DATE, Date, 105)) + 
    DATEADD(MILLISECOND, Time, '00:00:00') >= DATEADD(SECOND, -2, GETDATE())
ORDER BY 
    CONVERT(DATETIME2(3), CONVERT(DATE, Date, 105)) + 
    DATEADD(MILLISECOND, Time, '00:00:00') DESC;

2. 增量查询

-- @last_fetched_time为应用存储的上次查询最大时间（DATETIME2(3)类型）
SELECT *
FROM your_table
WHERE 
    CONVERT(DATETIME2(3), CONVERT(DATE, Date, 105)) + 
    DATEADD(MILLISECOND, Time, '00:00:00') > @last_fetched_time
ORDER BY 
    CONVERT(DATETIME2(3), CONVERT(DATE, Date, 105)) + 
    DATEADD(MILLISECOND, Time, '00:00:00') ASC;

3. 性能优化：计算列和索引

-- 添加计算列
ALTER TABLE your_table
ADD full_time AS (
    CONVERT(DATETIME2(3), CONVERT(DATE, Date, 105)) + 
    DATEADD(MILLISECOND, Time, '00:00:00')
);

-- 创建索引
CREATE INDEX idx_full_time ON your_table(full_time);

PostgreSQL #

1. 查询最近2秒内的记录

SELECT *
FROM your_table
WHERE 
    TO_DATE(Date, 'DD-MM-YYYY') + MAKE_INTERVAL(milliseconds => Time) >= NOW() - INTERVAL '2 seconds'
ORDER BY 
    TO_DATE(Date, 'DD-MM-YYYY') + MAKE_INTERVAL(milliseconds => Time) DESC;

2. 增量查询

-- last_fetched_time为应用存储的上次查询最大时间（TIMESTAMP类型）
SELECT *
FROM your_table
WHERE 
    TO_DATE(Date, 'DD-MM-YYYY') + MAKE_INTERVAL(milliseconds => Time) > last_fetched_time
ORDER BY 
    TO_DATE(Date, 'DD-MM-YYYY') + MAKE_INTERVAL(milliseconds => Time) ASC;

3. 性能优化：生成列和索引

-- 添加生成列
ALTER TABLE your_table
ADD COLUMN full_time TIMESTAMP GENERATED ALWAYS AS (
    TO_DATE(Date, 'DD-MM-YYYY') + MAKE_INTERVAL(milliseconds => Time)
) STORED;

-- 创建索引
CREATE INDEX idx_full_time ON your_table(full_time);

三、注意事项 #

• 确保Date列的格式严格为日-月-年，否则日期转换函数会出错；
• 如果Time列存储的是字符串而非数字，需先转换为数值类型（比如CAST(Time AS UNSIGNED)在MySQL中）；
• 跨天场景下，组合后的时间戳会自动处理日期切换，无需额外逻辑。

内容的提问来源于stack exchange，提问作者Alexandr X