MySQL基础之日期和时间类型笔记

短命女 2021-08-13 16:40 446阅读 0赞

最近在看《MySQL技术内幕：SQL编程》并做了笔记，这是一篇笔记类型博客，分享出来方便自己复习，也可以帮助其他人

### 一、日期时间类型所占空间对比 ###

各种日期时间数据类型所占的空间：

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>所占空间</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>DATETIME</td> 
   <td>8字节</td> 
  </tr> 
  <tr> 
   <td>DATE</td> 
   <td>3字节</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP</td> 
   <td>4字节</td> 
  </tr> 
  <tr> 
   <td>YEAR</td> 
   <td>1字节</td> 
  </tr> 
  <tr> 
   <td>TIME</td> 
   <td>3字节</td> 
  </tr> 
 </tbody> 
</table>

### 二、DATETIME和DATE对比 ###

*  DATETIME占用8字节，既显示了日期也显示时间，可以表示的日期范围为“1000-01-01 00:00:00”到“9999-12-31 23:59:59”
 *  DATE占用3字节，只显示日期，不显示具体时间，可显示的日期范围为“1000-01-01”到“9999-12-31”

ok，这里特意介绍一下TIMESTAMP秒的小数部分问题

> 备注：5.6.4+版本才支持秒的小数部分，之前版本是不支持的

# 查询MySQL版本
    select version();
    # 建表验证问题
    create table t (a datetime);
    # 写数据秒后面加上小数
    insert into t select '2019-10-11 17:16:12.55555';
    # 查询，发现并没有查出秒之后的小数
    select * from t ;
    # 使用microsecond，读取秒之后的小数
    select microsecond('2019-10-11 17:16:12.55555') ;
    # CAST读取，验证了5.7+版本查询时候会出现四舍五入，如下sql得到2019-10-11 17:16:12，而低版本就不会
    SELECT CAST('2019-10-11 17:16:12.5555' AS DATETIME) ;
    # CAST读取，5.7+版本查询，四舍五入得到，2019-10-11 17:16:12，低版本正常显示，具体哪个版本开始的不知道，我在5.7+版本验证都是会出现四舍五入的情况
    select cast('2019-10-11 17:16:12.1234' as datetime) ;
    # 5.6.4+版本支持秒的小数部分
    # 支持的类型有TIME、DATETIME、TIMESTAMP，写法是type(size)，size为小数部分精度，最大为6
    # 删表，再验证一下
    DROP TABLE t;
    # 这里指定精度
    CREATE TABLE t (a DATETIME(4));
    # 秒后加小数，写数据
    INSERT INTO t SELECT '2019-10-11 17:16:12.55555';
    # 查询，发现可以正常写数据，不过精度只有4
    SELECT * FROM t ;

### 三、TIMESTAMP类型介绍 ###

TIMESTAMP占4个字节，显示的范围为“1970-01-01”UTC 到 “2038-01-19 03:14:07”UTC  
备注：UTC：协调世界时间、又称世界统一时间、世界标准时间和国际协调时间

> 注意：
> 
>  *  更新表时，可以设置TIMESTAMP类型的列，自动更新时间为当前时间
>  *  列为TIMESTAMP的日期类型可以设置一个默认值，而DATETIME不支持

例子，验证一下，设置默认值和自动更新时间

# 新增一张表
    CREATE TABLE t (
      a INT,
      b TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE = INNODB ;
    # 写一条数据
    INSERT INTO t (a) VALUES (1);
    # 查询，发现自动赋默认值时间
    SELECT * FROM t;

![在这里插入图片描述][20191013153008749.png]

验证自动更新时间问题

# 修改字段为自动更新(数据有改变时候才会自动更新)
    ALTER TABLE t MODIFY COLUMN b TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
    # 删一下表数据
    DELETE FROM t;
    # 写初始化数据
    INSERT INTO t SELECT 1,CURRENT_TIMESTAMP;
    # 查询，先记录下原来时间
    SELECT * FROM t;

![在这里插入图片描述][2019101314181190.png]

# 修改数据
    UPDATE t SET a =2;
    # 如果修改为1，是不会改变时间的
    SELECT * FROM t;

![在这里插入图片描述][20191013152950542.png]

### 四、YEAR和TIME类型对比 ###

*  YEAR类型占用1字节，书中介绍定义时可以指定显示的宽度为YEAR(2)或者YEAR(4)，不过在我的mysql版本5.7+验证，发现只能定义为YEAR或者YEAR(4)，也就是新版是不支持YEAR(2)这种形式的  
    对于YEAR(4)，其显示年份的范围为1901~2155；然后超过这个范围的话，mysql还可以写，不过被赋值为0000
 *  TIME类型只占3字节，显示的范围为“-838:59:59”~“838:59:59”，TIME的小时是可以大于23也可以为负值的，因为time也可以用来保存时间间隔

### 五、日期时间函数 ###

*  NOW、CURRENT\_TIMESTAMP和SYSDATE函数

介绍一下MySQL比较常用的NOW、CURRENT\_TIMESTAMP和SYSDATE函数

给个例子，用sleep函数，然后对比sleep函数执行前后，这几个函数获取的时间对比

SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE(),SLEEP(2),NOW(),CURRENT_TIMESTAMP(),SYSDATE();

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]  
从图对比可知，NOW()其实就是CURRENT\_TIMESTAMP()的近意函数，例子里使用了sleep(2)，隔2秒继续执行，可以对比得知，NOW、CURRENT\_TIMESTAMP其实获取的都是整条sql开始执行的时间，不管在sleep函数执行前后，而SYSDATE获取的其实执行sysdate这个函数时候的时间，并非整条sql开始执行的时间，所以在sleep函数执行前后获取的时间是不同的

*  DATE\_ADD和DATE\_SUB函数

DATE\_ADD(date , INTERVAL expr type)和DATE\_SUB(datte , INTTERVAL expr type)，expr可以为负数，所以DATE\_ADD既可以用于日期相加，也可以用于日期相减。type可以为YEAR、MONTH、DAY、HOUR、MINUTE、SECOND

SELECT NOW()AS NOW,DATE_ADD(NOW(),INTERVAL 1 DAY) AS tomorrow,DATE_SUB(NOW(),INTERVAL 1 DAY);

![在这里插入图片描述][20191013154611655.png]

> 备注：闰月问题，如果是闰月就返回29日，不是闰月就返回28日

SELECT DATE_ADD('2004-2-29',INTERVAL 1 YEAR);
    SELECT DATE_ADD('2004-2-29',INTERVAL 4 YEAR);

![在这里插入图片描述][20191013171856738.png]  
![在这里插入图片描述][2019101317193973.png]

*  DATE\_FORMAT函数  
    DATE\_FROMAT和Oracle中的to\_char功能有点类型，作用是按照用户定义的格式打印数据

SELECT DATE_FORMAT(NOW(),'%Y%m%d');

[20191013153008749.png]: /images/20210813/cc896e7391ee4f48812fa3a21a54fbc0.png
[2019101314181190.png]: /images/20210813/0eeecc6d8cc945a6a15165193f69daff.png
[20191013152950542.png]: /images/20210813/ac1aff7e3bab4b38851f16ef8291eca9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]: /images/20210813/ea34e4635a5f407cae2d8a86d3486806.png
[20191013154611655.png]: /images/20210813/dfacf29be800417c8e5999f4ea6cd980.png
[20191013171856738.png]: /images/20210813/2a623b4093894c898f0d6229ab034219.png
[2019101317193973.png]: /images/20210813/aefd7f0d830142718fff0d671106ab6f.png