MySQL中同时存在创建和上次更新时间戳字段解决方法浅析；MYSQL日期 字符串 时间戳互转

最新推荐文章于 2021-11-21 21:59:48 发布
最新推荐文章于 2021-11-21 21:59:48 发布
阅读量439 收藏
点赞数
分类专栏： 数据库 文章标签： mysql时间戳 时间格式转换

因为欣赏所以转载，原文地址 http://www.2cto.com/database/201409/330251.html 

http://www.cnblogs.com/jhy-ocean/p/5560857.html

在写这篇文章之前，明确我的MySQL版本。

mysql> SELECT VERSION(); +------------+ | VERSION() | +------------+ | 5.5.29-log | +------------+ 1 row in set (0.00 sec)
第一个，测试通过。  
?
1
2
3
4
5
6
CREATE TABLE temp
(
     id INT (11) PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR (10),
     updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
?
1
2
3
4
5
6
7
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使用触发器。
?
1
2
3
4
5
6
7
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()；

具体解决方法如下：

?
1
2
3
4
5
6
7
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)

总结

平时比较常用的时间、字符串、时间戳之间的互相转换，虽然常用但是几乎每次使用时候都喜欢去搜索一下用法；本文将作为一个笔记，整理一下三者之间的 转换（即：date转字符串、date转时间戳、字符串转date、字符串转时间戳、时间戳转date，时间戳转字符串）用法，方便日后查看；

涉及的函数

date_format(date, format) 函数，MySQL日期格式化函数date_format()

unix_timestamp() 函数

str_to_date(str, format) 函数

from_unixtime(unix_timestamp, format) 函数，MySQL时间戳格式化函数from_unixtime

时间转字符串

  1. select date_format(now(), '%Y-%m-%d');  
  2.   
  3. #结果：2016-01-05  

时间转时间戳

  1. select unix_timestamp(now());  
  2.   
  3. #结果：1452001082  

字符串转时间

  1. select str_to_date('2016-01-02', '%Y-%m-%d %H');  
  2.   
  3. #结果：2016-01-02 00:00:00  

字符串转时间戳

  1. select unix_timestamp('2016-01-02');  
  2.   
  3. #结果：1451664000  

时间戳转时间

  1. select from_unixtime(1451997924);  
  2.   
  3. #结果：2016-01-05 20:45:24  

时间戳转字符串

  1. select from_unixtime(1451997924,'%Y-%d');  
  2.   
  3. //结果：2016-01-05 20:45:24  

附表

MySQL日期格式化（format）取值范围。

 值含义
秒%S、%s两位数字形式的秒（ 00,01, ..., 59）
分%I、%i两位数字形式的分（ 00,01, ..., 59）
小时 %H24小时制，两位数形式小时（00,01, ...,23）
%h12小时制，两位数形式小时（00,01, ...,12）
%k24小时制，数形式小时（0,1, ...,23）
%l12小时制，数形式小时（0,1, ...,12）
%T24小时制，时间形式（HH:mm:ss）
%r 12小时制，时间形式（hh:mm:ss AM 或 PM）
%p AM上午或PM下午 
  周  %W一周中每一天的名称（Sunday,Monday, ...,Saturday）
 %a一周中每一天名称的缩写（Sun,Mon, ...,Sat） 
%w 以数字形式标识周（0=Sunday,1=Monday, ...,6=Saturday） 
%U数字表示周数，星期天为周中第一天
%u数字表示周数，星期一为周中第一天
天%d 两位数字表示月中天数（01,02, ...,31）
%e  数字表示月中天数（1,2, ...,31）
 %D英文后缀表示月中天数（1st,2nd,3rd ...） 
 %j以三位数字表示年中天数（001,002, ...,366） 
月%M 英文月名（January,February, ...,December） 
%b 英文缩写月名（Jan,Feb, ...,Dec） 
%m 两位数字表示月份（01,02, ...,12）
%c 数字表示月份（1,2, ...,12） 
年%Y 四位数字表示的年份（2015,2016...）
%y  两位数字表示的年份（15,16...）
文字输出 %文字 直接输出文字内容

sweet阿林
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql死锁几种情况的测试
敖尔其楞的专栏
05-09 1万+
sessionA: test>begin -> ; Query OK, 0 rows affected (0.00 sec)test>select * from tt where id_test=1234 lock in share mode; +—-+———+ | id | id_test | +—-+———+ | 4 | 1234 | +—-+———+ 1 ro
如何建立mysql时间戳字段
lq547854的专栏
11-02 5449
<br />我用PHP+MYSQL写一个程序，需要数据库每次插入数据时，希望数据库有一个字段my_time，自动获取当前时间的时间戳，通过phpmyadmin有没有办法建立这样的字段？<br />你通过phpmyadmin查看表结构，下面会有插入字段的地方，选择类型一般INT，或者unsigned INT <br />把表列的类型改了timestamp类型，默认值改成CURRENT_TIMESTAMP 就可以自动获得当前时间<br />一个表格里面只能用一个<br />我不是要通过PHP代码写入数据库哦，而
《partner4java 讲述MySQL》之第一步：基本语法
爱.NET
11-27 207
（导读：本章学习的内容主要是对数据表和保存数据的基本管理语法；数据库非常重要，有时候大部分j2ee工程师也叫做数据库开发工程师，项目中无非就是对数据CURD操作；本章的学习无非就是大量语法的记忆，所以，建议自己动手。） 数据库：从本质上讲，数据库系统只不过是一套对大量信息进行管理的高效办法而已。（高效主要体现在：缩短信息记录的录入时间；缩短信息记录的检索时间；灵活的信息检索顺序；灵活的输出格式；...
数据库的主从模式以及双机热备
shi_xiansheng的博客
08-03 8643
主从结构搭建 配置主服务器(云主机4) 编辑主master服务器配置文件/etc/my.cnf #vim /etc/my.cnf 在[mysqld]节点下加入两句话 server-id=1  当前数据库在集群中的id值 log-bin=mysql-bin        #启用二进制日志；key值的名字不能变,value表示你定义 的二进制文件的名字.可以自定义 完成了二进制文件...
理解MySQL的索引与优化
lengyuhong的专栏
10-26 1699
写在前面：索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点。考虑如下情况，假设数据库中一个表有10^6条记 录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4 个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要 100s(但实际上要好很多很多)。如果对之建立B-Tree索引，则只需要进行log100(10^
详解MySQL日期 字符串 时间戳互转
09-09
在MySQL数据库中，日期、字符串和时间戳之间的转换是常见的操作。这些转换涉及到多个内置函数，可以帮助我们在处理日期和时间数据时实现灵活的格式化和解析。以下是对这些转换的详细说明： 1. **日期转字符串**：...
MySQL中日期和时间戳互相转换的函数和方法
09-10
在MySQL数据库中，日期和时间戳是两种不同的数据类型，它们在处理时间数据时各有所长。本篇文章将深入探讨如何在MySQL中进行日期和时间戳之间的转换，这对于数据分析、报表生成以及时间相关的操作至关重要。 首先，...
C#更新SQLServer中TimeStamp字段(时间戳)的方法
01-01
本文实例讲述了C#更新SQLServer中TimeStamp字段(时间戳)的方法。分享给大家供大家参考。具体实现方法如下： public partial class Form1 : Form { private SqlConnection mCnn = null; private long TimeStamp...
mysql 更新时间字段
LYW5856的专栏
11-21 1781
mysql更新表中日期字段时间 DATE_ADD和DATE_SUB函数可以用INTERVAL关键字标识的时间间隔进行加减操作，类似于 1.更新表中有效期valid_time字段值都增加一天 1 UPDATEequipment SETvalid_time=DATE_ADD(valid_time,INTERVAL 1DAY); 2.更新表中有效期equipment字段值都减少一天 1 UPDATEequipmentSET...
mysql主从错误总结
零零喵的博客
10-04 4176
转自：http://blog.51cto.com/loose/1224564 1 、出现错误提示 Slave I/O: error connecting to master ‘backup@192.168.0.x:3306’ -retry-time: 60 retries: 86400,Error_code: 1045 解决方法: 从服务器上删除掉所有的二进制日志文件，包括一个数据目录下的mas...
MySQL详解－－锁
热门推荐
上善若水，水善利万物而不争。
03-06 13万+
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。本章我们着重讨论MySQL锁机制的特点，常见的锁问题，以及解决MySQL
mysql 0.00 sec_MySQL InnoDB隔离级别
weixin_39635567的博客
02-07 374
上篇文章讨论了事务隔离级别，隔离级别这个东西在不同的数据库产品上，是有一些区别的，本篇重点讲讲mysql数据库。四种标准的隔离级别MySQL数据库都支持，下面我们一个一个看过来先。首先我们先创建一个简单的测试表。CREATE TABLE tb1(idINTNOT NULL,valueDECIMALNOT NULL,PRIMARY KEY (id))ENGINE=INNODB;Read Uncomm...
mysql 学习记录（三）--字段
bwshqh的博客
10-03 914
填充字段、自增长字段、带小数的字段、与时间有关的字段、varchar与char字段的长度、枚举型字段、set类型字段设置
MySQL SubQuery学习记录
余璜的技术博客
08-13 2569
The Subquery as Scalar Operand mysql> CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t1 VALUES(100, 'abcde'); Query OK, 1 row affect
MYSQL主、从服务器配置及错误处理
Sky 的专栏
02-14 8660
一. MySQL主服务器配置 1.建立用户 grant replication slave on *.* to gd@192.168.6.101 identified by '123456'; # grant replication slave on *.* to ‘用户名’@'主机’ identified by ‘密码’; # 可在B Slave上做连接测试: mysql -h 192.
mysql 数据库基本sql语句记录
chuxin_mm的博客
08-28 290
为表t_user 创建一个t_age的字段。 ALTER TABLE `t_user` ADD COLUMN `t_age` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '年龄';
MySQL增删改查之DML语句详解
总结沉淀
04-02 2157
DML语句 主要指数据库对表记录的操作，包括表记录的插入（insert）、更新（update）、删除（delete）和查询（select）等 以下我们的操作都基于下表进行： mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field    | Type
mysql 更新字段内指定字符串
rumengqiang的博客
10-10 722
例如：pot 列中 'yyy' 替换成 'xx' UPDATE tab set pot = REPLACE(pot,'yyy','xx');
mysql 存储，更新 时间戳转换成timestamp格式
dlh918的博客
09-07 570
FROM_UNIXTIME('时间戳"','%Y%m%d%H%i%s') update t_adv_bonus set USER_BONUS_TIME=FROM_UNIXTIME('".time()."','%Y%m%d%H%i%s')
mysql中字符串和时间互相转换的方法(自动转换及DATE_FORMAT函数)
IT匠人的专栏
09-14 3万+
本文导语: mysql中字符串和时间互相转换的方法(自动转换及DATE_FORMAT函数)一、mysql中字符串转换成日期下面将讲述如何在MYSQL中把一个字符串转换成日期：1、无需转换的：SELECT * FROM tairlist_day WHERE rq>'2010-07-31' AND rq mysql中字符串和时间互相转换的方法(自动转换及DATE_FORMAT函数)
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值