MySQL中同时存在创建和上次更新时间戳字段解决方法浅析_MySQL
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL中同时存在创建和上次更新时间戳字段解决方法浅析_MySQL,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含8102字,纯文字阅读大概需要12分钟。
内容图文
![MySQL中同时存在创建和上次更新时间戳字段解决方法浅析_MySQL](/upload/InfoBanner/zyjiaocheng/580/d4ee456a25bc43488dadbf15083eaf38.jpg)
mysql> SELECT VERSION(); +------------+ | VERSION() | +------------+ | 5.5.29-log | +------------+ 1 row in set (0.00 sec)
第一个,测试通过。
CREATE TABLE temp ( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10), updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
CREATE TABLE temp ( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10), created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );第一种,created_at使用DEFAULT CURRENT_TIMESTAMP或者DEFAULT now(),updated_at使用触发器。
CREATE TABLE temp ( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10), created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp NULL );mysql> INSERT INTO temp(name,created_at,updated_at) VALUES('robin',now(),now()); Query OK, 1 row affected (0.03 sec) mysql> INSERT INTO temp(name,created_at,updated_at) VALUES('wentasy',now(),now()); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM temp; +----+---------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+---------+---------------------+---------------------+ | 1 | robin | 2014-09-01 14:00:39 | 2014-09-01 14:00:39 | | 2 | wentasy | 2014-09-01 14:01:11 | 2014-09-01 14:01:11 | +----+---------+---------------------+---------------------+ 2 rows in set (0.00 sec)
delimiter | DROP TRIGGER IF EXISTS tri_temp_updated_at; CREATE TRIGGER tri_temp_updated_at BEFORE UPDATE ON temp FOR EACH ROW BEGIN SET NEW.updated_at = now(); END; | delimiter ;
mysql> UPDATE temp SET name='robinwen' WHERE id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 #可以看到已经记录了第一条数据的更新时间 mysql> SELECT * FROM temp; +----+----------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+----------+---------------------+---------------------+ | 1 | robinwen | 2014-09-01 14:00:39 | 2014-09-01 14:03:05 | | 2 | wentasy | 2014-09-01 14:01:11 | 2014-09-01 14:01:11 | +----+----------+---------------------+---------------------+ 2 rows in set (0.00 sec)
第二种,created_at使用触发器,updated_at使用DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP或者DEFAULT now() ON UPDATE now();
具体解决方法如下:
CREATE TABLE temp ( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10), created_at timestamp NULL, updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );delimiter | DROP TRIGGER IF EXISTS tri_temp_created_at; CREATE TRIGGER tri_temp_created_at BEFORE INSERT ON temp FOR EACH ROW BEGIN IF new.created_at IS NULL THEN SET new.created_at=now(); END IF; END; | delimiter ;
mysql> INSERT INTO temp(name,created_at,updated_at) VALUES('robin',now(),now()); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO temp(name,created_at,updated_at) VALUES('wentasy',now(),now()); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM temp; +----+---------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+---------+---------------------+---------------------+ | 1 | robin | 2014-09-01 14:08:36 | 2014-09-01 14:08:36 | | 2 | wentasy | 2014-09-01 14:08:44 | 2014-09-01 14:08:44 | +----+---------+---------------------+---------------------+ 2 rows in set (0.00 sec)
mysql> UPDATE temp SET name='robinwen' WHERE id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 #可以看到已经记录了第一条数据的更新时间 mysql> SELECT * FROM temp; +----+----------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+----------+---------------------+---------------------+ | 1 | robinwen | 2014-09-01 14:08:36 | 2014-09-01 14:09:09 | | 2 | wentasy | 2014-09-01 14:08:44 | 2014-09-01 14:08:44 | +----+----------+---------------------+---------------------+ 2 rows in set (0.00 sec)
第三种,created_at指定timestamp DEFAULT '0000-00-00 00:00:00',updated_at指定DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP或者timestamp DEFAULT now() ON UPDATE now(); CREATE TABLE temp ( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10), created_at timestamp NULL DEFAULT '0000-00-00 00:00:00', updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
mysql> INSERT INTO temp(name,created_at,updated_at) VALUES('robin',now(),now()); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO temp(name,created_at,updated_at) VALUES('wentasy',now(),now()); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM temp; +----+---------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+---------+---------------------+---------------------+ | 1 | robin | 2014-09-01 14:10:43 | 2014-09-01 14:10:43 | | 2 | wentasy | 2014-09-01 14:10:57 | 2014-09-01 14:10:57 | +----+---------+---------------------+---------------------+ 2 rows in set (0.00 sec)
mysql> UPDATE temp SET name='robinwen' WHERE id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 #可以看到已经记录了第一条数据的更新时间 mysql> SELECT * FROM temp; +----+----------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+----------+---------------------+---------------------+ | 1 | robinwen | 2014-09-01 14:10:43 | 2014-09-01 14:11:24 | | 2 | wentasy | 2014-09-01 14:10:57 | 2014-09-01 14:10:57 | +----+----------+---------------------+---------------------+ 2 rows in set (0.00 sec)
第四种,更换MySQL版本,MySQL 5.6已经去除了此限制。
我们可以看下MySQL 5.5和5.6帮助文档对于这个问题的解释。
From the MySQL 5.5 documentation:
One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.
Changes in MySQL 5.6.5:
Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.
我们确定下MySQL的版本。
mysql> SELECT VERSION(); +---------------------------------------+ | VERSION() | +---------------------------------------+ | 5.6.20-enterprise-commercial-advanced | +---------------------------------------+ 1 row in set (0.00 sec) CREATE TABLE temp ( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10), created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); Query OK, 0 rows affected (0.28 sec)
mysql> INSERT INTO temp(name) VALUES('robin'); Query OK, 1 row affected (0.07 sec) mysql> INSERT INTO temp(name) VALUES('wentasy'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM temp; +----+---------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+---------+---------------------+---------------------+ | 1 | robin | 2014-09-01 15:05:57 | 2014-09-01 15:05:57 | | 2 | wentasy | 2014-09-01 15:06:02 | 2014-09-01 15:06:02 | +----+---------+---------------------+---------------------+ 2 rows in set (0.01 sec) mysql> UPDATE temp SET name='robinwen' WHERE id=1; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 #可以看到已经记录了第一条数据的更新时间 mysql> SELECT * FROM temp; +----+----------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+----------+---------------------+---------------------+ | 1 | robinwen | 2014-09-01 15:05:57 | 2014-09-01 15:06:45 | | 2 | wentasy | 2014-09-01 15:06:02 | 2014-09-01 15:06:02 | +----+----------+---------------------+---------------------+ 2 rows in set (0.00 sec)
总结
Good Luck!
Robin
2014年9月1日
内容总结
以上是互联网集市为您收集整理的MySQL中同时存在创建和上次更新时间戳字段解决方法浅析_MySQL全部内容,希望文章能够帮你解决MySQL中同时存在创建和上次更新时间戳字段解决方法浅析_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。