mysql查询符合周期内的数据

介绍

在项目需求中需要获取查询符合周期内的数据可以参考此篇文章。用到了mysql中的IF(expr1 , expr2 , expr3)、DAYOFWEEK(NOW())、FIND_IN_SET(str,strlist) 、data(now())、time(now())

表结构

CREATE TABLE `ad_list` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `distributionChannel` varchar(100) NOT NULL DEFAULT '' COMMENT '投放渠道,
  `distributionCity` varchar(100) NOT NULL COMMENT '投放城市',
  `name` varchar(200) NOT NULL DEFAULT '' COMMENT '广告名称',
  `image` varchar(200) NOT NULL DEFAULT '' COMMENT '广告图',
  `distributionStartTime` date NOT NULL COMMENT '开始投放时间',
  `distributionEndTime` date NOT NULL COMMENT '结束投放时间',
  `playCycle` varchar(10) NOT NULL DEFAULT '' COMMENT '播放频率-周期',
  `weekTime` varchar(20) NOT NULL DEFAULT '' COMMENT '1-7代表周一到周日 多个逗号分隔',
  `playStartTime` time NOT NULL COMMENT '播放频率-开始时间(通用)',
  `playEndTime` time NOT NULL COMMENT '播放频率-结束时间(通用)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=209 DEFAULT CHARSET=utf8mb4 COMMENT='广告表';

操作界面

投放周期类型 playCycle 的值可以为day 或 week。

需求

查找当前符合投放周期广告数据

SELECT
*
FROM
	`yun_ad_list` 
WHERE
IF
	(
		playCycle = 'day',
		( TIME ( NOW()) BETWEEN playStartTime AND playEndTime ),
		( FIND_IN_SET( DAYOFWEEK( NOW()), weekTime ) AND TIME ( NOW()) BETWEEN playStartTime AND playEndTime ) 
	) 
	AND DATE (
	NOW()) BETWEEN distributionStartTime 
	AND distributionEndTime;

在这个查询语句中，我们使用了IF()函数来根据字段值来确定两种不同的查询条件。

如果字段值为day，则执行 (TIME(NOW()) BETWEEN '09:00:00' AND '18:00:00')，即按天时段查询。
如果字段值为week，则执行 (FIND_IN_SET(DAYOFWEEK(NOW()), weekTime) AND TIME(NOW()) BETWEEN '09:00:00' AND '18:00:00')，即按周期查询。
请注意，在按周期查询时，我们使用了FIND_IN_SET()函数来检查当前日期的周几（使用DAYOFWEEK(NOW())）是否在weekTime字段中。同时，我们还判断了当前时间是否在指定的时段内。
最后我们使用了AND DATE(NOW()) BETWEEN distributionStartTime AND distributionEndTime 来查询当前时间是否在大的投放周期内。

注意

DAYOFWEEK(NOW()) 周一返回的是2，西方以周日为一周的开始。如果设定上1-7代表周一到周日。则需要其他语言进行处理后代入语句中进行查询

要让MySQL将周一作为一周中的第一天而不是第二天，你可以使用SET语句修改@@global.sql_mode变量，将其设置为合适的模式。具体来说，你可以设置@@global.sql_mode为'NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,WEEKDAY=0'。

以下是修改@@global.sql_mode的完整步骤：

1.使用管理员权限登录到你的MySQL服务器。

2.运行以下SQL语句来修改@@global.sql_mode变量：

SET GLOBAL sql_mode = 'NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,WEEKDAY=0';

3. 重启MySQL服务器，以确保修改生效。

现在，SELECT DAYOFWEEK(NOW());语句将返回1，表示周一是一周中的第一天。

请注意，这会影响整个MySQL服务器上的所有数据库和连接。如果只想对当前会话生效，可以使用SET SESSION sql_mode = ...来设置sql_mode变量。而且，修改@@global.sql_mode可能需要管理员权限，因此请确保你有足够的权限来执行这些操作。