从日期选择星期几 [英] Select day of week from date

问题描述

我在MySQL中有一张下表，其中记录了每天发生的事情的事件计数

I have the following table in MySQL that records event counts of stuff happening each day

event_date     event_count
2011-05-03     21
2011-05-04     12
2011-05-05     12

我希望能够按日期范围和星期几有效地查询此内容.例如-五月的星期二的event_count是多少?"

I want to be able to query this efficiently by date range AND by day of week. For example - "What is the event_count on Tuesdays in May?"

当前，event_date字段是日期类型. MySQL中是否有任何功能可以让我按星期几查询此列，还是应该在表中添加另一列来存储星期几?

Currently the event_date field is a date type. Are there any functions in MySQL that let me query this column by day of week, or should I add another column to the table to store the day of week?

该表将包含成千上万的行，因此在选择之后，我将选择最有效的解决方案(而不是最简单的解决方案).

The table will hold hundreds of thousands of rows, so given a choice I'll choose the most efficient solution (as opposed to most simple).

推荐答案

使用

Use DAYOFWEEK in your query, something like:

SELECT * FROM mytable WHERE MONTH(event_date) = 5 AND DAYOFWEEK(event_date) = 7;

这将查找五月的星期六的所有信息.

This will find all info for Saturdays in May.

要获得最快的读取速度，请存储非规范化字段，该字段是星期几(以及您需要的其他任何内容).这样，您可以为列编制索引并避免全表扫描.

To get the fastest reads store a denormalized field that is the day of the week (and whatever else you need). That way you can index columns and avoid full table scans.

只需首先尝试上面的方法，看看它是否满足您的需求，如果不合适，请添加一些额外的列并在写入时存储数据.只需注意更新异常(如果更改event_date，请确保更新day_of_week列).

Just try the above first to see if it suits your needs and if it doesn't, add some extra columns and store the data on write. Just watch out for update anomalies (make sure you update the day_of_week column if you change event_date).

请注意，非规范化字段将增加执行写操作所需的时间，增加写操作的时间，并占用更多空间.确保您确实需要该好处，并且可以衡量它是否对您有帮助.

Note that the denormalized fields will increase the time taken to do writes, increase calculations on write, and take up more space. Make sure you really need the benefit and can measure that it helps you.

这篇关于从日期选择星期几的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！