你在使用哪种编程语言?快来投票,亲手选出你心目中的编程语言之王 了解详情
写点什么

银行数据库迁移至 MySQL,竟被时间字段这玩意耍了……

2021 年 5 月 28 日

银行数据库迁移至MySQL,竟被时间字段这玩意耍了……

背景介绍

笔者在工作中曾遇到 SyBase 数据库迁移至 MySQL 时的一个问题:使用 bcp 将 SyBase 中的数据导出为 csv 文件时,datetime 数据类型默认导出格式与 MySQL 不兼容。Sybase 默认的 datetime 类型格式为 Mmm dd yyyy hh:nn:ss:sssAA,示例如下:


Dec 24 2017 12:00:00:000PM
复制代码


而 MySQL 中 datetime 类型格式为 yyyy-mm-dd hh:nn:ss,示例如下:


2017-12-24 12:00:00
复制代码


因此直接将 csv 文件导入 MySQL 会由于不兼容导致报错。

解决方式

解决此问题的方式不外乎两种:一是修改导出后的 csv 文件,令其时间字段的数据格式兼容 MySQL;二是修改 SyBase 数据库中时间字段的数据格式,使其直接能通过 bcp 导出兼容 MySQL 的 csv 文件。鉴于第一种方法较为复杂,本文笔者采用第二种方法解决该问题。

1、convert()函数介绍

convert()函数是 SyBase 数据库中用于数据类型转换的函数,其使用方式如下:


convert(datatype,expression,[format-style])
复制代码


  • datatype:必选参数,将要转换成的数据类型;

  • expression:必选参数,待转换的字段;

  • format-style:可选参数,转换时间类型时,决定输出格式的参数,对应关系见下表:


format-style的值

输出格式

输出示例

100

Mmm dd yyyy hh:nnAA

Dec 24 2017 12:00PM

101

mm/dd/yyyy

12/24/2017

102

yyyy.mm.dd

2017.12.24

103

dd/mm/yyyy

24/12/2017

104

dd.mm.yyyy

24.12.2017

105

dd-mm-yyyy

24-12-2017

106

dd Mmm yyyy

24 Dec 2017

107

Mmm dd,yyyy

Dec 24,2017

108

hh:nn:ss

12:00:00

109

Mmm dd yyyy hh:nn:ss:sssAA

Dec 24 2017 12:00:00:000PM

110

mm-dd-yyyy

12-24-2017

111

yyyy/mm/dd

2017/12/24

112

yyyymmdd

20171224


例如有一个名为 test 的表,将该表中一个名为 changetime,类型为 datetime 的字段转换为 varchar 类型,可以使用如下 SQL 语句:



select convert(varchar(100),changetime,111) as dates from test;
复制代码


若该字段仅有一行数据,其值为 Dec 24 2017 12:00:00:000PM,那么以上 SQL 输出的结果为 2017/12/24.

2、str_replace()函数介绍

str_replace()参数用于替换字符串中的部分字符,其使用方式如下:


str_replace(‘string1’,’string2’,’string3’)
复制代码


参数含义:

  • string1:必选参数,原始字符串;

  • string2:必选参数,待转换的字符;

  • string3:必选参数,需要转换成的字符;


例如以下 SQL:


select str_replace(‘2017/12/24’,’/’,’-’);
复制代码


其输出结果为 2017-12-24.

3、具体方案实施

由以上示例可见,若将 SyBase 中 datetime 类型字段使用 convert()函数转换为 varchar 类型,format-style 的值指定为 111,再使用 str_replace()函数,将 convert()转换来的字符串中的’/’转换为’-’,即可满足 MySQL 中对 datetime 类型的格式要求(yyyy-mm-dd);同理,format-style 的值指定为 108,即可满足 hh:nn:ss 的格式要求。具体的实施方案举例说明如下:


1)在 SyBase 数据库建立一张临时表 tmp,表结构与待导出数据的表 test 一致,仅 datetime 类型字段改为 varchar 类型;表结构见以下 SQL 语句:


create table test(id int,time datetime);create table tmp(id int,time varchar(100));
复制代码


2)采用如下 SQL 语句将原始表的数据存入临时表中:


insert into tmp select id,str_replace(convert(varchar(100),time,111),'/','-')+' '+convert(varchar(100),time,108) from test;
复制代码


3)将临时表 tmp 中的数据使用 bcp 命令导出为 csv 文件;


4)将 csv 文件中的数据导入 MySQL 数据库对应表中。


至此,解决了 SyBase 中 datetime 类型字段默认导出格式与 MySQL 不兼容的问题。


作者介绍

农行研发中心“数风云”团队,一支朝气蓬勃、快速成长的技术团队,始终致力于农行大数据、数据库和云计算等领域的应用实践与技术创新,探索数据赋能,勇攀数据云巅,为企业数字化转型和金融科技发展不断贡献力量。


本文转载自:dbaplus 社群(ID:dbaplus)

原文链接:银行数据库迁移至MySQL,竟被时间字段这玩意耍了……

2021 年 5 月 28 日 08:001044

评论

发布
暂无评论
发现更多内容

2021金三银四必问储备知识:Java线程池详解

Java王路飞

Java 程序员 面试 多线程 线程池

【百度官方技术分享】中间件技术在百度云原生测试中的应用实践

百度Geek说

产品 测试 中间件 技术宅 架构·

交易所搭建

v16629866266

交易所开发

JVM调优艺术:JVM内存管理机制深度剖析

程序员小毕

Java 程序员 面试 性能优化 JVM

容器 & 服务:Jenkins构建实例

程序员架构进阶

容器 持续集成 七日更 28天写作 2月春节不断更

helm入门教学

三丰SanFeng

Kubernetes k8s Helm

话题讨论 | 你选择去一线城市还是老家的省会城市?

石云升

话题讨论 职业发展 2月春节不断更

产品0期 - 第四周作业 - 附件1

曾烧麦

产品训练营

Qcon现代数据架构-《万亿级数据库MongoDB集群性能数十倍提升优化实践》核心17问详细解答

杨亚洲(专注mongodb及高性能中间件)

MySQL 数据库 mongodb 分布式 分布式数据库mongodb

惊呆,一条sql竟然让oracle奔溃了

程序员jinjunzhu

oracle mybatis 批量操作

用例图

Eva

第四周 开启新的篇章,打磨产品的最强辅助——文档

小匚

极客时间 产品经理 产品经理训练营

在游戏运营行业,Serverless 如何解决数据采集分析痛点?

阿里巴巴云原生

Serverless 运维 云原生 关系型数据库 消息中间件

2021年人工智能数据采集标注行业四大趋势预测;清华提出深度对齐聚类用于新意图发现

京东科技开发者

人工智能 数字货币

流媒体传输协议之 RTP (上篇)

阿里云视频云

音视频 流媒体 rtp

技术文档丨循迹搭建--车辆集成

百度开发者中心

关于产品文档与原型的思考

妹妹10分钟就玩懂了零拷贝和NIO,也太强了

moon聊技术

Java nio 零拷贝

第四次作业

Geek_79e983

【STM32】GPIO输入—按键检测

AXYZdong

硬件 stm32 2月春节不断更

产品0期 - 第四周作业

曾烧麦

产品训练营

作业 - 第四章 业务流程与产品文档 (一)

hao hao

产品经理训练营作业 03

KingSwim

羚羊行走在悬崖边:一份报告背后的移动开发者“自救计划”

脑极体

第四章作业

白知之明

一个只会写Bug的Coder年终总结

z小赵

程序员 互联网 职场成长

京东科技集团21篇论文高票入选国际顶会AAAI 2021

京东科技开发者

机器学习 AI

极客时间APP购买课程模块用例文档

夏天的风

用例图

作业 - 第四周

eva

WEEK4作业

Geek_6a8931

Vue开发中可以使用的ES6新特征

devpoint

Vue ES6

技术为帆,纵横四海- Lazada技术东南亚探索和成长之旅

技术为帆,纵横四海- Lazada技术东南亚探索和成长之旅

银行数据库迁移至MySQL,竟被时间字段这玩意耍了……-InfoQ