一句话总结
在导入该列的时候使用set子句,用str_to_date函数把指定格式的字符串做转换,极端恶劣情况下用case when对不同的格式分别转换。
背景
最近在做数据库作业,具体背景不重要,然后发现导入数据成了最困难的问题,具体就是在导入数据库之前要进行日期格式的转换,倒腾一下午终于算整完了(然后回头一搜发现解决办法,心态爆炸
基本介绍
mysql数据库存储的时间数据类型包括date、time、datatime等,但是date都是%Y-%m-%d 的形式,插入的时候可以自动识别由其他分割符分开的日期,但是遇到%m-%d-%Y就抓瞎了。
导入数据的基本格式是
LOAD
解决办法大概是在导入前就是利用set子句
这里参考了
LOAD DATA INFILE 导入数据www.jianshu.com这篇文章,
把数据从字符串转换为日期用到的mysql函数是str_to_date(date,’%Y-%m-%d’) ,第一个参数是需要转换的字符串,第二个是字符串的格式,具体各个占位符的意义可见
mysql中DATE_FORMAT()和str_to_date() 、to_daysmy.oschina.net这里用到的是%Y、%m、%d、%T,分别代表四位年、月、日、24小时的时间。
需要转化的数据格式如下
需要转换ObservationDate和Last Update两列
第一列换比较容易,在载入最后加上
set
即可
但是第二个就没那么容易,不光因为有时间,而且格式大概有不一样的三种,分别是
%m/%d/%Y %H:%i
%Y-%m-%dT%T
%Y-%m-%d %T
这个时候还要请出我们的case when,匹配不同类型的字符串然后返回不同的值
case
最后的载入数据语句
load
学习数据库真是漫漫长路啊