MySQL數(shù)據(jù)類型--------日期與時(shí)間類型實(shí)戰(zhàn)

1. 背景

　 * MySQL支持多種表示日期和時(shí)間的數(shù)據(jù)類型，如YEAR、TIME、DATETIME、TIMESTAMP等等……

   * MySQL 5.6.4版本開始支持微秒(支持類型：TIMTE、DATETIME、TIMESTAMP,　微秒長度最大為6) 

2. 日期與時(shí)間類型所屬空間與表示范圍

3. 日期與時(shí)間類型 "0" 值格式

4. 日期與時(shí)間類型相關(guān)函數(shù)操作

  * NOW() 獲取語句開始執(zhí)行的日期和時(shí)間，[ MySQL 5.6.3版本之后可以獲取微秒 ]

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2017-06-27 09:00:13 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW(6);
+----------------------------+
| NOW(6)                     |
+----------------------------+
| 2017-06-27 09:18:50.720849 |
+----------------------------+
1 row in set (0.00 sec)

  * SYSDATE() 獲取SYSDATE()函數(shù)執(zhí)行的時(shí)間

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2017-06-27 09:38:03 |        0 | 2017-06-27 09:38:05 |
+---------------------+----------+---------------------+
1 row in set (2.01 sec)

　* CURDATE() 獲取當(dāng)前日期

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2017-06-27 |
+------------+
1 row in set (0.00 sec)

  * CURTIME() 獲取當(dāng)前時(shí)間

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 09:05:51  |
+-----------+
1 row in set (0.03 sec)

　* ADDDATE() 增加日期

　　[ 在當(dāng)前日期上增加 10 天 ]

mysql> SELECT CURDATE(), ADDDATE(CURDATE(), "10");
+------------+--------------------------+
| CURDATE()  | ADDDATE(CURDATE(), "10") |
+------------+--------------------------+
| 2017-06-27 | 2017-07-07               |
+------------+--------------------------+
1 row in set (0.02 sec)

  * ADDTIME() 增加時(shí)間

    [ 在當(dāng)前時(shí)間上增加1天1小時(shí)1分1秒 ]

mysql> SELECT NOW(), ADDTIME(NOW(), '1 1:1:1');
+---------------------+---------------------------+
| NOW()               | ADDTIME(NOW(), '1 1:1:1') |
+---------------------+---------------------------+
| 2017-06-27 09:00:21 | 2017-06-28 10:01:22       |
+---------------------+---------------------------+
1 row in set (0.02 sec)

  * DATE() 返回字符串中的日期

mysql> SELECT NOW(), DATE(NOW());
+---------------------+-------------+
| NOW()               | DATE(NOW()) |
+---------------------+-------------+
| 2017-06-27 09:07:18 | 2017-06-27  |
+---------------------+-------------+
1 row in set (0.00 sec)

  * TIME() 返回字符串中的時(shí)間

mysql> SELECT NOW(), TIME(NOW());
+---------------------+-------------+
| NOW()               | TIME(NOW()) |
+---------------------+-------------+
| 2017-06-27 09:07:40 | 09:07:40    |
+---------------------+-------------+
1 row in set (0.01 sec)

  * YEAR() 返回字符串中的年數(shù)

mysql> SELECT NOW(), YEAR(NOW());
+---------------------+-------------+
| NOW()               | YEAR(NOW()) |
+---------------------+-------------+
| 2017-06-27 09:12:39 |        2017 |
+---------------------+-------------+
1 row in set (0.00 sec)

  * MONTH() 返回字符串中的月數(shù)

mysql> SELECT NOW(), MONTH(NOW());
+---------------------+--------------+
| NOW()               | MONTH(NOW()) |
+---------------------+--------------+
| 2017-06-27 09:13:09 |            6 |
+---------------------+--------------+
1 row in set (0.01 sec)

  * DAY() 返回字符串中的日數(shù)

mysql> SELECT NOW(), DAY(NOW());
+---------------------+------------+
| NOW()               | DAY(NOW()) |
+---------------------+------------+
| 2017-06-27 09:12:08 |         27 |
+---------------------+------------+
1 row in set (0.01 sec)

  * HOUR() 返回字符串中的小時(shí)數(shù)

mysql> SELECT NOW(), HOUR(NOW());
+---------------------+-------------+
| NOW()               | HOUR(NOW()) |
+---------------------+-------------+
| 2017-06-27 09:13:57 |           9 |
+---------------------+-------------+
1 row in set (0.00 sec)

  * MINUTE() 返回字符串中的分鐘數(shù)

mysql> SELECT NOW(), MINUTE(NOW());
+---------------------+---------------+
| NOW()               | MINUTE(NOW()) |
+---------------------+---------------+
| 2017-06-27 09:15:38 |            15 |
+---------------------+---------------+
1 row in set (0.00 sec)

  * SECOND() 返回字符串中的秒數(shù)

mysql> SELECT NOW(), SECOND(NOW());
+---------------------+---------------+
| NOW()               | SECOND(NOW()) |
+---------------------+---------------+
| 2017-06-27 09:16:23 |            23 |
+---------------------+---------------+
1 row in set (0.02 sec)

  * MICROSECOND()　返回字符串中的微秒數(shù)　[MySQL 5.6.3版本之后]

mysql> SELECT NOW(6), MICROSECOND(NOW(6));
+----------------------------+---------------------+
| NOW(6)                     | MICROSECOND(NOW(6)) |
+----------------------------+---------------------+
| 2017-06-27 09:18:11.264125 |              264125 |
+----------------------------+---------------------+
1 row in set (0.01 sec)

  * DATEDIFF() 日期相減運(yùn)算

mysql> SELECT DATEDIFF('2017-06-29', '2017-06-09');
+--------------------------------------+
| DATEDIFF('2017-06-29', '2017-06-09') |
+--------------------------------------+
|                                   20 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF('2017-06-29', '2017-07-09');
+--------------------------------------+
| DATEDIFF('2017-06-29', '2017-07-09') |
+--------------------------------------+
|                                  -10 |
+--------------------------------------+
1 row in set (0.03 sec)

  * DAYNAME() 返回工作日的英文名稱

mysql> SELECT CURDATE(), DAYNAME(CURDATE());
+------------+--------------------+
| CURDATE()  | DAYNAME(CURDATE()) |
+------------+--------------------+
| 2017-06-27 | Tuesday            |
+------------+--------------------+
1 row in set (0.01 sec)

　* MONTHNAME() 返回月的英文名稱

mysql> SELECT CURDATE(), MONTHNAME(CURDATE());
+------------+----------------------+
| CURDATE()  | MONTHNAME(CURDATE()) |
+------------+----------------------+
| 2017-06-27 | June                 |
+------------+----------------------+
1 row in set (0.01 sec)

  * PERIOD_DIFF() 月數(shù)相減運(yùn)算 [ 格式是YYYYMM或者YYMM ]

mysql> SELECT PERIOD_DIFF(201706, 201705);
+-----------------------------+
| PERIOD_DIFF(201706, 201705) |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT PERIOD_DIFF(201706, 201707);
+-----------------------------+
| PERIOD_DIFF(201706, 201707) |
+-----------------------------+
|                          -1 |
+-----------------------------+
1 row in set (0.00 sec)

　…………等等，詳情查看官網(wǎng)手冊。

5. TIMESTAMP 和 DATETIME 區(qū)別 [ TIMESTAMP VS DATETIME]

　 * timestamp相比較datetime所占空間字節(jié)小。

   * timestamp容易所支持的范圍比timedate要小。 并且容易出現(xiàn)超出的情況

   * timestamp受時(shí)區(qū)timezone的影響以及MYSQL版本和服務(wù)器的SQL MODE的影響, 而datetime不受時(shí)區(qū)影響.

6. 時(shí)間影響實(shí)驗(yàn)

   * 創(chuàng)建表 t_d

mysql> CREATE TABLE t_d(
    -> t TIMESTAMP,
    -> d DATETIME
    -> )ENGINE=INNODB CHARSET=utf8mb4;
Query OK, 0 rows affected (0.09 sec)

   * 查看時(shí)區(qū)

mysql> show variables like '%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.02 sec)

　 * 插入數(shù)據(jù)

mysql> INSERT INTO t_d SELECT NOW(), NOW();
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

   * 查看數(shù)據(jù)

mysql> SELECT * FROM t_d;
+---------------------+---------------------+
| t                   | d                   |
+---------------------+---------------------+
| 2017-06-27 09:44:13 | 2017-06-27 09:44:13 |
+---------------------+---------------------+
1 row in set (0.00 sec)

　 * 更新時(shí)區(qū)

mysql> set time_zone = '+0:00';
Query OK, 0 rows affected (0.00 sec)

   * 再次查看數(shù)據(jù)

mysql> SELECT * FROM t_d;
+---------------------+---------------------+
| t                   | d                   |
+---------------------+---------------------+
| 2017-06-27 01:44:13 | 2017-06-27 09:44:13 |
+---------------------+---------------------+
1 row in set (0.01 sec)

7. 總結(jié)

以需求驅(qū)動技術(shù)，技術(shù)本身沒有優(yōu)略之分，只有業(yè)務(wù)之分。