从一个存储过程说说mysql中日期函数的参数是否必须是日期格式

文章目录

Mysql中的时间函数
创建时间索引，查询时使用字符串索引是否失效
满足要求的存储过程

因测试需要。创建数据要求如下：

指定起始日期，指定结束日期(默认是当前日)，指定每天每张表产生的数据条数。计算起始日期到结束日期的间隔天数。表中时间戳字段在循环时自动加。
使用mysql版本(5.6)。

表结构如下：

CREATE TABLE `student` (
  `id` int(15) NOT NULL AUTO_INCREMENT,
  `Sname` varchar(32) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `cardID` int(12) DEFAULT NULL,
  `Birthday` date DEFAULT NULL,
  `Email` varchar(40) DEFAULT NULL,
  `Class` varchar(20) DEFAULT NULL,
  `enterTime` datetime DEFAULT NULL,
  `drp_modify_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `idx_drp_modify_time` (`drp_modify_time`)
) ENGINE=InnoDB AUTO_INCREMENT=11560 DEFAULT CHARSET=utf8

Mysql中的时间函数

mysql日期类型有三种：Date,DateTime,timestamp。下面统称为日期类型。

NOW()	返回当前的日期和时间
函数	描述
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间按的单独部分
DATE_ADD()	给日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间
STR_TO_DATE()	把字符串转换为日期格式
TO_DAYS()	返回格林乔治时间到今天的天数

其中DATE_ADD()、DATE_SUB()、DATEDIFF()、TO_DAYS()中的函数参数均可是字符串或是日期类型，若是字符串必须是能隐式转换的字符串。
比如DATE_ADD()函数
SELECT DATE_ADD("2020-07-06", interval 1 day);结果2020-07-07数字是间隔的天数。interval 数字后面可以跟day、week、month、year。跟mysql 的其它关键字一样他们不区分大小写。

DATEDIFF()是返回两个日期之间的天数。是之间的间隔。SELECT DATEDIFF(“2020-07-07”,“2020-07-01”);返回结果是6不是7。
注意:
2020-07-06也可以写成2020/07/06也能写成2020:07:06。对中间的连接符没有要求，但一般按照习惯连接符写成-。隐式转换的字符串，不能带有字母，不符合的年月日小时分钟等，比如2020-17-16、y2020-07-06等。

创建时间索引，查询时使用字符串索引是否失效

添加列。注意只能用after不能用before。

alter table student ADD  date_test DATETIME DEFAULT now() 
comment '日期测试' after Class;

添加索引

CREATE   INDEX IDX_DATE_TEST  ON  student (date_test);

查看sql的执行计划，数据请自己造。

-- 可以把date_test换成drp_modify_time也是一样的，都使用了索引
explain select * from student where date_test='2020-07-06';

可以看出使用到了索引。因此在查询时也无需使用函数把字符串转换为日期类型。
参考资料

满足要求的存储过程

DELIMITER $$
-- 赋值变量时可以直接使用函数赋值
CREATE  PROCEDURE `lipp3`(IN iCount INT,IN startDate VARCHAR(30))
BEGIN
    DECLARE intervalK INT;
	DECLARE i INT;
	DECLARE	inumStudent INT;
    DECLARE intervalDay INT;
    DECLARE hourAndMinute VARCHAR(30);
   
    -- 计算差值不好含本天，需要加1
    SET intervalDay = DATEDIFF(now(),startDate )+1;
    SET intervalK =1;
    SET i =1;
    -- 开启事务要么都成功要么不成功，便于控制
    START TRANSACTION;
    WHILE intervalK<= intervalDay DO
      -- 初始化id
      SELECT if(max(id) is null,0,max(id)) INTO inumStudent FROM student;
    -- 其它表需要插入数据也需要初始化id
	  	WHILE i <= iCount DO
      -- 暂时随机到秒级 有多个时使用CONCAT_WS(separator,str1,str2,...)，自动拼接的连接符。
      set hourAndMinute = CONCAT(FLOOR(RAND()*24),":",FLOOR(RAND()*60),":",FLOOR(RAND()*60));
			INSERT INTO student VALUES(
			inumStudent+i,
      CONCAT('阿兰',inumStudent+i),
			IF(CEIL(RAND()*10)%2=0,'男','女'),
			inumStudent,
			CONCAT(CONVERT(CEIL(RAND()*10)+1980,CHAR(4)),'-',LPAD(CONVERT(CEIL(RAND()*12),
			CHAR(2)),2,'0'),'-',LPAD(CONVERT(CEIL(RAND()*28),CHAR(2)),2,'0')),
			CONCAT(CONCAT('alan',inumStudent+i),'@hotmail.com'),
			CASE CEIL(RAND()*3) WHEN 1 THEN '网络与网站开发' WHEN 2 THEN '计算机科学技术' ELSE '汇编语言初入门' END,
			NOW(),
       -- mysql自动隐式转换。不需要使用函数强转
			CONCAT(yearAndDay," ",hourAndMinute)
      );
      -- 这可以添加任何表进行插入
	   SET i =i+1;
		 END WHILE;
     SET i=1;
     SET intervalK =intervalK+1;
     SET yearAndDay =date_add(yearAndDay, interval 1 day);
    END WHILE;
    COMMIT;
    -- 不使用分号$$与end之间有空格
END $$

DELIMITER ;

总结：
①DATE_ADD()、DATE_SUB()、DATEDIFF()、TO_DAYS()函数在计算时，可以不用把字符串转换成日期类型。
②查询插入时，也可以不用把字符串转换成日期类型。在写sql的时候就变得简单。
每篇一语：

马行千里，不洗尘沙。——余秋雨

本文地址：https://blog.csdn.net/lppzyt/article/details/107169596

从一个存储过程说说mysql中日期函数的参数是否必须是日期格式

文章目录

Mysql中的时间函数

创建时间索引，查询时使用字符串索引是否失效

满足要求的存储过程

相关推荐