数据库|mysql教程
字段,同时,时间
数据库-mysql教程
在写这篇文章之前，明确我的MySQL版本。
hessian源码下载,vscode mayun,ubuntu源推荐,dns tomcat,怎么修复sqlite,万网免费云服务器,jquery 滚屏插件,锤子风格前端框架,爬虫与防爬虫,php和web的区别,岳麓区seo,出售家教网站模板,网页3d拖动效果,直播8 模板,js跳转指定页面,web医院管理系统,vb 无窗体程序lzw
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使用触发器。
协同网络办公系统源码,vscode怎样新建项目,ubuntu唤醒花屏,远程tomcat访问不了,高端爬虫,php 字符串是否为空,绵阳抖音seo推广,极品旅游网站,模板消息接口外链lzw
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 | -09-01 14:00:39 | -09-01 14:00:39 | | 2 | wentasy | -09-01 14:01:11 | -09-01 14:01:11 | +—-+———+———————+———————+ 2 rows in set (0.00 sec)
qq采集器源码,vscode运行ssm,ubuntu安装怎么分配空间,tomcat复制日志,安卓面试sqlite,slider插件下载,电商平台 前端框架,python爬虫频率过高,php 微信 源码,seo方案怎么写,爱心捐助公益网站源码,mp4网页播放代码,简单的九宫格html模板,滑动鼠标滑轮切换页面,自动办公管理系统 源码,java程序中加弹弹球动画lzw
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= obinwen 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 | -09-01 14:00:39 | -09-01 14:03:05 | | 2 | wentasy | -09-01 14:01:11 | -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( obin,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 | -09-01 14:08:36 | -09-01 14:08:36 | | 2 | wentasy | -09-01 14:08:44 | -09-01 14:08:44 | +----+---------+---------------------+---------------------+ 2 rows in set (0.00 sec)
mysql> UPDATE temp SET name= obinwen 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 | -09-01 14:08:36 | -09-01 14:09:09 | | 2 | wentasy | -09-01 14:08:44 | -09-01 14:08:44 | +----+----------+---------------------+---------------------+ 2 rows in set (0.00 sec)
第三种，created_at指定timestamp DEFAULT 000-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 000-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( obin,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 | -09-01 14:10:43 | -09-01 14:10:43 | | 2 | wentasy | -09-01 14:10:57 | -09-01 14:10:57 | +----+---------+---------------------+---------------------+ 2 rows in set (0.00 sec)
mysql> UPDATE temp SET name= obinwen 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 | -09-01 14:10:43 | -09-01 14:11:24 | | 2 | wentasy | -09-01 14:10:57 | -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( obin); 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 | -09-01 15:05:57 | -09-01 15:05:57 | | 2 | wentasy | -09-01 15:06:02 | -09-01 15:06:02 | +----+---------+---------------------+---------------------+ 2 rows in set (0.01 sec) mysql> UPDATE temp SET name= obinwen 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 | -09-01 15:05:57 | -09-01 15:06:45 | | 2 | wentasy | -09-01 15:06:02 | -09-01 15:06:02 | +----+----------+---------------------+---------------------+ 2 rows in set (0.00 sec)
总结
Good Luck!
Robin
9月1日
