mysql timestamp类型测试

最新推荐文章于 2021-02-17 19:58:46 发布

aoerqileng

最新推荐文章于 2021-02-17 19:58:46 发布

阅读量539

点赞数

CC 4.0 BY-SA版权

分类专栏： MYSQL

本文链接：https://blog.youkuaiyun.com/aoerqileng/article/details/79916615

MYSQL 专栏收录该内容

274 篇文章

订阅专栏

本文详细介绍了MySQL中时间戳字段的使用方法，包括默认值设置、时区影响及有效范围等，并通过具体示例展示了如何正确配置以适应不同场景的需求。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql> create table test(id int,d timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into test values(1,null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| id   | d                   |
+------+---------------------+
|    1 | 2018-04-12 16:55:36 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> update test set id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+------+---------------------+
| id   | d                   |
+------+---------------------+
|    2 | 2018-04-12 16:55:54 |
+------+---------------------+
1 row in set (0.00 sec)

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

mysql> show variables like 'time%';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| time_format   | %H:%i:%s          |
| time_zone     | +09:00            |
| timed_mutexes | OFF               |
| timestamp     | 1523523607.844138 |
+---------------+-------------------+
4 rows in set (0.00 sec)

mysql> select * from test;
+------+---------------------+
| id   | d                   |
+------+---------------------+
|    2 | 2018-04-12 17:55:54 |
+------+---------------------+
1 row in set (0.00 sec)

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

mysql> select * from test;
+------+---------------------+
| id   | d                   |
+------+---------------------+
|    2 | 2018-04-12 16:55:54 |
+------+---------------------+
1 row in set (0.00 sec)

看到是跟时区相关，另外在使用set global time_zone=’+9:00’的时候是无法修改时区的，没有报错，只能修改本会话的时区信息。

下面是timestamp范围的测试

 TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

建表语句

mysql> CREATE TABLE `db_sample_source` (
    ->   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键（自增长）',
    ->   `enabled` int(1) DEFAULT '1' COMMENT '是否可用',
    ->   `db_name` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库名',
    ->   `db_url` varchar(256) COLLATE utf8_bin NOT NULL COMMENT '数据库链接',
    ->   `db_port` bigint(20) NOT NULL COMMENT '端口',
    ->   `user_name` varchar(256) COLLATE utf8_bin NOT NULL COMMENT '用户名',
    ->   `password` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '密码',
    ->   `db_type` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '数据库类型 oracle | mysql',
    ->   `db_description` varchar(1024) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库描述',
    ->   `db_table_name` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库表名',
    ->   `create_user` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '创建人',
    ->   `create_time` timestamp NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '创建记录时间',
    ->   `modify_user` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '修改人',
    ->   `modify_time` timestamp NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '修改记录时间',
    ->   `version` int(11) NOT NULL DEFAULT '0' COMMENT '版本',
    ->   `create_group` varchar(256) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '创建用户组',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='数据源来源数据库';
ERROR 1067 (42000): Invalid default value for 'create_time'
mysql> CREATE TABLE `db_sample_source` (
    ->   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键（自增长）',
    ->   `enabled` int(1) DEFAULT '1' COMMENT '是否可用',
    ->   `db_name` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库名',
    ->   `db_url` varchar(256) COLLATE utf8_bin NOT NULL COMMENT '数据库链接',
    ->   `db_port` bigint(20) NOT NULL COMMENT '端口',
    ->   `user_name` varchar(256) COLLATE utf8_bin NOT NULL COMMENT '用户名',
    ->   `password` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '密码',
    ->   `db_type` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '数据库类型 oracle | mysql',
    ->   `db_description` varchar(1024) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库描述',
    ->   `db_table_name` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库表名',
    ->   `create_user` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '创建人',
    ->   `create_time` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01' COMMENT '创建记录时间',
    ->   `modify_user` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '修改人',
    ->   `modify_time` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01' COMMENT '修改记录时间',
    ->   `version` int(11) NOT NULL DEFAULT '0' COMMENT '版本',
    ->   `create_group` varchar(256) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '创建用户组',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='数据源来源数据库';
Query OK, 0 rows affected (0.04 sec)

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone |        |
| time_zone        | +00:00 |
+------------------+--------+
2 rows in set, 1 warning (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-12-06 06:45:47 |
+---------------------+
1 row in set (0.00 sec)

如果我们的系统是设置的东八区，那么需要加8小时

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

mysql> drop table db_sample_source;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `db_sample_source` (
    ->   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键（自增长）',
    ->   `enabled` int(1) DEFAULT '1' COMMENT '是否可用',
    ->   `db_name` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库名',
    ->   `db_url` varchar(256) COLLATE utf8_bin NOT NULL COMMENT '数据库链接',
    ->   `db_port` bigint(20) NOT NULL COMMENT '端口',
    ->   `user_name` varchar(256) COLLATE utf8_bin NOT NULL COMMENT '用户名',
    ->   `password` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '密码',
    ->   `db_type` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '数据库类型 oracle | mysql',
    ->   `db_description` varchar(1024) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库描述',
    ->   `db_table_name` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库表名',
    ->   `create_user` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '创建人',
    ->   `create_time` timestamp NOT NULL DEFAULT '1970-01-01 07:00:00' COMMENT '创建记录时间',
    ->   `modify_user` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '修改人',
    ->   `modify_time` timestamp NOT NULL DEFAULT '1970-01-01 07:00:00' COMMENT '修改记录时间',
    ->   `version` int(11) NOT NULL DEFAULT '0' COMMENT '版本',
    ->   `create_group` varchar(256) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '创建用户组',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='数据源来源数据库';
ERROR 1067 (42000): Invalid default value for 'create_time'
mysql> CREATE TABLE `db_sample_source` (
    ->   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键（自增长）',
    ->   `enabled` int(1) DEFAULT '1' COMMENT '是否可用',
    ->   `db_name` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库名',
    ->   `db_url` varchar(256) COLLATE utf8_bin NOT NULL COMMENT '数据库链接',
    ->   `db_port` bigint(20) NOT NULL COMMENT '端口',
    ->   `user_name` varchar(256) COLLATE utf8_bin NOT NULL COMMENT '用户名',
    ->   `password` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '密码',
    ->   `db_type` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '数据库类型 oracle | mysql',
    ->   `db_description` varchar(1024) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库描述',
    ->   `db_table_name` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库表名',
    ->   `create_user` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '创建人',
    ->   `create_time` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01' COMMENT '创建记录时间',
    ->   `modify_user` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '修改人',
    ->   `modify_time` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01' COMMENT '修改记录时间',
    ->   `version` int(11) NOT NULL DEFAULT '0' COMMENT '版本',
    ->   `create_group` varchar(256) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '创建用户组',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='数据源来源数据库';
Query OK, 0 rows affected (0.04 sec)