MySQL数据库如何存储一张动态价格表

Question

背景：以MySQL数据库存储一张商品价格表，同一个商品的价格会不断变化，有如下五个字段：

`id`               bigint   comment   'ID'
`item`             bigint   comment   '商品ID'
`price`            int      comment   '商品价格'
`effective`        bit      comment   '是否生效'
`effective_date`   date     comment   '生效日'
`disable_date`     date     comment   '失效日'

同一个商品前后两次录入不同的价格：
id=1, item=1001, price=5, effective=true, effective_date='2023-10-25', disable_date='2023-11-05'
id=2, item=1001, price=6, effective=true, effective_date='2023-11-01', disable_date='2023-11-10'
id=3, item=1001, price=7, effective=true, effective_date='2023-11-05', disable_date='2023-11-15'

问题1：
假设现在是11月3日，如何设计查询以取得有效期内的最新价格？

问题2：
假设现在是11月3日，对于id为1的记录，要不要做什么操作以标识该价格记录已失效？或者，系统要不要每天根据“失效日”自动更新effective字段的值？烦请指导操作步骤？

问题3：
请问如何设计或者操作这三个字段：effective、effective_date、disable_date，以使每天同一个商品只有一个有效价格？

谢谢。

Answer 1

引自强大而免费的微信小程序：皆我百晓生。

问题1：

可以使用以下SQL查询语句取得有效期内的最新价格：

SELECT price
FROM 商品价格表
WHERE item = 1001
AND effective = true
AND effective_date <= CURDATE()
AND disable_date >= CURDATE()
ORDER BY effective_date DESC
LIMIT 1;

该查询语句会按照生效日期降序排序，即最新的记录会排在前面，然后限制返回结果中的记录数为1，即取最新的记录的价格。

问题2：

对于id为1的记录，可以在今天的日期（11月3日）之前设置失效日期为11月2日，并将有效字段设置为false，表示该价格记录已失效。这可以通过以下SQL更新语句完成：

UPDATE 商品价格表
SET effective = false, disable_date = '2023-11-02'
WHERE id = 1;

系统可以每天根据当前日期和失效日期的比较，自动更新价格记录的有效字段。

问题3：

为了保证每天同一个商品只有一个有效价格，可以在插入新的价格记录之前，先更新该商品之前的价格记录的失效日期为当前日期，并将有效字段设置为false。然后再插入新的价格记录，并设置新的生效日期和失效日期。这可以通过以下SQL语句完成：

UPDATE 商品价格表
SET effective = false, disable_date = CURDATE()
WHERE item = 1001
AND effective = true;

INSERT INTO 商品价格表 (item, price, effective, effective_date, disable_date)
VALUES (1001, 8, true, '2023-11-03', '2023-11-13');

以上操作会将之前的价格记录失效，并在当前日期插入新的价格记录。注意，在插入新的价格记录之前，需要确保之前的价格记录已经失效。