写点什么

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

评论

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

你不可不知道的Design Thinking

长沙造纸农

设计 思维方式 设计思维 设计实践 设计师

Mac搭建本地koa2项目

靖仙

koa2 本地环境搭建

中国移动张浩:AMQP on Pulsar 的设计与应用一览

Apache Pulsar

Apache 云原生 Apache Pulsar 消息中间件 AMQP

Flink SQL 1.11 新功能与最佳实践

Apache Flink

flink

Nodejs使用es module开发CLI

zayfen

nodejs Module ES cli esm

详解增强算术赋值:“-=”操作是怎么实现的?

Python猫

Python 编程

Apache Pulsar 社区周报|08-22 ~ 09-04

Apache Pulsar

开源 云原生 Apache Pulsar 消息系统 消息中间件

如何基于 Flink 生成在线机器学习的样本?

Apache Flink

flink

华为HMS:风雨突然,仍求自我

脑极体

Hadoop 客户端节点

yuanhang

hadoop3

作为一个架构师,我是不是应该有很多职责?

架构师修行之路

程序员 架构师

第13周总结+作业

林毋梦

面经手册 · 第10篇《扫盲java.util.Collections工具包,学习排序、二分、洗牌、旋转算法》

小傅哥

Java 数据结构 算法 归并排序 洗牌算法

前端 10 问之 TypeScript (第一篇)

局外人

typescript 大前端

CDN百科11 | 如何用CDN加速OSS源站资源

阿里云Edge Plus

随笔文,关于”中台“ 的一些“小偏论”

松子(李博源)

大数据 数据中台 中台 数据平台

Elasticsearch之文档操作

北漂码农有话说

Git 中submodule的使用,终于有人说明白了

Java架构师迁哥

MySQL中my.cnf的配置说明

Matrix Chan

运维 MySQ MySQL 运维 数据库运维

大厂面试爱问的「调度算法」,20 张图一举拿下

Java架构师迁哥

2020年史诗级‘面试宝典’横空而出,金九银十就靠“它”涨薪了

学习 程序员 面试 架构师

Spring 5 中文解析核心篇-集成测试之TestContext(下)

青年IT男

单元测试 Spring5

记一次前端vue相关面试题目和简答案

靖仙

面试 Vue MVVM

机器学习及信息与认知的逻辑

superman

学习 认知提升 个人提升

Redis-技术专题-Redis知识体系

码界西柚

甲方日常 12

句子

工作 随笔杂谈 日常

话题讨论 | 程序员们来说一说,你们从编程开始到现在共使用过多少种语言?

InfoQ写作社区官方

写作平台 话题讨论 语言

Hadoop3 环境搭建

yuanhang

金九银十:搞定这两个GitHub标星50K开源项目,拿个30k轻松吧?

编程 程序员 面试 算法 架构师

MySQL8.0大表秒加字段,是真的吗?

Simon

MySQL

5G加速商用,云通信如何推动企业营销数智化

阿里云Edge Plus

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