把生命浪费在美好事物上

产品产品产品

358篇博客

最新博客

最新资源下载

编辑推荐

网易数帆开源API网关与容器云项目，让云原生生产落地“多快好网易汪源：统一负载与多云环境的“开放姿态”，才是云原生网易数帆如何用 Kubernetes“原语”搞定云原生中间件快手打新挤爆券商系统，网易数帆推出券商稳定性保障方案探索智慧校园新模式，网易有数在教育行业的实践分享金融行业大数据治理之路——数据模型篇

MySQL中“诡异”的TIMESTAMP数据类型（下篇）

把生命浪费在美好事物上2018-08-10 11:54
   e、案例五： 验证了4.2-b的使用规则 
#5.5版本 & 5.6版本 & 5.7版本
mysql> #执行SQL
mysql> CREATE TABLE tab4(
    -> id BIGINT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> db_create_time TIMESTAMP  NOT NULL DEFAULT '2000-01-01 00:00:00'
    -> )ENGINE=INNODB DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tab4 \G
*************************** 1. row ***************************
       Table: tab4
Create Table: CREATE TABLE `tab4` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `db_create_time` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> INSERT INTO tab4(id) VALUES(1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM tab4;
+----+---------------------+
| id | db_create_time      |
+----+---------------------+
|  1 | 2000-01-01 00:00:00 |
+----+---------------------+
1 row in set (0.00 sec)
 f、案例六：(见TIMESTAMP类型和DATETIME类型) 
#MySQL所有版本
mysql> #执行SQL
mysql> SHOW CREATE TABLE tab5 \G
*************************** 1. row ***************************
       Table: tab5
Create Table: CREATE TABLE `tab5` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `start` datetime NOT NULL,
  `db_create_time` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> INSERT INTO tab5 VALUES(1,'2017-06-30 00:00:00','2017-06-30 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tab5;
+----+---------------------+---------------------+
| id | start               | db_create_time      |
+----+---------------------+---------------------+
|  1 | 2017-06-30 00:00:00 | 2017-06-30 00:00:00 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)mysql> SHOW GLOBAL VARIABLES LIKE '%sql_mode%';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.00 sec)
#MySQL此时的时区默认是系统时区CST，即：东8区时间(有关时区详情见第一部分：时区简介)
mysql> SHOW GLOBAL VARIABLES LIKE '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)
#将MySQL时区修改为东1区
mysql> SET GLOBAL time_zone='+1:00';
Query OK, 0 rows affected (0.00 sec)


#新开SESSION，查看有关datetime类型、timestamp类型受时区的影响情况
mysql> \q
Bye

#新SESSION
mysql> USE t1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
#由于"东8区本地时间" = "东1区本地时间+7:00"，所以timestamp类型的数据整体减少了7个小时
Database changed
mysql> SELECT * FROM tab5;
+----+---------------------+---------------------+
| id | start               | db_create_time      |
+----+---------------------+---------------------+
|  1 | 2017-06-30 00:00:00 | 2017-06-29 17:00:00 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

#引入SQL_MODE='NO_ZERO_DATE, NO_ZERO_IN_DATE'
   g、案例七： 
#MySQL5.5版本
mysql> SET SESSION SQL_MODE='NO_ZERO_DATE,NO_ZERO_IN_DATE';
Query OK, 0 rows affected (0.00 sec)

#MySQL5.6版本
mysql> SET SESSION SQL_MODE='NO_ZERO_DATE,NO_ZERO_IN_DATE';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------+
| Level   | Code | Message                                                                  |
+---------+------+--------------------------------------------------------------------------+
| Warning | 1681 | 'NO_ZERO_DATE' is deprecated and will be removed in a future release.    |
| Warning | 1681 | 'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release. |
+---------+------+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

#MySQL5.7版本
mysql> SET SESSION SQL_MODE='NO_ZERO_DATE,NO_ZERO_IN_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                 |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3135 | 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#MySQL5.5 & 5.6 & 5.7 版本
mysql> CREATE TABLE tab6(
    -> id BIGINT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> `db_create_time` timestamp NOT NULL ,
    -> `db_update_time` timestamp NOT NULL 
    -> )ENGINE=INNODB DEFAULT CHARSET=UTF8;
ERROR 1067 (42000): Invalid default value for 'db_update_time'
原因结合SQL_MODE：NO_ZERO_DATE和默认行为4.1-c 应该就很明确了。

5：TIMESTAMP类型常见的线上问题
   5.1：由于主从explicit_defaults_for_timestamp参数不一致导致的表结构不一致问题进而引起的主从数据不一致问题
   5.2：更改时区导致的timestamp全表数据更新( 见案例六)
   5.3：不同数据库版本timestamp的精度问题
   5.4：开发提供了建表结构，但是后来发现表中数据并非建表结构中所期望的( 类似案例二)
举例： 
#开发人员提供的SQL如下，期望用start_date来存储该行数据的开始时间
mysql> CREATE TABLE testtab(
    -> id BIGINT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> `start_date` timestamp NOT NULL COMMENT '开始时间',
    -> `end_date` timestamp NOT NULL COMMENT '结束时间'
    -> )ENGINE=INNODB DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
#在默认行为存在的前提下实际上建表语句会变成这样的
#开发人员期望start_date用来存储数据的开始时间，不能自动更新
mysql> show create table testtab \G
*************************** 1. row ***************************
       Table: testtab
Create Table: CREATE TABLE `testtab` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `start_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '开始时间',
  `end_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '结束时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)#解决方案，手动添加默认值即可
mysql> CREATE TABLE testtab1(
    -> id BIGINT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> `start_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '开始时间',
    -> `end_date` timestamp NOT NULL  DEFAULT CURRENT_TIMESTAMP COMMENT '结束时间'
    -> )ENGINE=INNODB DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table testtab1 \G
*************************** 1. row ***************************
       Table: testtab1
Create Table: CREATE TABLE `testtab1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `start_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '开始时间',
  `end_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '结束时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> CREATE TABLE testtab2(
    -> id BIGINT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> `start_date` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '开始时间',
    -> `end_date` timestamp NOT NULL  DEFAULT '2000-01-01 00:00:00' COMMENT '结束时间'
    -> )ENGINE=INNODB DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table testtab2 \G
*************************** 1. row ***************************
       Table: testtab2
Create Table: CREATE TABLE `testtab2` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `start_date` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '开始时间',
  `end_date` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '结束时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

6：线上推荐使用的有关TIMESTAMP类型建表方式 
CREATE TABLE testtable(
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'id',
  #businesscolumn1...业务所需要的字段
  #businesscolumn2...业务所需要的字段
  #businesscolumn3...业务所需要的字段
  db_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  db_update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='';
1：表必备三字段:id, db_create_time, db_update_time 
2：如果某个字段需要emoji表情符，则可以单独添加某个字段为CHARACTER SET utf8mb4
3：业务字段尽量避免过多的使用TIMESTAMP类型，如果需要使用请显示指定TIMESTAMP字段的具体属性，如`starttime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP  或者  `starttime` TIMESTAMP NOT NULL DEFAULT '2000-01-01 00:00:00'
4：具体业务字段和索引由开发人员添加
具体细节问题由DBA跟开发人员确认后酌情考量，比如因为MySQL版本不同酌情选择DEFAULT CURRENT_TIMESTAMP或者DEFAULT '2000-01-01 00:00:00'等

网易云新用户大礼包：https://www.163yun.com/gift

本文来自网易实践者社区，经作者葛崇授权发布。


推荐博客