MySQL 从日期中选择星期几
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5967426/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select day of week from date
提问by Kevin
I have the following table in MySQL that records event counts of stuff happening each day
我在 MySQL 中有下表记录了每天发生的事情的事件计数
event_date event_count
2011-05-03 21
2011-05-04 12
2011-05-05 12
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_count 是多少？”
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?
目前 event_date 字段是日期类型。MySQL 中是否有任何函数可以让我按星期几查询此列，还是应该向表中添加另一列来存储星期几？
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).
该表将包含数十万行，因此如果有选择，我将选择最有效的解决方案（而不是最简单的解决方案）。
回答by Henry
Use DAYOFWEEKin your query, something like:
在您的查询中使用DAYOFWEEK，例如：
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 readsstore 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.
为了获得最快的读取，存储一个非规范化字段，即星期几（以及您需要的任何其他内容）。这样你就可以索引列并避免全表扫描。
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).
只需先尝试上述操作，看看它是否适合您的需求，如果不适合，请添加一些额外的列并在写入时存储数据。请注意更新异常（如果更改 event_date，请确保更新 day_of_week 列）。
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.
请注意，非规范化字段会增加写入所需的时间，增加写入计算并占用更多空间。确保您确实需要该福利，并且可以衡量它是否对您有所帮助。
回答by Thomas Li
Check DAYOFWEEK()function
检查DAYOFWEEK()函数
回答by Jokerius
If you want textual representation of day of week - use DAYNAME() function.
如果您想要星期几的文本表示 - 使用 DAYNAME() 函数。
