MySQL CSV 导入：日期时间值

MySQL CSV import: datetime value

提问by Binary Alchemist

I have been banging my head against a wall trying to import datetime values from a .csv file.

我一直在试图从 .csv 文件中导入日期时间值。

Here's the import statement.

这是导入语句。

LOAD DATA LOCAL INFILE 'myData.csv' INTO TABLE equity_last_importFIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (equity,last,@last_date) SET last_date = STR_TO_DATE( @last_date, '%Y-%m-%d %H:%i:%s')

LOAD DATA LOCAL INFILE 'myData.csv' INTO TABLE equity_last_importFIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (equity,last,@last_date) SET last_date = STR_TO_DATE( @last_date, '%Y-%m-%d %H:%i:%s')

Here's a sample of the data:

以下是数据示例：

4108,48.74,"2013-09-16 16:15:04"
4249,8.1,"2013-09-16 16:15:04"
4197,3.81,"2013-09-16 17:20:00"
4139,26.81,"2013-09-16 16:15:04"
4218,24.83,"2013-09-16 17:20:00"
4260,79.72,"2013-09-16 16:15:04"
4270,450.12,"2013-09-16 17:20:00"
4242,30.38,"2013-09-16 16:15:04"
4193,1.42,"2013-09-16 16:15:04"
4134,3.77,"2013-09-16 16:15:04"

I am able to import date values using STR_TO_DATE()but I not able to get datetime values to import. I have tried several different date formats other than '%Y-%m-%d %H:%i:%s' and I always get a null datetime [0000-00-00 00:00:00]. I have also tried not using STR_TO_DATE(), since the string is in the default MySQL datetime format.

我可以使用导入日期值，STR_TO_DATE()但无法获取要导入的日期时间值。除了“%Y-%m-%d %H:%i:%s”之外，我尝试了几种不同的日期格式，但我总是得到一个空日期时间 [0000-00-00 00:00:00]。我也试过不使用STR_TO_DATE()，因为字符串是默认的 MySQL 日期时间格式。

Any help will be appreciated.

任何帮助将不胜感激。

回答by Asaph

The date in your data file is already in a format MySQL should natively understand. It's just enclosed in double quotes. You need to tell LOAD DATA INFILEhow to deal with the quotes. Try something like this:

数据文件中的日期已经是 MySQL 应该能够理解的格式。它只是用双引号括起来。你需要告诉LOAD DATA INFILE如何处理报价。尝试这样的事情：

LOAD DATA LOCAL INFILE 'myData.csv'
INTO TABLE equity_last_import
FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ','
LINES TERMINATED BY '\n'
(equity,last,last_date)

Update:

更新：

Since you've said it doesn't work, I created a test table and verified that it does work. Here's the proof:

既然你说它不起作用，我创建了一个测试表并验证它确实有效。这是证据：

I've highlighted your csv data from the question and pasted into a new file called myData.csvin my system's /tmpfolder. Then I connected to the mysql console, switched to the testdatabase and ran the following:

我从问题中突出显示了您的 csv 数据，并将其粘贴到myData.csv我系统/tmp文件夹中名为的新文件中。然后我连接到 mysql 控制台，切换到test数据库并运行以下命令：

mysql> create table equity_last_import (equity int, last decimal(10,2), last_date datetime) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> LOAD DATA LOCAL INFILE '/tmp/myData.csv'
    -> INTO TABLE equity_last_import
    -> FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> (equity,last,last_date);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from equity_last_import;
+--------+--------+---------------------+
| equity | last   | last_date           |
+--------+--------+---------------------+
|   4108 |  48.74 | 2013-09-16 16:15:04 |
|   4249 |   8.10 | 2013-09-16 16:15:04 |
|   4197 |   3.81 | 2013-09-16 17:20:00 |
|   4139 |  26.81 | 2013-09-16 16:15:04 |
|   4218 |  24.83 | 2013-09-16 17:20:00 |
|   4260 |  79.72 | 2013-09-16 16:15:04 |
|   4270 | 450.12 | 2013-09-16 17:20:00 |
|   4242 |  30.38 | 2013-09-16 16:15:04 |
|   4193 |   1.42 | 2013-09-16 16:15:04 |
|   4134 |   3.77 | 2013-09-16 16:15:04 |
+--------+--------+---------------------+
10 rows in set (0.00 sec)

See? It works perfectly.

看？它完美地工作。

Another Update:

另一个更新：

You've specified that you're getting the following error now:

您已指定您现在收到以下错误：

Out of range value for column 'last_date' at row 1

Does your CSV file have a header? If so, you may want to add IGNORE 1 LINESto your LOAD DATA INFILEcommand to tell MySQL to skip over the header.

你的 CSV 文件有标题吗？如果是这样，您可能希望添加IGNORE 1 LINES到您的LOAD DATA INFILE命令中以告诉 MySQL 跳过标题。

回答by Here Iam

I ran into the same problem. I fixed it by changing the format for the date column in my CSV file to match the MySQL datetime format.

我遇到了同样的问题。我通过更改 CSV 文件中日期列的格式以匹配 MySQL 日期时间格式来修复它。

1. Open CSV in Excel.
2. Highlight the column.
3. Right-click on the column.
4. Click on Format Cells.
5. Pick Custom.
6. Use yyyy/mm/dd hh:mm:ssin the Typefield.
7. Click ok

1. 在 Excel 中打开 CSV。
2. 突出显示该列。
3. 右键单击该列。
4. 单击Format Cells。
5. 挑选Custom。
6. yyyy/mm/dd hh:mm:ss在Type现场使用。
7. 点击 ok

My CSV successfully imported after I changed the datetime format as above.

如上所述更改日期时间格式后，我的 CSV 成功导入。

回答by pablonaze

I was having the same trouble and here's what I discovered, I think it might help you

我遇到了同样的问题，这是我发现的，我想它可能对你有帮助

The problem regards a GMT conflict: The database I was extracting the .csv file was on GMT 00:00, so there wasn't daylight saving time.

问题与 GMT 冲突有关：我提取 .csv 文件的数据库位于 GMT 00:00，因此没有夏令时。

My local server (which was the one I was trying to insert the .csv file) was running on my computer (system's) GMT, by default. In my case it was GMT -3, which is affected by daylight saving time.

默认情况下，我的本地服务器（这是我试图插入 .csv 文件的服务器）在我的计算机（系统的）GMT 上运行。就我而言，它是 GMT -3，受夏令时的影响。

SQL has a special way to deal with daylight saving time, it ignores the exact date and time when the daylight saving time starts to happen. In my case, it was october 20 and all the records between 00 and 1 AM of that day simply weren't recognized by the server, even if the format was correct, because they simply 'didn't exist' (see more here). This was affecting all timestamp records, not only the specifics of daylight saving time.

SQL 有一种特殊的方式来处理夏令时，它会忽略夏令时开始发生的确切日期和时间。就我而言，那是 10 月 20 日，服务器无法识别当天上午 00 点到 1 点之间的所有记录，即使格式正确，因为它们根本“不存在”（请参阅此处的更多信息） . 这影响了所有时间戳记录，而不仅仅是夏令时的细节。

My solution was to set the local server to GMT +00, before creating the new table and import the .csv, using

我的解决方案是将本地服务器设置为 GMT +00，然后再创建新表并导入 .csv，使用

SET time_zone='+00:00';

And then when I imported the .csv file all the records were read properly. I think if you set the time zone equal to the one that generated the .csv file should work!

然后当我导入 .csv 文件时，所有记录都被正确读取。我认为，如果您将时区设置为与生成 .csv 文件的时区相同，则应该可以工作！

回答by user1746679

Pulled my hair out over this also because I'm not importing in the above suggested way.

这也是因为我没有以上述建议的方式导入。

Workaround: Created a temporary field temp_date of type "VARCHAR" on your import table and have no problems loading the data. I then was able to perform an update on my date column which is of type date.

解决方法：在您的导入表上创建了一个“VARCHAR”类型的临时字段 temp_date 并且加载数据没有问题。然后，我能够对日期类型的日期列执行更新。

    update table
    set date = temp_date