代码之家 › 专栏 › 技术社区 › Steven Smith

MYSQL读取原始CSV数据

mysql-workbench csv mysql

Steven Smith · 技术社区 · 6 年前

出于好奇,我经常从同事那里得到原始数据CSV,有时我会查看原始数据,MySQL无法读取日期、时间戳和货币等数据,

时间戳示例 2018.03.05 18:38:41 然后我使用工作台将其导出到MySQL,然后显示 0000-00-00 00:00:00 因为MYSQL更喜欢使用自己的数据类型

我已经在MySQL参考上看过了

DATETIME类型用于同时包含日期和时间的值部分。MySQL检索并显示“YYYY-MM-DD”中的日期时间值 HH:MM:SS格式。支持的范围为“1000-01-01 00:00:00”到 “9999-12-31 23:59:59”。

另外,还有一个关于货币的问题(使用十进制(13,2)),因为我得到了欧元格式,如 35,72 因为我必须换成 35.72 如果我忽略它,因为MySQL只会 35.00 并从中留出72美分。

它可以是一个痛苦的我检查所有的CSV文本的日期和金钱。我问过同事这个CSV,他说他从他们的软件中得到了导出CSV,因为他不能在软件上修改它。

有没有办法可以通过Mysql读取并翻译到正确类型的数据字段。我不确定Workbench是否能做到这一点。

1 回复 | 直到 6 年前

Bill Karwin 6 年前

时间戳格式应该不会有问题。我测试了一下,效果很好。

https://dev.mysql.com/doc/refman/5.7/en/datetime.html

MySQL允许对指定为字符串的值使用宽松的格式,其中任何标点字符都可以用作日期部分或时间部分之间的分隔符。

货币格式必须分配给字符串变量,然后可以根据解析字符串的表达式设置货币列。

这是mysql客户端中的一个示例,但您可以在mysql Workbench中执行相同的操作:

mysql> create table test (id int, d datetime, money decimal(13,2));

mysql> load data local infile 'test.csv' into table test 
   (id, d, @n) 
   set money = @n + substring_index(@n, ',', 2)/100;

mysql> select * from test;
+------+---------------------+-------+
| id   | d                   | money |
+------+---------------------+-------+
|  123 | 2018-03-05 18:38:41 | 35.35 |
+------+---------------------+-------+

回复您的评论:

mysql> select str_to_date('05.03.2018 18:38:41 UTC', '%m.%d.%Y %k:%i:%s UTC') as d;
+---------------------+
| d                   |
+---------------------+
| 2018-05-03 18:38:41 |
+---------------------+

不幸的是,时区没有日期格式代码,但如果您可以在收到的数据中始终使用UTC,那么您可以在格式字符串中使用UTC,如我上面所示。

看见 https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date