浅谈MySQL timestamp(3)问题

作者:johnny233 时间:2024-01-14 17:47:23 

背景

最近在负责开发维护的一款数据平台,有一个功能是把数据从某个源头数据源(如常规的JDBC数据源,MySQL,Oracle等)推到目地数据源(还包括企微,MQ等)。一次推送数据就是一个任务,当然需要记录此次推送任务的执行情况,如任务的开始时间,结束时间,任务名称,任务执行状态,任务日志(失败原因),执行人,执行方式(手动执行还是定时触发)。另外,从来源数据源取数,怎么取数是通过SQL指定的,那我们还可以记录一下SQL查询耗时,以及SQL查询条数。总耗时就是任务执行结束时间减去任务开始时间,总耗时肯定大于SQL查询耗时。

统计SQL查询耗时时,还需要考虑到SQL取数的数据量,假如SQL查询量为1000w,程序不可能一次查询全部数据,然后加工处理并发送到下游目地数据源,故而需要设置分批。假设批次为50w,则SQL查询耗时为20次查询的耗时之和。

扯远一句,项目是接手维护的,一开始的设计开发者是用秒来记录SQL查询耗时,这样一看就不严谨,因为很多SQL查询耗时根本不需要1秒。事实上就算查询耗时超过1s,1.6s和1.7s也是有区别的,故而需要带毫秒来优化记录SQL查询耗时。最后在前端展示时,用小数点来表示毫秒。

很常规,看起来也没有任何问题的数据表设计:

create table execlog (
   id             bigint(11) auto_increment primary key,
   total_sql_time bigint                                  null comment 'SQL执行耗时,单位豪秒',
   start_date     timestamp default CURRENT_TIMESTAMP not null comment '执行开始时间',
   end_date       timestamp                               null comment '执行结束时间',
);

注:total_sql_time注释单位毫秒,以及程序记录单位是后来优化调整的。

某次任务执行记录截图如下,发现总耗时为0秒,而查询耗时为146毫秒。这显然不符合逻辑。

浅谈MySQL timestamp(3)问题

优化

优化思路也不难,就是任务开始时间需要记录到毫秒级别,改进后的表结构为:

create table execlog (
   id             bigint(11) auto_increment primary key,
   total_sql_time bigint                                    null comment 'SQL执行耗时,单位豪秒',
   start_date     timestamp(3) default CURRENT_TIMESTAMP(3) not null comment '执行开始时间',
   end_date       timestamp(3)                                  null comment '执行结束时间',
);

值得注意的是,MySQL直到版本5.6(不太确定)才支持,如何知道自己使用的MySQL Server版本是否支持timestamp(3),执行语句即可验证,没有报错并且返回毫秒数表示支持:select now(3);

优化上面截图中的日志记录问题分为两个步骤,即日志记录和日志显示。

日志记录

增加一个取当时时间精确到毫秒的静态方法:

public static final String COMMON_DATE_WITH_MILLI_SECOND = "yyyy-MM-dd HH:mm:ss:sss";

public static String getNowWithMilliSecond() {
   SimpleDateFormat sdf = new SimpleDateFormat(Constant.COMMON_DATE_WITH_MILLI_SECOND);
   return sdf.format(new Date());
}

表结构如上改进后,发现start_date记录没有问题,带3位小数点。但end_date记录不到小数点,即未记录到毫秒,于是怀疑表结构不对。

某次任务执行肯定会有start_date,故而设置为not null,但会因发布,或调试中断等各种原因导致记录不到end_date时间点,因此字段不能设置为not null

落不到数据,怀疑需要给一个默认值,于是如下改表结构

create table execlog (
   id             bigint(11) auto_increment primary key,
   start_date     timestamp(3) default CURRENT_TIMESTAMP(3) not null comment '执行开始时间',
end_date       timestamp(3) default CURRENT_TIMESTAMP(3) null comment '执行结束时间'
);

但是还是记录不到结束时间的毫秒数。

此时只能好好看代码,end_date是在任务结束(不管是正常结束还是异常结束,异常结束在finally语句块)时updateExecLog更新:

<update id="updateExecLog" parameterType="com.xy.cloudiview.common.po.ExecLog">
   UPDATE execlog t
   <set>
       <if test="errorLog != null">
           t.error_log = #{errorLog},
       </if>
       t.end_date = now(),
   </set>
   WHERE t.id = #{id}
</update>

此处已经指定end_date取数为now(),肯定不会记录到毫秒数的。需改成t.end_date = now(3),

再看另外一个updateExecLog更新语句:

<update id="updateByPrimaryKeySelective" parameterType="com.xy.cloudiview.common.po.ExecLogWithBlobs">
   update execlog
   <set>
       <if test="endDate != null">
           end_date = #{endDate,jdbcType=TIMESTAMP},
       </if>
   </set>
   where id = #{id,jdbcType=BIGINT}
</update>

貌似找到一点问题解决思路,开启MySQL日志打印功能:

mybatis:
 configuration:
   log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

一个很无厘头的尝试:

<if test="endDate != null"> end_date = #{endDate,jdbcType=TIMESTAMP(3)},</if>

报错:

MyBatisSystemException: nested exception is org.apache.ibatis.builder.BuilderException: Error resolving JdbcType. Cause: java.lang.IllegalArgumentException: No enum constant org.apache.ibatis.type.JdbcType.TIMESTAMP(3)
    at java.lang.Enum.valueOf(Enum.java:238)

那就把jdbcType去掉:

<if test="endDate != null">
   end_date = #{endDate},
</if>

打印日志如下:

JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7ef24deb] will not be managed by Spring
==>  Preparing: update execlog SET end_date = ? where id = ? 
==> Parameters: 2022-10-12 17:08:13:013(String), 31542157(Long)
nested exception is com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '2022-10-12 16:05:47:047' for column 'end_date' at row 1

也就是说,当MyBatis遇到MySQL timestamp(3)时,MyBatis上面这种写法支持不了。

于是只能换一种写法:

<update id="updateByPrimaryKeySelective" parameterType="com.xy.cloudiview.common.po.ExecLogWithBlobs">
   update execlog
   <set>
   <if test="modelId != null">
           model_id = #{modelId,jdbcType=BIGINT},
       </if>
end_date = now(3),
   </set>
   where id = #{id,jdbcType=BIGINT}
</update>

解决问题。也不需要在Java代码层设置带毫秒数的当前时间。

此时,回头看看第一个截图,里面有一个日志类型的字段,文章开头没有描述。这里解释一下,因为平台有多种类型的任务,涉及多个maven Module模块和多个表,于是有多个execlog的insert和update语句。

再来看一个insertExecLog语句,省略无关字段,这种方法是MyBatis插件generator自动生成的,也没有任何问题,就是看起来非常冗余,两个<trim>语句块,再加上每个<trim>语句块里面每个字段都有<if>语句块条件判断语句:

<insert id="insertSelective" parameterType="com.xy.cloudiview.common.po.ExecLogWithBlobs">
 <selectKey resultType="java.lang.Long" keyProperty="id" order="AFTER">
       SELECT LAST_INSERT_ID()
   </selectKey>
   insert into execlog
   <trim prefix="(" suffix=")" suffixOverrides=",">
       <if test="startDate != null">
           start_date,
       </if>
   </trim>
   <trim prefix="values (" suffix=")" suffixOverrides=",">
       <if test="startDate != null">
           #{startDate,jdbcType=TIMESTAMP},
       </if>
   </trim>
</insert>

维护项目的一个默认的潜规则,就是除非没有大的问题,尽可能不要大面积改动代码,大面积改动还不如重构,重构的前提是对项目非常了解。

当然这个地方只是一个MyBatis方法而已,还达不到重构那个深度。但是我也是想着尽可能不要改动太多,于是改动如下:

<insert id="insertSelective" parameterType="com.xy.cloudiview.common.po.ExecLogWithBlobs">
 <selectKey resultType="java.lang.Long" keyProperty="id" order="AFTER">
       SELECT LAST_INSERT_ID()
   </selectKey>
   insert into execlog
   <trim prefix="(" suffix=")" suffixOverrides=",">
       start_date,
   </trim>
   <trim prefix="values (" suffix=")" suffixOverrides=",">
       now(),
   </trim>
</insert>

报错信息就不贴出来了,原因还是当MyBatis遇到MySQL timestamp(3)时,MyBatis上面这种写法不支持。

改动方法:

删除start_date对应的if判断语句块,让MySQL的default CURRENT_TIMESTAMP(3)来使其生效

使用如下方法:

<insert id="saveExecLog" parameterType="com.xy.cloudiview.common.po.ExecLogWithBlobs">
   INSERT INTO execlog(start_date) VALUES (now(3))
</insert>

日志查询

改进前的执行日志列表页查询语句为:

select
DATE_FORMAT(t.start_date,'%Y-%m-%d %H:%i:%s') as startDate,
TO_SECONDS(t.end_date)-TO_SECONDS(t.start_date) as totalTime
from execlog

改进后的:

select
SUBSTRING(DATE_FORMAT(t.end_date, '%Y-%m-%d %H:%i:%s.%f'), 1, 23) AS endDate,
TIMESTAMPDIFF(MICROSECOND, t.start_date, t.end_date) / (1000 * 1000) AS totalTime
from execlog

最后实现的效果是:

浅谈MySQL timestamp(3)问题

参考

get-milliseconds-with-date-format-in-mysql
https://stackoverflow.com/questions/26299149/timestamp-with-a-millisecond-precision-how-to-save-them-in-mysql
https://stackoverflow.com/questions/20520443/mysql-timestamp-to-default-null-not-current-timestamp

来源:https://blog.csdn.net/lonelymanontheway/article/details/127292085

标签:MySQL,timestamp(3)
0
投稿

猜你喜欢

  • Keras 使用 Lambda层详解

    2021-08-11 20:12:40
  • python selenium实现智联招聘数据爬取

    2022-08-30 00:29:02
  • 收集前端面试题之url、href、src

    2024-06-05 09:12:30
  • Vue.js 利用v-for中的index值实现隔行变色

    2024-04-16 09:35:40
  • js 替换功能函数,用正则表达式解决,js的全部替换

    2024-04-17 09:56:35
  • MySQL Server 层四个日志详解

    2024-01-18 12:12:59
  • 用Python代码来解图片迷宫的方法整理

    2023-03-17 18:35:43
  • python 绘制国旗的示例

    2023-01-05 19:29:32
  • chatgpt成功解决Access denied 1020错误问题(最新推荐)

    2022-04-15 15:30:09
  • python模块中pip命令的基本使用

    2023-09-28 22:44:20
  • 一篇文章了解Python中常见的序列化操作

    2023-01-22 05:22:20
  • 用python生成一张壁纸实例代码

    2022-06-06 10:26:51
  • Pycharm github配置实现过程图解

    2022-06-16 03:49:08
  • python批量替换页眉页脚实例代码

    2022-09-09 11:02:29
  • php strstr查找字符串中是否包含某些字符的查找函数

    2023-11-17 01:42:23
  • 不用Global.asa也能实现统计在线人数吗?

    2009-10-29 12:28:00
  • Mysql 数据库访问类

    2024-01-15 00:31:41
  • vue中el-table格式化el-table-column内容的三种方法

    2024-04-27 16:10:39
  • tensorflow ckpt模型和pb模型获取节点名称,及ckpt转pb模型实例

    2021-05-23 10:22:53
  • python函数递归调用的实现

    2022-08-14 20:03:57
  • asp之家 网络编程 m.aspxhome.com