MySQL CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 详解 - chenshun123的博客 - CSDN博客

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/chenshun123/article/details/79677433

1> CURRENT_TIMESTAMP : 当要向数据库执行 insert操作时，如果有个 timestamp字段属性设为 CURRENT_TIMESTAMP，则无论这个字段有没有set值都插入当前系统时间

2> ON UPDATE CURRENT_TIMESTAMP : 使用 ON UPDATE CURRENT_TIMESTAMP 放在 TIMESTAMP 类型的字段后面，在数据发生更新是该字段将自动更新时间

mysql> CREATE TABLE time(

    -> id INT(22) PRIMARY KEY AUTO_INCREMENT,

    -> name VARCHAR(100),

    -> time1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -> time2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -> time3 TIMESTAMP DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,

    -> time4 TIMESTAMP DEFAULT '0000-00-00 00:00:00'

    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO time(name) VALUES('a'),('b'),('c');

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM time;

| id | name | time1               | time2               | time3               | time4               |

|  1 | a    | 2018-03-08 02:10:44 | 2018-03-08 02:10:44 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |

|  2 | b    | 2018-03-08 02:10:44 | 2018-03-08 02:10:44 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |

|  3 | c    | 2018-03-08 02:10:44 | 2018-03-08 02:10:44 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |

3 rows in set (0.00 sec)

mysql> UPDATE time SET name='a1' WHERE id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM time;

| id | name | time1               | time2               | time3               | time4               |

|  1 | a1   | 2018-03-08 02:11:03 | 2018-03-08 02:10:44 | 2018-03-08 02:11:03 | 0000-00-00 00:00:00 |

|  2 | b    | 2018-03-08 02:10:44 | 2018-03-08 02:10:44 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |

|  3 | c    | 2018-03-08 02:10:44 | 2018-03-08 02:10:44 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |

3 rows in set (0.00 sec)

Original url: Access
Created at: 2019-02-26 15:02:11
Category: default
Tags: none

未标明原创文章均为采集，版权归作者所有，转载无需和我联系，请注明原出处，南摩阿彌陀佛，知识，不只知道，要得到

请先后发表评论
  • 最新评论
  • 总共2条评论
DeepMind

Rain酱：

坑位 time3 TIMESTAMP DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,

2019-02-26 15:03:02 回复

DeepMind

Rain酱：

time2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

2019-02-26 15:02:48 回复