笔者在工作中曾遇到 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 文件。鉴于第一种方法较为复杂,本文笔者采用第二种方法解决该问题。
convert()函数是 SyBase 数据库中用于数据类型转换的函数,其使用方式如下:
convert(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.
str_replace()参数用于替换字符串中的部分字符,其使用方式如下:
str_replace(‘string1’,’string2’,’string3’)
复制代码
参数含义:
例如以下 SQL:
select str_replace(‘2017/12/24’,’/’,’-’);
复制代码
其输出结果为 2017-12-24.
由以上示例可见,若将 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)
领取专属 10元无门槛券
私享最新 技术干货