AICon 上海站|90%日程已就绪,解锁Al未来! 了解详情
写点什么

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

  • 2021-05-28
  • 本文字数:1331 字

    阅读完需:约 4 分钟

银行数据库迁移至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-05-28 08:001944

评论

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

心理行业需要用到堡垒机的几个情形讲解

行云管家

心理 网络安全 等保 堡垒机

MAMP PRO永久版:本地开发神器!

Rose

普洱市具有资质等保测评机构在哪里?电话多少?

行云管家

网络安全 等保 云南 普洱市

SketchUp Pro 2023:专业级3D建模,轻松实现创意构想

Rose

创意无限,绘图神器!OmniGraffle Pro,设计新高度

Rose

audirvana mac中文破解版 原生无损音乐播放器

Rose

车企接连入局人形机器人赛道,「小米」、「小鹏」能否维持先发优势

机器人头条

小米 人形机器人 具身智能 小鹏

一个明知没啥前途也要开张的市场开张了

明道云

淘宝天猫API接口探索:商品详情与关键字搜索商品列表的实战应用

代码忍者

API 接口 pinduoduo API

阿里大佬翻遍全网Java面试文章,总结出这份1658页文档,GitHub收获25K+点赞

Summer

Java 编程 程序员 面试 大厂

“泡沫”催生行业“繁荣”,切入具身智能赛道正当时!!

机器人头条

机器人 强化学习 大模型 人形机器人 具身智能

RFID技术在ERP系统中的集成应用

积木链小链

ERP RFID

想在 Java 八股文面试中脱颖而出?这1000 道互联网大厂 工程师面试题必不可少

Summer

Java 程序员 面试 架构师 编程开发

EndNote X9汉化安装包 及EndNote X9安装教程

Rose

解锁数据洞察新境界!Tableau Desktop Pro 2020

Rose

Qt 开发 macOS 应用的技术难点

北京木奇移动技术有限公司

软件外包公司 QT外包开发 QT开发公司

哪款远程控制软件最合适日常使用?几款远控软件盘点

科技热闻

零代码赋能高等教育数字化转型的实践与思考

明道云

ARM版CentOS Linux系统镜像文件(苹果M1专用) 及安装教程

Rose

macOS Developer Beta Access Utility(苹果开发者工具)

Rose

银行数据库迁移至MySQL,竟被时间字段这玩意耍了……_数据库_dbaplus社群_InfoQ精选文章