从CSV导入到(My)SQL数据库时，能否将Unix时间戳自动转换为DATETIME列? [英] Can I get Unix timestamp automatically converted to a DATETIME column when importing from CSV to a (My)SQL database?

问题描述

我创建了一个具有timestamp DATETIME列的表，并且试图从具有Unix时间戳行的CSV文件中导入数据.

I created a table that has a timestamp DATETIME column and I am trying to import data from CSV files that have Unix timestamped rows.

但是，当我尝试使用DataGrip的CSV导入功能时，出现了类似的错误

However, when I try to use DataGrip's CSV import feature, I get errors to the tune of

1:1: conversion failed: "1394669640" to datetime
2:1: conversion failed: "1394670060" to datetime
3:1: conversion failed: "1394670480" to datetime
4:1: conversion failed: "1394670780" to datetime

也许dataGrip不是最好的工具，但是鉴于我没有直接访问运行DB服务器[*]的计算机的权限，所以这几乎是我能做的.显然，如果我将列设置为INT，则此方法有效.

Maybe dataGrip is not the best tool, but that's pretty much all I can do with, given that I don't have direct access to the machine which runs the DB server[*]. Obviously this works if I set the column to be INT.

有没有一种配置表格的方式，以便自动进行转换?

Is there a way of configuring the table so that the conversion happens automatically?

[*]是的，但是DBMS在Docker容器内运行，因此，除了在主机上复制CSV文件然后在容器内复制之外，我不能像LOAD DATA INFILE这样玩弄技巧并使用FROM_UNIXTIME().

[*] I do, but the DBMS is running inside a Docker container so, short of copying the CSV files on the host and then inside the container, I can't play tricks like LOAD DATA INFILE and set the column with FROM_UNIXTIME().

推荐答案

您可以使用LOAD DATA LOCAL INFILE将CSV文件加载到远程MySQL(即docker容器内)，而无需将CSV文件复制到容器中

You can use LOAD DATA LOCAL INFILE to load a CSV file to a remote MySQL (i.e. inside a docker container), without needing to copy the CSV file into the container.

mysql> create table MyTable (timestamp DATETIME PRIMARY KEY);

mysql> load data local infile 'data.csv' into table MyTable (@dummy) 
    SET timestamp = FROM_UNIXTIME(@dummy);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from MyTable;
+---------------------+
| timestamp           |
+---------------------+
| 2014-03-13 00:14:00 |
| 2014-03-13 00:21:00 |
| 2014-03-13 00:28:00 |
| 2014-03-13 00:33:00 |
+---------------------+

请注意，必须同时在服务器，远程客户端和客户端中启用local-infile选项.要执行上面的示例，我必须在服务器的/etc/my.cnf中启用local-infile=1，并且还使用选项mysql --local-infile运行了客户端.如果没有这两个选项，我会得到一个非常令人困惑的错误:

Note the local-infile option needs to be enabled both on your server, to allow remote clients, and also in the client. To do the example above, I had to enable local-infile=1 in my server's /etc/my.cnf and I also ran the client with the option mysql --local-infile. Without both of those options, I got this very confusing error:

错误1148(42000):此MySQL版本不允许使用所使用的命令

阅读 https://dev.mysql. com/doc/refman/5.7/en/load-data-local.html 了解详情.

这篇关于从CSV导入到(My)SQL数据库时，能否将Unix时间戳自动转换为DATETIME列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！