店铺是否营业功能该如何设计?

Question

需求是可以给店铺配置周一至周日每日是否营业,可以选择24小时营业，不营业，多时间段营业，跨天营业。并且这个要考虑时区,不同时区的周一08:00-18:00是不一样的。

最后实现查询店铺时过滤掉不营业的店铺。

配置json,一个对周一至周日多时间段的配置。

{"stopBusiness":false,"timeZone":"Canada/Yukon","weekdayConfig":[{"index":0,"noBusiness":false,"allDay":false,"timeSegment":["15:25-03:00","01:16-07:11"]},{"index":1,"noBusiness":false,"allDay":true,"timeSegment":[]},{"index":2,"noBusiness":false,"allDay":true,"timeSegment":[]},{"index":3,"noBusiness":false,"allDay":false,"timeSegment":["00:51-23:51","15:05-19:05"]},{"index":4,"noBusiness":false,"allDay":true,"timeSegment":[]},{"index":5,"noBusiness":false,"allDay":true,"timeSegment":[]},{"index":6,"noBusiness":false,"allDay":true,"timeSegment":[]}]}

这样的配置是没办法在数据库层面解析判断的,我现在的做法是把时间段转换成一个分钟数组,比如周一的08:00-18:00相当于是560-1080,周二的08:00-18:00相当于是2000-2520
最终生成一个int[] timeSegment = new int[]{560,1080,2000,2520}，再把这个时间段根据服务器时区和配置时区的差值做修正，就得到服务器时区下的一个多时段配置。后续判断是否营业只需要看某个时间点是否在timeSegment的某个区间内。

在java端维护所有营业时间数据，每次数据库查询，先在程序中找出所有营业店铺,然后sql中使用

select *** from shop where shop_id in (营业中店铺id列表)

现在有个问题就是,如果店铺量很多时,上千上万个店铺在营业,这个sql就很长,感觉不合理。
如果在数据库中使用一个字段标记是否营业,需要定时任务，不停的修改为营业或者不营业,这个频率应该很低,十几个小时一次。
我很想知道这种场景下，最优的处理方式是什么样子的？这种频繁变动的字段是否合适，是否需要特别处理？是否需要单独一个表(店铺id，营业状态)来处理?

Answer 1

要不就这样,每天凌晨算出当天营业的店铺,塞入缓存。这样想查询结果就可以直接查询缓存就行

Answer 2

如果是配置设定营业时间，可以考虑从数据结构上进行一层封装（没有什么是加一层无法实现的）。

根据楼主描述，目前的数据结果为，店铺是一个实体，其他设定的所有数据都在另外一个实体里，可在中间增加一层【营业日历】去进行处理，类似于ERP或生产系统常用到的【生产日历】。

---

设计表结构如下：

CREATE TABLE `operate_worktime` (
  `FID` INT(11) NOT NULL AUTO_INCREMENT,
  `FNAME` VARCHAR(30) NOT NULL COMMENT '名称',
  `FSTARTTIME` VARCHAR(10) DEFAULT NULL COMMENT '起始时间',
  `FENDTIME` VARCHAR(10) DEFAULT NULL COMMENT '结束时间',
  `FCROSSDAY` INT(1) DEFAULT '0' COMMENT '是否跨天',
  `FTIMEZONE` VARCHAR(50) DEFAULT '0' COMMENT '时区',
  `FDESCRIPTION` VARCHAR(50) DEFAULT NULL COMMENT '描述 ',
  PRIMARY KEY (`FID`)
) ENGINE=INNODB COMMENT='班次';

CREATE TABLE `operate_calendar` (
  `FID` INT(11) NOT NULL AUTO_INCREMENT,
  `FNAME` VARCHAR(50) NOT NULL COMMENT '名称',
  PRIMARY KEY (`FID`)
) ENGINE=INNODB  COMMENT='运营日历';

CREATE TABLE `operate_calendar_detail` (
  `FID` INT(11) NOT NULL AUTO_INCREMENT,
  `FOPERATECALENDARID` INT(11) NOT NULL COMMENT '运营日历ID',
  `FDATE` DATE NOT NULL COMMENT '日期',
  `FDATESTRING` VARCHAR(10) NOT NULL COMMENT '字符格式日期',
  `FYEAR` INT(5) NOT NULL COMMENT '年',
  `FMONTH` INT(5) NOT NULL COMMENT '月',
  `FDAY` INT(5) NOT NULL COMMENT '日',
  `FSTARTTIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '起始时间',
  `FENDTIME` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '结束时间',
  `FTIMESTRING` VARCHAR(15) NOT NULL COMMENT '字符格式时间',
  `FHOURS` DECIMAL(19,6) DEFAULT NULL COMMENT '工时（小时）',
  `FMINUTES` DECIMAL(19,6) DEFAULT NULL COMMENT '工时（分钟）',
  `FDESCRIPTION` VARCHAR(50) NOT NULL COMMENT '描述',
  `FCROSSDAY` INT(1) DEFAULT '0' COMMENT '是否跨天',
  `FTIMEZONE` VARCHAR(50) DEFAULT '0' COMMENT '时区',
  PRIMARY KEY (`FID`),
  KEY `index_1` (`FOPERATECALENDARID`)
) ENGINE=INNODB AUTO_INCREMENT=10416 DEFAULT CHARSET=utf8 COMMENT='运营日历明细';

班次：
基础资料，用来定义营业时间，仅含time，不含date。如：白班（夏），起始时间8:00，结束时间18:00，不跨天，上海时区。

运营日历：
两个字段而已，用来从数据结构上进行拆分具体的运营日期+时间。具体编辑和操作模式看下图，下图为我设计的工厂日历，左上是班次，左下是日历，可拖动到右边的日历组件里，把time和date组合起来，生成明细的数据。

运营日历明细：
作为运营日历的子表，用来进行date和Time数据的存储。

---

最终在店铺基础资料上绑定运营日历即可，一般店铺的运营时间不会每天都不一样，所以提取基础资料出来应该算是比较好的方法了。

关于取数的问题，可从时间区间上去过滤，例如使用日历组件，每次只需要加载一个月的数据，通过这种方式去优化，比想办法优化取数结构更切合实际且不容易出问题。

以上仅供参考。

Answer 3

把营业时间单独拉一张表，每个店铺每天的每个营业时间段都作为该表的一条记录，查询哪些正在营业只要查询该表的哪些营业时间段包含当前时间就行了

CREATE TABLE stores (
  id INT AUTO_INCREMENT PRIMARY KEY COMMENT '商店ID',
  name VARCHAR(255) COMMENT '商店名称'
);

CREATE TABLE business_hours (
  id INT AUTO_INCREMENT PRIMARY KEY,
  store_id INT COMMENT '商店ID',
  day_index INT COMMENT '日期索引，0~6表示周一到周日',
  timezone VARCHAR(10) COMMENT '时区';
  open_time TIME COMMENT '营业起始时间',
  close_time TIME COMMENT '营业结束时间'
);

新增数据

INSERT INTO business_hours (store_id, day_index, timezone, open_time, close_time) VALUES
  (1, 0, 'America/Los_Angeles', '08:00:00', '12:00:00'), -- 周一上午营业时间
  (1, 0, 'America/Los_Angeles', '13:00:00', '17:00:00'), -- 周一下午营业时间
  (1, 0, 'America/Los_Angeles', '18:00:00', '22:00:00'), -- 周一晚上营业时间
  ...;

查询正在营业的商店

-- 获取当前时刻的 UTC 时间和星期几
SET @utc_now = CONVERT_TZ(NOW(), @@global.time_zone, 'UTC');
SET @current_day_index = WEEKDAY(@utc_now);

-- 查询正在营业的商店
SELECT s.* FROM stores AS s
JOIN business_hours AS bh ON s.id = bh.store_id
WHERE bh.day_index = @current_day_index
  AND @utc_now BETWEEN CONVERT_TZ(bh.open_time, bh.timezone, 'UTC') AND CONVERT_TZ(bh.close_time, bh.timezone, 'UTC');

Answer 4

建议的表结构可以参考一下：

CREATE TABLE stores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE store_timezones (
  store_id INT PRIMARY KEY,
  timezone VARCHAR(50),
  FOREIGN KEY (store_id) REFERENCES stores(id)
);

CREATE TABLE business_hours (
  id INT AUTO_INCREMENT PRIMARY KEY,
  store_id INT,
  day_index INT,
  open_time TIME,
  close_time TIME,
  FOREIGN KEY (store_id) REFERENCES stores(id)
);

查询正在营业的商店：

-- 获取当前时刻的 UTC 时间和星期几
SET @utc_now = CONVERT_TZ(NOW(), @@global.time_zone, 'UTC');
SET @current_day_index = WEEKDAY(@utc_now);

-- 查询正在营业的商店
SELECT s.* FROM stores AS s
JOIN store_timezones AS stz ON s.id = stz.store_id
JOIN business_hours AS bh ON s.id = bh.store_id
WHERE bh.day_index = @current_day_index
  AND @utc_now BETWEEN CONVERT_TZ(bh.open_time, stz.timezone, 'UTC') AND CONVERT