写点什么

银行数据库迁移至 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:002019

评论

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

Week5 一致性Hash

evildracula

学习 架构

第 9 周 作业

Pyr0man1ac

架构师训练营第二期 第5周总结

月下独酌

极客大学架构师训练营

JPA-技术专题-JPA&Hibernate注解

码界西柚

第9周作业

paul

第 05 周学习总结 [架构师训练营第 2 期]

Airship

极客大学架构师训练营

第五周总结

jizhi7

极客大学架构师训练营

第 8 周 作业

Pyr0man1ac

架构师训练营第九周学习笔记

一马行千里

学习 极客大学架构师训练营

架构师训练营 - 第 9周课后作业 - JVM 垃圾回收原理

树森

架构师训练营第五周作业

邢永春

第九周作业 (作业一)

Geek_83908e

架构师一期

架构师训练营第一期-第九周学习总结

卖猪肉的大叔

架构师训练营 - week09 - 学习总结

lucian

极客大学架构师训练营

架构师训练营第五周学习总结

邢永春

【架构师训练营 1 期】第九周作业

诺乐

第五周作业总结

hunk

极客大学架构师训练营

第 9 周 997日记

Pyr0man1ac

第五周作业

hunk

极客大学架构师训练营

第九周作业

熊桂平

极客大学架构师训练营

Architecture Phase1 Week9:Summarize

phylony-lu

极客大学架构师训练营

架构第九周总结

Geek_Gu

极客大学架构师训练营

第九周作业(作业二)

Geek_83908e

架构师一期

架构师训练营 - week09 - 作业

lucian

极客大学架构师训练营

架构第九周作业

Geek_Gu

极客大学架构师训练营

架构师训练营第九周命题作业

一马行千里

学习 极客大学架构师训练营

架构师训练营第 2 期 第五周作业

月下独酌

极客大学架构师训练营

Architecture Phase1 Week9:HomeWork

phylony-lu

极客大学架构师训练营

架构师训练营第一期-第九周课后作业

卖猪肉的大叔

极客大学架构师训练营

架构师训练营 - 第九周

袭望

第 8 周 996日记

Pyr0man1ac

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