无锡cms建站,企业网站建设方案书前言,如何做网站推广,排行榜前十名MySQL数据库常用的时间类型有timestamp和datetime#xff0c;两者主要区别是占用存储空间长度不一致、可存储的时间也有限制#xff0c;但针对不同版本下#xff0c;timestamp字段类型的设置需要慎重#xff0c;因为不注意的可能会被“坑死”。一、TIMESTAMP和DATETIME字段…MySQL数据库常用的时间类型有timestamp和datetime两者主要区别是占用存储空间长度不一致、可存储的时间也有限制但针对不同版本下timestamp字段类型的设置需要慎重因为不注意的可能会被“坑死”。一、TIMESTAMP和DATETIME字段类型对比字段类型存储长度时间范围备注timestamp4字节1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTCdatetime8字节(5.7占5字节)1000-01-01 00:00:00 to 9999-12-31 23:59:591.timestamp注意事项(1)5.7版本之前没有explicit_defaults_for_timestamp 参数(5.7默认开启timestamp不会设置default值属性)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.htmlMySQL converts TIMESTAMP valuesfrom 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 servers 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);或DEFAULT0 or DEFAULT 2000-01-01 00:00:00二、数据库时区参数1.system_time_zone数据库实例启动从my.cnf配置参数timezonetimezone_name获取若my.cnf未设置则从操作系统获取环境变量TZ获取2.time_zone数据库当前实际使用的时区默认为system即系统时区其设置可以通过set global time_zone设置其中参数值有以下三种形式SYSTEM 、10:00 、Europe/HelsinkiLinux时区知识扩展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/clockZONEAsia/Shanghairm/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_TIMESTAMPCREATE TABLE test (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);5.0.67版本ERROR1067 (42000): Invalid default value for dt5.5.20版本ERROR1067 (42000): Invalid default value for dt5.6.22版本Query OK,0 rows affected (0.02sec)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 NULLdefaultCURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,dt datetimedefaultNULL) ENGINEInnoDB DEFAULT CHARSETlatin15.5.20版本CREATE TABLE test01 (ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,dt datetime DEFAULT NULL) ENGINEInnoDB DEFAULT CHARSETutf8mb45.6.22版本CREATE TABLE test01 (ts timestamp NULL DEFAULT NULL,dt datetime DEFAULT NULL) ENGINEInnoDB DEFAULT CHARSETutf85.7.18版本CREATE TABLE test01 (ts timestamp NULL DEFAULT NULL,dt datetime DEFAULT NULL) ENGINEInnoDB DEFAULT CHARSETutf8(3)、测试timestamp和datetime类型mysqldump导出导入影响测试结论为timestamp数据类型的记录导出会以utc时间格式导出导入库中自动由UTC格式转为系统默认时区所以看到导出文件timestamp内容时间戳和实际存储的不相符。如果需要看到和导入与实际相符的时间戳需要加入参数--tz-utcfalse用于禁止timestamp时区转换默认是开启的即导出文件中开头设置的/*!40103 SET TIME_ZONE00:00 */;系统默认是cst时区数据库参数设置也是CST和SYSTEM根据系统时间插入数据| system_time_zone | CST || time_zone | SYSTEM |insert into test01 values(sysdate(),sysdate());5.0.67版本select * fromtest01;------------------------------------------| ts | dt |------------------------------------------| 2018-01-12 14:15:19 | 2018-01-12 14:15:19 |------------------------------------------5.5.20版本select * fromtest01;------------------------------------------| ts | dt |------------------------------------------| 2018-01-12 14:15:27 | 2018-01-12 14:15:27 |------------------------------------------5.6.22版本select * fromtest01;------------------------------------------| ts | dt |------------------------------------------| 2018-01-07 20:13:22 | 2018-01-07 20:13:22 |------------------------------------------5.7.18版本select * fromtest01;------------------------------------------| ts | dt |------------------------------------------| 2018-01-07 20:13:20 | 2018-01-07 20:13:20 |------------------------------------------然后将数据库表test01表利用mysqldump导出mysqldump dbtest --tables test01 test01.sql5.0.67版本-- MySQL dump 10.11----Host: localhost Database: dbtest-- -------------------------------------------------------- Server version 5.0.67-percona-highperf-log/*!40103 SET OLD_TIME_ZONETIME_ZONE*/;/*!40103 SET TIME_ZONE00:00*/;---- Table structure fortable test01--DROP TABLE IF EXISTS test01;SET saved_cs_clientcharacter_set_client;SET character_set_clientutf8;CREATE TABLE test01 (ts timestamp NOT NULLdefaultCURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,dt datetimedefaultNULL) ENGINEInnoDB DEFAULT CHARSETlatin1;SET character_set_clientsaved_cs_client;---- Dumping data fortable 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_ZONEOLD_TIME_ZONE*/;-- Dump completed on 2018-01-12 6:22:255.5.20版本-- MySQL dump 10.13 Distrib 5.5.20, forLinux (x86_64)----Host: localhost Database: dbtest-- -------------------------------------------------------- Server version 5.5.20-rel24.1-log/*!40101 SET NAMES utf8*/;/*!40103 SET OLD_TIME_ZONETIME_ZONE*/;/*!40103 SET TIME_ZONE00:00*/;---- Table structure fortable 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) ENGINEInnoDB DEFAULT CHARSETutf8mb4;/*!40101 SET character_set_client saved_cs_client*/;---- Dumping data fortable 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_ZONEOLD_TIME_ZONE*/;-- Dump completed on 2018-01-12 14:22:325.6.22版本-- MySQL dump 10.13 Distrib 5.6.22-71.0, forLinux (x86_64)----Host: localhost Database: dbtest-- -------------------------------------------------------- Server version 5.6.22-71.0-log/*!40101 SET NAMES utf8*/;/*!40103 SET OLD_TIME_ZONETIME_ZONE*/;/*!40103 SET TIME_ZONE00:00*/;---- Table structure fortable 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) ENGINEInnoDB DEFAULT CHARSETutf8;/*!40101 SET character_set_client saved_cs_client*/;---- Dumping data fortable 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_ZONEOLD_TIME_ZONE*/;-- Dump completed on 2018-01-07 20:20:295.7.18版本-- MySQL dump 10.13 Distrib 5.7.18-15, forLinux (x86_64)----Host: localhost Database: dbtest-- -------------------------------------------------------- Server version 5.7.18-15-log/*!40101 SET NAMES utf8*/;/*!40103 SET OLD_TIME_ZONETIME_ZONE*/;/*!40103 SET TIME_ZONE00:00*/;---- Table structure fortable 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) ENGINEInnoDB DEFAULT CHARSETutf8;/*!40101 SET character_set_client saved_cs_client*/;---- Dumping data fortable 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_ZONEOLD_TIME_ZONE*/;-- Dump completed on 2018-01-07 20:20:29删除test01表并用mysqldump导出文件还原mysql -D dbtest -e drop tables test01mysql-D dbtest | 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 id1 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后可以通过一些参数控制例如可以加参数--commentsfalse来禁止添加comments信息即所有--开头的comment会不记录在备份文件中也可以增加--dump-datefalse来禁止时间戳添加即只包含-- 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.13static void write_footer(FILE *sql_file){if(opt_xml){fputs(\n, sql_file);check_io(sql_file);}else if (!opt_compact){if(opt_tz_utc)fprintf(sql_file,/*!40103 SET TIME_ZONEOLD_TIME_ZONE */;\n);fprintf(sql_file,\n/*!40101 SET SQL_MODEOLD_SQL_MODE */;\n);if (!path){fprintf(md_result_file,\/*!40014 SET FOREIGN_KEY_CHECKSOLD_FOREIGN_KEY_CHECKS*/;\n\/*!40014 SET UNIQUE_CHECKSOLD_UNIQUE_CHECKS*/;\n);}if(opt_set_charset)fprintf(sql_file,/*!40101 SET CHARACTER_SET_CLIENTOLD_CHARACTER_SET_CLIENT */;\n/*!40101 SET CHARACTER_SET_RESULTSOLD_CHARACTER_SET_RESULTS */;\n/*!40101 SET COLLATION_CONNECTIONOLD_COLLATION_CONNECTION */;\n);fprintf(sql_file,/*!40111 SET SQL_NOTESOLD_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);}elseprint_comment(sql_file,0, -- Dump completed\n);check_io(sql_file);}}/*write_footer*/
