mysql之日期

你的名字 2022-05-13 03:44 125阅读 0赞

**1.在 MySQL 中,有三种主要的类型：Text（文本）、Number（数字）和 Date/Time（日期/时间）类型。**

**Text 类型：**

<table> 
 <tbody> 
  <tr> 
   <th>数据类型</th> 
   <th>描述</th> 
  </tr> 
  <tr> 
   <td>CHAR(size)</td> 
   <td>保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。</td> 
  </tr> 
  <tr> 
   <td>VARCHAR(size)</td> 
   <td>保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。<strong>注释：</strong>如果值的长度大于 255，则被转换为 TEXT 类型。</td> 
  </tr> 
  <tr> 
   <td>TINYTEXT</td> 
   <td>存放最大长度为 255 个字符的字符串。</td> 
  </tr> 
  <tr> 
   <td>TEXT</td> 
   <td>存放最大长度为 65,535 个字符的字符串。</td> 
  </tr> 
  <tr> 
   <td>BLOB</td> 
   <td>用于 BLOBs（Binary Large OBjects）。存放最多 65,535 字节的数据。</td> 
  </tr> 
  <tr> 
   <td>MEDIUMTEXT</td> 
   <td>存放最大长度为 16,777,215 个字符的字符串。</td> 
  </tr> 
  <tr> 
   <td>MEDIUMBLOB</td> 
   <td>用于 BLOBs（Binary Large OBjects）。存放最多 16,777,215 字节的数据。</td> 
  </tr> 
  <tr> 
   <td>LONGTEXT</td> 
   <td>存放最大长度为 4,294,967,295 个字符的字符串。</td> 
  </tr> 
  <tr> 
   <td>LONGBLOB</td> 
   <td>用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。</td> 
  </tr> 
  <tr> 
   <td>ENUM(x,y,z,etc.)</td> 
   <td>允许您输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。 <p><strong>注释：</strong>这些值是按照您输入的顺序排序的。</p> <p>可以按照此格式输入可能的值： ENUM('X','Y','Z')</p> </td> 
  </tr> 
  <tr> 
   <td>SET</td> 
   <td>与 ENUM 类似，不同的是，SET 最多只能包含 64 个列表项且 SET 可存储一个以上的选择。</td> 
  </tr> 
 </tbody> 
</table>

**Number 类型：**

<table> 
 <tbody> 
  <tr> 
   <th>数据类型</th> 
   <th>描述</th> 
  </tr> 
  <tr> 
   <td>TINYINT(size)</td> 
   <td>-128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。</td> 
  </tr> 
  <tr> 
   <td>SMALLINT(size)</td> 
   <td>-32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。</td> 
  </tr> 
  <tr> 
   <td>MEDIUMINT(size)</td> 
   <td>-8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。</td> 
  </tr> 
  <tr> 
   <td>INT(size)</td> 
   <td>-2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。</td> 
  </tr> 
  <tr> 
   <td>BIGINT(size)</td> 
   <td>-9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。</td> 
  </tr> 
  <tr> 
   <td>FLOAT(size,d)</td> 
   <td>带有浮动小数点的小数字。在 size 参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。</td> 
  </tr> 
  <tr> 
   <td>DOUBLE(size,d)</td> 
   <td>带有浮动小数点的大数字。在 size 参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。</td> 
  </tr> 
  <tr> 
   <td>DECIMAL(size,d)</td> 
   <td>作为字符串存储的 DOUBLE 类型，允许固定的小数点。在 size 参数中规定最大位数。在 d 参数中规定小数点右侧的最大位数。</td> 
  </tr> 
 </tbody> 
</table>

\*这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。

**Date 类型：**

<table> 
 <tbody> 
  <tr> 
   <th>数据类型</th> 
   <th>描述</th> 
  </tr> 
  <tr> 
   <td>DATE()</td> 
   <td>日期。格式：YYYY-MM-DD <p><strong>注释：</strong>支持的范围是从 '1000-01-01' 到 '9999-12-31'</p> </td> 
  </tr> 
  <tr> 
   <td>DATETIME()</td> 
   <td>*日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS <p><strong>注释：</strong>支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'</p> </td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP()</td> 
   <td>*时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的秒数来存储。格式：YYYY-MM-DD HH:MM:SS <p><strong>注释：</strong>支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC</p> </td> 
  </tr> 
  <tr> 
   <td>TIME()</td> 
   <td>时间。格式：HH:MM:SS <p><strong>注释：</strong>支持的范围是从 '-838:59:59' 到 '838:59:59'</p> </td> 
  </tr> 
  <tr> 
   <td>YEAR()</td> 
   <td>2 位或 4 位格式的年。 <p><strong>注释：</strong>4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。</p> </td> 
  </tr> 
 </tbody> 
</table>

\*即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。  
地址：https://www.w3cschool.cn/sql/anioefpk.html  
**2.在建表时怎么设置默认时间？**

**只有一种方法，使用current\_timestamp(current\_date/current\_time只能用作查询显示时间）**

例如：

create table test(
    id int(4) not null AUTO_INCREMENT,
    times TIME NOT NULL DEFAULT CURRENT_TIME,//不支持current_time
    dates DATE NOT NULL DEFAULT CURRENT_DATE,//不支持current_date
    timestamp timestamp not null default current_timestamp,//仅支持current_timestamp
    primary key(id)
    )engine=innodb auto_increment=1 default charset=utf8

**3.date函数有哪些？**

下面的表格列出了 MySQL 中最重要的内建日期函数：

<table> 
 <tbody> 
  <tr> 
   <th>函数</th> 
   <th>描述</th> 
  </tr> 
  <tr> 
   <td><a href="http://www.runoob.com/sql/func-now.html" rel="nofollow">NOW()</a></td> 
   <td>返回当前的日期和时间</td> 
  </tr> 
  <tr> 
   <td><a href="http://www.runoob.com/sql/func-curdate.html" rel="nofollow">CURDATE()</a></td> 
   <td>返回当前的日期</td> 
  </tr> 
  <tr> 
   <td><a href="http://www.runoob.com/sql/func-curtime.html" rel="nofollow">CURTIME()</a></td> 
   <td>返回当前的时间</td> 
  </tr> 
  <tr> 
   <td><a href="http://www.runoob.com/sql/func-date.html" rel="nofollow">DATE()</a></td> 
   <td>提取日期或日期/时间表达式的日期部分，语法：<span style="color:#f33b45;">DATE（date）</span><br> 例：SELECT DATE('1995-02-05 23:2:22');</td> 
  </tr> 
  <tr> 
   <td><a href="http://www.runoob.com/sql/func-extract.html" rel="nofollow">EXTRACT()</a></td> 
   <td>返回日期/时间的单独部分，语法：<span style="color:#f33b45;">EXTRACT(unit FROM date)</span>，unit的值有多个。<br> 例：SELECT EXTRACT(DAY FROM ordertime) AS DATE FROM test5;//day 表示仅返回日，ordertime是表test5的列</td> 
  </tr> 
  <tr> 
   <td><a href="http://www.runoob.com/sql/func-date-add.html" rel="nofollow">DATE_ADD()</a></td> 
   <td>向日期添加指定的时间间隔，语法：<span style="color:#f33b45;">DATE_ADD(date,INTERVAL expr type)</span>， <p>date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。</p> <p>type 参数可以是下列值：<a href="http://www.runoob.com/sql/func-date-add.html" rel="nofollow">http://www.runoob.com/sql/func-date-add.html</a><br> 例：SELECT CURRENT_DATE,DATE_ADD(CURRENT_DATE,INTERVAL 40 DAY);</p> </td> 
  </tr> 
  <tr> 
   <td><a href="http://www.runoob.com/sql/func-date-sub.html" rel="nofollow">DATE_SUB()</a></td> 
   <td>从日期减去指定的时间间隔，语法：<span style="color:#f33b45;">DATE_SUB(date,INTERVAL expr type)</span> <p>date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。</p> <p>type 参数可以是下列值：同上</p> </td> 
  </tr> 
  <tr> 
   <td><a href="http://www.runoob.com/sql/func-datediff-mysql.html" rel="nofollow">DATEDIFF()</a></td> 
   <td>返回两个日期之间的天数，语法：<span style="color:#f33b45;">DATEDIFF(date1,date2)</span> <p>date1 和 date2 参数是合法的日期或日期/时间表达式。</p> <p>例：SELECT DATEDIFF(‘2017-5-1‘,'2017-05-20');//返回19</p> </td> 
  </tr> 
  <tr> 
   <td><a href="http://www.runoob.com/sql/func-date-format.html" rel="nofollow">DATE_FORMAT()</a></td> 
   <td> <p>用不同的格式显示日期/时间，语法：<span style="color:#f33b45;">DATE_FORMAT(date,format)</span></p> <p>date 参数是合法的日期。format 规定日期/时间的输出格式。</p> <p>可以使用的格式有：<a href="http://www.runoob.com/sql/func-date-format.html" rel="nofollow">http://www.runoob.com/sql/func-date-format.html</a></p> <p>例：SELECT DATE_FORMAT(CURRENT_TIMESTAMP,'%Y-%m-%d %H:%i:%m'),<br> DATE_FORMAT(CURRENT_TIMESTAMP,'%y-%m-%d %T');//2018-09-10 12:56:09 ,18-09-10 12:56:01</p> </td> 
  </tr> 
 </tbody> 
</table>

now()、sysdate()、date()、  
current\_date和current\_date（）和curdate（）、current\_time和current\_time（）和curtime（）、current\_timestamp和current\_timestamp（）效果一样  
其他时间函数

**4.MySQL日期函数与日期转换格式化函数大全**  
https://www.jb51.net/article/135803.htm  
日期转换格式化函数三种：mysql中常用的几种时间格式转换函数整理如下（包含第7点）

1.   时间戳转换为日期from\_unixtime(timestamp, format)
    
        SELECT FROM_UNIXTIME(23434,'%Y-%m-%d');
        ->1970-01-01
    
     
2.   日期转换为时间戳unix\_timestamp(date)
    
        select unix_timestamp('1997-10-16');
        ->876931200
3.   日期转换任意时间格式date\_format(date, format)
    
        SELECT DATE_FORMAT('1994-10-16','%m月%d日');
        ->10月16日

**5.mysql之TIMESTAMP（时间戳）用法详解（**[https://www.jb51.net/article/51794.htm?=278673284][https_www.jb51.net_article_51794.htm_278673284]**）**

**一、TIMESTAMP的变体**

TIMESTAMP时间戳在创建的时候可以有多重不同的特性，如：

1.  在创建新记录和修改现有记录的时候都对这个数据列刷新：
    
        ordertime timestamp default current_timestamp on update current_timestamp,
2.  在创建新记录的时候把这个字段设置为当前时间，但以后修改时，不再刷新它：
    
        ordertime timestamp default timestamp,
3.  在创建新记录的时候把这个字段设置为0，以后修改时刷新它：
    
        ordertime timestamp on update timestamp,
4.  在创建新记录的时候把这个字段设置为给定值，以后修改时刷新它：
    
        ordertime TIMESTAMP DEFAULT ‘yyyy-mm-dd hh:mm:ss' ON UPDATE CURRENT_TIMESTAMP

**MySQL目前不支持列的Default 为函数的形式,如达到你某列的默认值为当前更新日期与时间的功能,你可以使用TIMESTAMP列类型**

**6.时间戳timestamp和datetime的区别（用哪一种好）    https://www.jb51.net/article/62532.htm**

**相同点**

1.  都可以用于表示时间
2.  都呈字符串显示

**不同点**

1.  TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 开始所经过的秒数来储存，不考虑闰秒。
2.  datetime支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'  
    timstamp支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC
3.  工作方式不同：在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。  
    TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

**7.MySQL中日期和时间戳互相转换的函数和方法  **

参考第四点

[https_www.jb51.net_article_51794.htm_278673284]: https://www.jb51.net/article/51794.htm?=278673284