关于date格式的两个案例(r4笔记第96天)

在工作中总是会碰到各种和date相关的问题,一般这种问题都是让人很纠结的。 比如前几天一个朋友和我分享了他关于时间问题的两个案例。 第一个是他在做impdp导入数据的时候,发现导入很顺利完成了,但是原本的timestamp数据类型的数据都变成了sysdate值了,这样数据就完全不对应了。timestamp的数据类型精度要高得多,可以精确到小数秒(默认是6位,算是微妙了,可以最高到9位),可能在有些系统中精确到秒已经足够了,但是据我所知,还没有这种数据类型的强制转换。我们聊了会,基本的共识就是数据库层面不会自动做这种数据类型的转换,过了会他告诉我,发现问题了,是由于触发器导致的。 触发器的内容大体如下:

CREATE OR REPLACE TRIGGER "XXXXXX_TRG" BEFORE INSERT OR UPDATE ON XXXXXX_HISTORY REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN IF (INSERTING) THEN :NEW.ADD_TIMESTAMP := SYSTIMESTAMP; END IF; :NEW.REC_TIMESTAMP := SYSTIMESTAMP; END;

有了这些信息,也算是虚惊一场。 不过触发器带来的问题真是无所不在,我之前在数据迁移的时候碰到一个问题,也是在数据导入的时候没有禁用触发器,结果数据导入之后,本来需要导入1500万数据的表,结果在数据导入之后发现导入了1800万,多了300万的数据,当时也是感觉很蹊跷,最后排查发现是由于触发器导致的。最后也是经过反复确认,准备了一堆的脚本最终清理了那多余的300万数据。也算是沉痛的教训,所以在数据导入的过程中,触发器的作用是显而易见的,有时候我们需要禁用,有时候需要启用,得看业务需要了。 过了几天,这个朋友碰到了另外一个问题,问题的场景更为复杂,是一个日本开发的系统做迁移(包括应用代码迁移),通过客户端程序调用的时候发生了数据类型的冲突。 朋友在他们的应用程序中的代码查到他们的SQL代码。

如下格式: select * from emp where hiredate >= to_char(sysdate,'YYYY-MM-DD'); 他们在sql中用date 型数据与 char型的数据做了一个比较。 大家都知道,这种写法是有错误的,一般会报一个 ORA-01861: 文字与格式字符串不匹配 但是这生产环境已经运行了很长时间,一直没有报错,原因是为什么呢? 大家都知道 NLS_DATE_FORMAT这个参数,也知道有LANG这个环境变量 也知道session>instance>database; 但是是否有想过session中这个 NLS_DATE_FORMAT 是根据什么去判定的?是session 发起端的 date_format,还是 db端响应的date_foramt. date_format 与语言环境又有没有关系? 一般来说能够修改NLS_DATE_FORMAT的方式有以下几种 第一种是通过系统变量NLS_DATE_FORMAT,这个变量也是依赖于NLS_LANG的设置的。 第二种是通过sqlplus的glogin.sql来设置NLS_DATE_FORMAT,alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';这个也算是统一的配置了。

第三种是通过session级的变更来完成,直接作用于当前的session. alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

第四钟是通过system级的变更,直接通过alter system set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';这个作用范围很广,一般是不需要这么做的。 这几种方式貌似都和他碰到的问题不搭边,最后他是通过logon触发器来搞定的。可以设定一些规则,做一些针对性的操作。 触发器的内容如下: CREATE OR REPLACE TRIGGER LOGINTRG AFTER LOGON ON DATABASE BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''yyyy-mm-dd hh24:mi:ss'''; END LOGINTRG; 这种方式确实是奏效了,但是解决了问题但是心里还是感觉不够踏实,因为这种一种特殊的处理。 最后他们经过很多的分析,原本的设置是英文,发现修改客户端的LANG参数配置为日文就不需要触发器了,这个问题就引刃而解了。 我们先从LANG这个环境变量入手 大家都知道 LANG变量是language的简称,比如这几种配置 LANG=zh_CN.UTF-8 LANG=en_US.UTF-8 LANG=ja_JP.UTF-8 下面是三个语言环境的日期格式 [oracle@iZ23snm97y9Z ~]$ export LANG=zh_CN.UTF-8 [oracle@iZ23snm97y9Z ~]$ date 2015年 04月 01日 星期三 11:14:50 CST [oracle@iZ23snm97y9Z ~]$ export LANG=ja_JP.UTF-8 [oracle@iZ23snm97y9Z ~]$ date 2015年 4月 1日 水曜日 11:15:04 CST [oracle@iZ23snm97y9Z ~]$ export LANG=en_US.UTF-8 [oracle@iZ23snm97y9Z ~]$ date Wed Apr 1 11:15:23 CST 2015 LANG是针对Linux系统的语言、地区、字符集的设置,对linux下的应用程序有效,如date;NLS_LANG是针对Oracle语言、地区、字符集的设置,对oracle中的工具有效 这个问题的模拟,还是需要模拟客户端程序来做了,关于日期的一些格式化设置,目前还是需要通过分析一下JDBC中的一些细节来看看有什么细节之处了。

最后还是感谢木呼提供的精彩案例。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-04-04

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java面试通关手册

Mysql锁机制简单了解一下

Java面试通关手册(Java学习指南,欢迎Star,会一直完善下去,欢迎建议和指导):https://github.com/Snailclimb/Java_G...

171110
来自专栏jouypub

MySQL的语句执行顺序

MySQL的语句一共分为11步,如下图所标注的那样,最先执行的总是FROM操作,最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表,这个虚拟的表作为...

5110
来自专栏小怪聊职场

MySQL(七)|MySQL分库分表的那点事(小怪的Java群第一次话题讨论)

29940
来自专栏扎心了老铁

Elasticsearch-sql 用SQL查询Elasticsearch

Elasticsearch的查询语言(DSL)真是不好写,偏偏查询的功能千奇百怪,filter/query/match/agg/geo各种各样,不管你是通过封装...

1.3K40
来自专栏chenssy

在一个千万级的数据库查寻中,如何提高查询效率?

1、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 orderby 涉及的列上建立索引;

15220
来自专栏chenssy

【死磕Sharding-jdbc】---结果合并总结

这句SQL会使得MySQL在无法利用索引的情况下跳过1000000条记录后,再获取10条记录,其性能可想而知。而在分库分表的情况下(假设分为2个库),为了保证数...

19230
来自专栏互扯程序

数据库SQL优化大总结之 百万级数据库优化方案

网上关于SQL优化的教程很多,但是比较杂乱。近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充。

13040
来自专栏杨建荣的学习笔记

迁移式升级的测试(三)(r10笔记第36天)

还是继续昨天的任务。 前面的内容可以参见:迁移式升级的一点思考 (r10笔记第27天)、迁移式升级的新方案测试 (r10笔记第30天)、迁移式升级的测试(二)(...

36050
来自专栏逸鹏说道

程序猿是如何解决SQLServer占CPU100%的

文章目录 遇到的问题 使用SQLServer Profiler监控数据库 SQL1:查找最新的30条告警事件 SQL2:获取当前的总报警记录数 有哪些SQL语句...

38480
来自专栏技术沉淀

Python: 操作MySQL数据库

20340

扫码关注云+社区

领取腾讯云代金券