将空值和格式不正确的日期时间值导入到MySQL的日期时间列中

Question

我正在使用Sequel Pro界面的MySQL数据库，对SQL还不熟悉。我试图从csv文件中导入数据，并且我要导入的一列是datetime类型。但是，我收到的数据格式为mm/dd/yy hh:mm AM/PM或null。最初，我修改了该列的类型为varchar以避免问题，但现在我需要对数据执行一些日期函数，除非该列具有datetime类型和格式，否则无法完成操作，因此我需要一种将传入数据转换为正确datetime格式的方法。

此外，没有SQL或数据库知识的人将运行导入语句，因此最好让他们只需单击文件--导入，而无需在mysql命令行中输入任何复杂内容。另外，在运行查询后，我需要按相同的格式导出数据（mm/dd/yy hh:mm AM/PM或null）。

以下是该列的一些示例值：

Completion Time
null
6/16/14 10:33 AM
null
null
6/16/14 13:03 PM
6/17/14 13:53 PM
6/18/14 14:38 PM
6/18/14 14:52 PM
6/19/14 13:13 PM
6/18/14 18:56 PM
6/18/14 19:02 PM
null

我从几个小时的谷歌搜索中得出的一个可能简单的解决方案，是将列类型保持为varchar，然后以某种方式提取传入数据的mm/dd/yy部分，将其转换为正确的MySQL日期格式，然后执行我的日期函数。

无论如何，任何帮助都将不胜感激。

- djar

---

您需要将输入的日期转换，因为它不是mysql可以正确解析的格式。它无法可靠地确定哪一部分是月份，哪一部分是日期。输出后可以使用此链接重新转换：http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format - ToBe

有没有一种方法可以设置存储过程或触发器来转换传入的日期？ - djar

Answer 1

使用MySQL的日期和时间函数并不困难。STR_TO_DATE可以满足您的导入需求:

我接收到的数据格式为mm/dd/yy hh:mm AM/PM或null。

您可以使用以下代码获取DATETIME值：

STR_TO_DATE(yourValue, '%m/%d/%y %h:%i %p')

您可以在函数DATE_FORMAT的说明中找到STR_TO_DATE的说明。

对于导出操作，您可以使用与之前完全相同的格式字符串，使用已提及的DATE_FORMAT函数进行反向处理：

SELECT DATE_FORMAT(your_datetime_col, '%m/%d/%y %h:%i %p')

请看这个 演示 您可以在INSERT语句中进行转换，就像这样:

INSERT INTO example (date_time) VALUES 
(STR_TO_DATE('09/26/14 07:30 AM', '%m/%d/%y %h:%i %p'));

请看更新后的演示

使用LOAD DATA INFILE自定义导入

假设我们有一个名为example的表，有两列id和date_time：

CREATE TABLE example (
    id INT NOT NULL PRIMARY KEY,
    date_time DATETIME
);

我们有一个CSV文件示例example.csv，其中包含以下数据：

id,date
1,09/26/14 07:30 AM
2,07/23/14 07:30 PM

要使用LOAD DATA INFILE导入此文件，您需要使用以下语句：

LOAD DATA INFILE 'path/on/server/to/example.csv'
INTO TABLE example
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES             -- because of the column headers in the first line
(id, @var1)                -- You've got to map every field of your csv file to a column
                           -- of your table.
                           -- You've got to list the names of the columns of your table,
                           -- not the headers in the csv file.
                           -- if one field should be ignored, use another variable for this
                           -- field.
SET date_time = STR_TO_DATE(@var1, '%m/%d/%y %h:%i %p');

如果CSV文件中的日期包含文本字符串“null”，表示该值为NULL，则应使用CASE运算符：

date
09/26/14 07:30 AM
null
07/23/14 07:30 PM

那么我们必须使用：

LOAD DATA INFILE 'path/on/server/to/example.csv'
INTO TABLE example
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n' -- your line endings
IGNORE 1 LINES             -- because of the column headers in the first line
(@var1)                     -- read all parts of the date in variables
SET completionTime = CASE 
                     WHEN @var1 = 'null' THEN NULL 
                     ELSE STR_TO_DATE(@var1, '%m/%d/%y %h:%i %p')
                     END;

在这种情况下，包含分隔符（逗号）的字段的问题，您已经通过用一个封闭字符来封闭这些字段（或者所有字段）来解决。

但我们确实应该查看您的真实格式。

- VMai

---

我的字段没有用引号括起来，我已经按照你的建议更改了字段名称以保护隐私。问题是日期时间列完全为空，其他列的数据也混乱不堪，日期时间列中的一些字符出现在其他列中，替换了实际应该存在的数据，然后这些数据被移动到了下一列。 - djar

@djar 由于您的日期列是第一列，请给我一个或两个带有日期（不应该有问题）和第二字段匿名数据的行。 很显然，如果日期列和第一字段可以正常工作，则其余部分不应该成为大问题。 - VMai

@djar 听起来你的数据里有逗号，导致了映射出现问题。 - Michael McGriff

@djar：这似乎不难：请将日期复制到您的问题中，以便我们可以看到格式。 - VMai

@djar 我猜测 STR_TO_DATE 由于某些原因失败了。请发布该列中的一些实际值，以便我们可以看到它正在尝试转换的内容。 - Michael McGriff

Answer 2

你问题的真正答案：

如果你有非技术人员运行Sequel Pro导入并需要数据操作，则需要编写导入/导出脚本，用户可以上传和下载Excel表格。

然而...

我曾经遇到过使用Sequel Pro导入日期的类似问题。所以这里有一个“半”解决方案。你可以从中得到一些启示。

（注意：这个问题是5年前提出的，因此这对于任何遇到类似Sequel Pro问题的人都有好处。）

这是一个“半”解决方案：

首先在Excel中重新格式化DateTime。

1. 选择列
2. 转到“格式”>“单元格”
3. 选择“自定义”
4. 对于类型使用“yyyy-mm-dd hh:mm:ss”
5. 对于“null”值，输入一个明显错误的日期，如“2099-01-01 00:00:00:00”。

然后导入到Sequel Pro中。

1. 打开 Sequel Pro
2. 转到“视图”>“显示控制台”（这样可以避免 Sequel Pro CSV 导入崩溃）
3. 打开数据库连接并选择表格。
4. 导入 CSV。
5. 注意任何出现的错误

导入后清理数据。 （我告诉过你这只是半个解决方案）

1. 在 Sequel Pro 中运行此查询：UPDATE your_table SET your_column = NULL WHERE your_column = '2099-01-01 00:00:00:00';

- Adam