MySQL案例之Timestamp和Datetime

Rangle發表於2018-01-12
MySql

mysql資料庫常用的時間型別有timestamp和datetime，兩者主要區別是佔用儲存空間長度不一致、可儲存的時間也有限制，但針對不同版本下，timestamp欄位型別的設定需要慎重，因為不注意的可能會被“坑死”。

一、TIMESTAMP和DATETIME欄位型別對比

欄位型別 儲存長度 時間範圍 備註
timestamp 4位元組 '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC  
datetime 8位元組（5.7佔5位元組） '1000-01-01 00:00:00' to '9999-12-31 23:59:59'  

 

 

 

1.timestamp注意事項

(1)5.7版本之前，沒有explicit_defaults_for_timestamp 引數(5.7預設開啟，timestamp不會設定default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP屬性)，timestamp欄位預設存在default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP屬性，預設值針對的主要是以下函式產生的時間

These are CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, and LOCALTIMESTAMP().

(2)mysql的timestamp值自動從當前時區轉換到utc時區儲存，並且自動從utc時區轉換為當前系統時區檢索返回

官方參考文件：https://dev.mysql.com/doc/refman/5.7/en/datetime.html

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. 
(This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time.

2.datetime注意事項：

在mysql5.7之後，datetime欄位也可以指定預設值，並且格式和timestamp一樣

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
或
DEFAULT 0 or DEFAULT '2000-01-01 00:00:00'

二、資料庫時區引數

1.system_time_zone

　　資料庫例項啟動，從my.cnf配置引數timezone=timezone_name獲取，若my.cnf未設定則從作業系統獲取環境變數TZ獲取
2.time_zone

　　資料庫當前實際使用的時區，預設為system，即系統時區，其設定可以通過set global time_zone=''設定，其中引數值有以下三種形式：'SYSTEM' 、'+10:00' 、'Europe/Helsinki'

Linux時區知識擴充套件：
Linux將時鐘分為系統時鐘(System Clock)和硬體(Real Time Clock，簡稱RTC)時鐘兩種。系統時間是指當前Linux Kernel中的時鐘，
而硬體時鐘則是主機板上由電池供電的那個主機板硬體時鐘，這個時鐘可以在BIOS的"Standard BIOS Feture"項中進行設定。
系統時鐘：
    所有作業系統命令、函式依賴的時鐘，獨立於硬體時鐘執行，可通過date設定
    設定系統時間：date -s "2017-08-22 22:58:00" 
    檢視時區：date -R

硬體時鐘：
    Linux啟動從系統配置獲取
    檢視硬體時鐘:clock --show  、 hwclock --show
    設定硬體時鐘：hwclock/clock --set --date="月/日/年  時：分：秒"
    系統時鐘同步到硬體：/sbin/clock -w
    硬體時鐘同步到系統：/sbin/clock -s

時區設定：
    修改/etc/sysconfig/clock  
    ZONE=Asia/Shanghai
    rm /etc/localtime
    連結到上海時區檔案   
    ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
    執行完上述過程後，重啟機器，即可看到時區已經更改。

三、實際使用例項

1.不同版本下timestamp和datetime使用例項

（1）測試timestamp和datetime建立帶預設值的表
同時執行建立帶預設值的SQL語句，發現5.6（不含5.6）之前版本datetime不支援帶DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

CREATE TABLE test (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

5.0.67版本
ERROR 1067 (42000): Invalid default value for 'dt'

5.5.20版本
ERROR 1067 (42000): Invalid default value for 'dt'
 
5.6.22版本
Query OK, 0 rows affected (0.02 sec)

5.7.18版本
Query OK, 0 rows affected (0.00 sec)

（2）測試timestamp和datetime建立表結構型別

不指定預設值的情況下建立表結構，發現5.6（不含5.6）timestamp預設值是CURRENT_TIMESTAMP並且隨著記錄更新而更新

CREATE TABLE test01 (
  ts TIMESTAMP  ,
  dt DATETIME 
);

5.0.67版本
 CREATE TABLE `test01` (
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `dt` datetime default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

5.5.20版本
 CREATE TABLE `test01` (
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
 
5.6.22版本
CREATE TABLE `test01` (
  `ts` timestamp NULL DEFAULT NULL,
  `dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

5.7.18版本
CREATE TABLE `test01` (
  `ts` timestamp NULL DEFAULT NULL,
  `dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

（3）、測試timestamp和datetime型別mysqldump匯出匯入影響

測試結論為，timestamp資料型別的記錄匯出會以utc時間格式匯出，匯入庫中自動由UTC格式轉為系統預設時區，所以看到匯出檔案timestamp內容時間戳和實際儲存的不相符。

如果需要看到和匯入與實際相符的時間戳，需要加入引數--tz-utc=false用於禁止timestamp時區轉換，預設是開啟的，即匯出檔案中開頭設定的/*!40103 SET TIME_ZONE='+00:00' */;

系統預設是cst時區，資料庫引數設定也是CST和SYSTEM，根據系統時間插入資料：

| system_time_zone | CST    |
| time_zone              | SYSTEM |

insert into test01 values(sysdate(),sysdate());

5.0.67版本
select * from test01;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2018-01-12 14:15:19 | 2018-01-12 14:15:19 | 
+---------------------+---------------------+
 
5.5.20版本
select * from test01;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2018-01-12 14:15:27 | 2018-01-12 14:15:27 |
+---------------------+---------------------+
 
5.6.22版本
select * from test01;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2018-01-07 20:13:22 | 2018-01-07 20:13:22 |
+---------------------+---------------------+
 
5.7.18版本
select * from test01;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2018-01-07 20:13:20 | 2018-01-07 20:13:20 |
+---------------------+---------------------+

然後將資料庫表test01表利用mysqldump匯出

mysqldump dbtest --tables test01  >test01.sql
5.0.67版本
-- MySQL dump 10.11
--
-- Host: localhost    Database: dbtest
-- ------------------------------------------------------
-- Server version       5.0.67-percona-highperf-log
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
--
-- Table structure for table `test01`
--
DROP TABLE IF EXISTS `test01`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `test01` (
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `dt` datetime default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
--
-- Dumping data for table `test01`
--
LOCK TABLES `test01` WRITE;
/*!40000 ALTER TABLE `test01` DISABLE KEYS */;
INSERT INTO `test01` VALUES ('2018-01-12 06:15:19','2018-01-12 14:15:19');
/*!40000 ALTER TABLE `test01` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
-- Dump completed on 2018-01-12  6:22:25 
5.5.20版本
-- MySQL dump 10.13  Distrib 5.5.20, for Linux (x86_64)
--
-- Host: localhost    Database: dbtest
-- ------------------------------------------------------
-- Server version       5.5.20-rel24.1-log

/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
--
-- Table structure for table `test01`
--
DROP TABLE IF EXISTS `test01`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test01` (
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `test01`
--
LOCK TABLES `test01` WRITE;
/*!40000 ALTER TABLE `test01` DISABLE KEYS */;
INSERT INTO `test01` VALUES ('2018-01-12 06:15:27','2018-01-12 14:15:27');
/*!40000 ALTER TABLE `test01` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
-- Dump completed on 2018-01-12 14:22:32
5.6.22版本
-- MySQL dump 10.13  Distrib 5.6.22-71.0, for Linux (x86_64)
--
-- Host: localhost    Database: dbtest
-- ------------------------------------------------------
-- Server version       5.6.22-71.0-log
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
--
-- Table structure for table `test01`
--
DROP TABLE IF EXISTS `test01`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test01` (
  `ts` timestamp NULL DEFAULT NULL,
  `dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `test01`
--
LOCK TABLES `test01` WRITE;
/*!40000 ALTER TABLE `test01` DISABLE KEYS */;
INSERT INTO `test01` VALUES ('2018-01-07 12:13:22','2018-01-07 20:13:22');
/*!40000 ALTER TABLE `test01` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
-- Dump completed on 2018-01-07 20:20:29
5.7.18版本
-- MySQL dump 10.13  Distrib 5.7.18-15, for Linux (x86_64)
--
-- Host: localhost    Database: dbtest
-- ------------------------------------------------------
-- Server version       5.7.18-15-log

/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
--
-- Table structure for table `test01`
--
DROP TABLE IF EXISTS `test01`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test01` (
  `ts` timestamp NULL DEFAULT NULL,
  `dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `test01`
--
LOCK TABLES `test01` WRITE;
/*!40000 ALTER TABLE `test01` DISABLE KEYS */;
INSERT INTO `test01` VALUES ('2018-01-07 12:13:20','2018-01-07 20:13:20');
/*!40000 ALTER TABLE `test01` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
-- Dump completed on 2018-01-07 20:20:29

刪除test01表，並用mysqldump匯出檔案還原

mysql -D dbtest -e "drop tables test01"
mysql -D dbtest  <test01.sql 
select * from test01;

5.0.67版本
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2018-01-12 14:15:19 | 2018-01-12 14:15:19 | 
+---------------------+---------------------+
5.5.20版本
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2018-01-12 14:15:27 | 2018-01-12 14:15:27 |
+---------------------+---------------------+
5.6.22版本
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2018-01-07 20:13:22 | 2018-01-07 20:13:22 |
+---------------------+---------------------+
5.7.18版本
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2018-01-07 20:13:20 | 2018-01-07 20:13:20 |
+---------------------+---------------------+

（4）ON UPDATE CURRENT_TIMESTAMP屬性影響

如果包含ON UPDATE CURRENT_TIMESTAMP屬性，則如果對錶記錄更新，此記錄對應的timestamp型別記錄也會更新

ts欄位是timestamp型別且含有on update current_timestamp屬性，dt欄位是datetime型別且不含on update current_timestamp屬性
更新前：
mysql -D dbtest -e "select * from test01;"
+---------------------+---------------------+------+
| ts                  | dt                  | id   |
+---------------------+---------------------+------+
| 2018-01-12 14:15:19 | 2018-01-12 14:15:19 | NULL | 
+---------------------+---------------------+------+
更新：
mysql -D dbtest -e "update test01 set id=1 where id is null"
更新後：
mysql -D dbtest -e "select * from test01;"                  
Logging to file '/home/mysql/query.log'
+---------------------+---------------------+------+
| ts                  | dt                  | id   |
+---------------------+---------------------+------+
| 2018-01-12 15:42:15 | 2018-01-12 14:15:19 |    1 | 
+---------------------+---------------------+------+

2.不同版本下mysqldump結束時間分析

使用mysqldump備份的同仁都可能會注意到，正常備份結束的話，在檔案結尾會有一條成功結束的表示，即 ：-- Dump completed on 2018-01-12  6:22:25。

這個在mysql5.0後可以通過一些引數控制，例如可以加引數--comments=false來禁止新增comments資訊，即所有--開頭的comment會不記錄在備份檔案中，也可以增加--dump-date=false來禁止時間戳新增，即只包含-- Dump completed。

但特別需要注意的是，此時間戳的由來在mysqldump 10.11版本（mysql5.0.x版本對應MySQL dump 10.11， 含10.11版本）記錄的是UTC時區時間戳，而在mysqldump 10.12版本之後（mysql 5.5對應的版本是MySQL dump 10.13，含10.13）記錄的是系統當前時區時間戳。

所以大家看到的是mysqldump 10.11備份結束的時間總是比系統當前時間提前8小時，例如mysql5.0系統當前是CST時區：2018-01-12  14:22:25   而備份檔案結束時間戳是-- Dump completed on 2018-01-12  6:22:25。這個只是記錄的時間戳不同，不影響最終的資料記錄時間。

原始碼片段如下：

./client/mysqldump.c

#define DUMP_VERSION "10.13"

static void write_footer(FILE *sql_file)
{
  if (opt_xml)
  {
    fputs("</mysqldump>\n", sql_file);
    check_io(sql_file);
  }
  else if (!opt_compact)
  {
    if (opt_tz_utc)
      fprintf(sql_file,"/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;\n");

    fprintf(sql_file,"\n/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;\n");
    if (!path)
    {
      fprintf(md_result_file,"\
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;\n\
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;\n");
    }
    if (opt_set_charset)
      fprintf(sql_file,
"/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;\n"
"/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;\n"
"/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;\n");
    fprintf(sql_file,
            "/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;\n");
    fputs("\n", sql_file);

    if (opt_dump_date)
    {
      char time_str[20];
      get_date(time_str, GETDATE_DATE_TIME, 0);
      print_comment(sql_file, 0, "-- Dump completed on %s\n", time_str);
    }
    else
      print_comment(sql_file, 0, "-- Dump completed\n");

    check_io(sql_file);
  }
} /* write_footer */

get_date()函式是核心，那麼其原始碼./mysys/mf_getdate.c如下：

 

相關文章