选择在特定日期输入的所有记录 - MySQL [英] Selecting all records entered in a particular day - MySQL

问题描述

我目前正在为一个网站制作邮箱，在数据库中保存大量邮件，根据日期可以选择过滤邮件。我在混淆使用哪种方法和如何。

I am currently working on a Mailbox for a website, holding a large number of messages within a database, where there is an option to filter the mails according to the date. I am in a confusion as of which method to use and how to.

要使用一个TIMESTAMP列，并仅根据DATE部分选择记录。考虑到TIMESTAMP是要做到这一点的数据类型，这似乎更好。但是，当过滤时，分割（到日期和时间）和比较不会更昂贵。如果更好的话，如何进行比较？ （输入：yyyy-mm-dd）

To use a TIMESTAMP column and select the records based on the DATE part only. This seems to be better considering that the TIMESTAMP is the datatype meant to do this. But when filtering, wouldn't the splitting (to date and time) and comparisons be more expensive. If better, how to perform the comparison? (Input : yyyy-mm-dd)

日期。然后将日期字段值与过滤器参数进行比较（格式为yyyy-mm-dd）。插入一个新的记录（邮件）似乎是昂贵的，这只是一次只发生一次。但是，过滤需要比较大量的记录。所以，似乎比较直截了当。
另外在方法二中，我设置默认值为CURRENT_DATE和CURRENT_TIME！

To use a column each for TIME and DATE. Then compare the date field value to the filter param (of the format : yyyy-mm-dd). This seems expensive at inserting a new record (mail), which happens only one at a time. But the filtering requires comparison of a large number of records. So, seems to be more straight forward. Also in method two, I am having a problem setting the default value as the CURRENT_DATE and CURRENT_TIME!

这是表创建代码：

CREATE TABLE mailbox (
    Mid INT NOT NULL AUTO_INCREMENT,
    FromId INT NOT NULL,
    ToId INT NOT NULL,
    Subject VARCHAR(256) DEFAULT 'No Subject',
    Message VARCHAR(2048) DEFAULT 'Empty Mail',
    SDate DATE DEFAULT CURRENT_DATE,
    STime TIME DEFAULT CURRENT_TIME,
    PRIMARY KEY (Mid),
    );

请帮助...

推荐答案

我将使用方法1，并使用

I would use method 1 and do the filtering with

WHERE 
     your_timestamp >= search_date 
AND 
     your_timestamp < search_date + INTERVAL 1 DAY

在这种情况下，MySQL可以使用索引。

assuming your search_date is of type DATE. MySQL can use an index in this case.

请参阅这个小提琴。
查看执行计划以验证索引的使用。

See this fiddle. Have a look at the execution plan to verify the use of the index.

这篇关于选择在特定日期输入的所有记录 - MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！