MySQL中基于DATE字段计算DAY字段的问题：更新与自动计算

• 需要对SCHEDULE表的DAY字段填充数据：通过对DATE字段的值应用函数，将结果写入永久表
• 希望设置表结构，使未来插入新数据时，DAY字段能自动从DATE字段计算得出
• 目前修改表结构后，DAY字段得到的是日期的日份（如12），而非预期的星期名称（如Monday），结果不符合要求

相关表数据与结构 #

表数据查询结果 #

mysql> SELECT * FROM SCHEDULE;
+----+---------+------------+----------+----------+--------+------+
| ID | TYPE    | DATE       | START    | END      | VENUE  | DAY  |
+----+---------+------------+----------+----------+--------+------+
|  1 | SCHOOL  | 2022-09-12 | 07:15:00 | 12:30:00 | SCHOOL | NULL |
|  2 | SCHOOL  | 2022-09-13 | 07:15:00 | 12:30:00 | SCHOOL | NULL |
|  3 | SCHOOL  | 2022-09-14 | 07:15:00 | 12:30:00 | SCHOOL | NULL |
|  4 | FIITJEE | 2022-09-14 | 15:30:00 | 19:45:00 | C-14   | NULL |
|  5 | SCHOOL  | 2022-09-15 | 07:15:00 | 12:30:00 | SCHOOL | NULL |
|  6 | SCHOOL  | 2022-09-16 | 07:15:00 | 12:30:00 | SCHOOL | NULL |
|  7 | FIITJEE | 2022-09-17 | 09:30:00 | 16:15:00 | C-14   | NULL |
+----+---------+------------+----------+----------+--------+------+
7 rows in set (0.00 sec)

表结构描述 #

mysql> DESCRIBE SCHEDULE;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| ID    | int        | NO   | PRI | NULL    | auto_increment |
| TYPE  | varchar(7) | YES  |     | NULL    |                |
| DATE  | date       | YES  |     | NULL    |                |
| START | time       | YES  |     | NULL    |                |
| END   | time       | YES  |     | NULL    |                |
| VENUE | varchar(6) | YES  |     | NULL    |                |
| DAY   | varchar(9) | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

内容的提问来源于stack exchange，提问作者Animish Yadav