mySQL: mysqlimport导入逗号分隔的文件-第一列= ID，它不在要导入的文件中

Question

大家好，我正在尝试导入一个非常大的文件，其中有美国20个城市每天每分钟记录的湿度数据。

我有一个表，我将其命名为"cityname“，这个表有两列：

-city_ID <- INT and is the primary key which increments automatically
-city_name <- character

我已经创建了另一个名为“created”的表，该表有7列：

-city_ID <- INT and is the primary key but does NOT increment automatically
-date_holding VARCHAR(30)
-time_holding VARCHAR(30)
-open
-high
-low
-close

date_holding是用来存放日期数据的，但是因为它的格式不是mysql所期望的(即m/d/y)，所以我想先把它存储在这个列中，然后再进行转换(除非有办法在导入数据时转换它？)。类似地，time_holding列保存的时间显示为hh:mm:ss AM (或PM)。我只想导入hh:mm:ss，忽略它是AM还是(PM)。

在任何情况下，我要导入的文件都有六列：

日期、时间、打开、高、低、关闭。

我想确保正在导入的数据设置了与‘city_ID’表中的city_ID相匹配的正确的cityname。举个例子：

city_ID city_name
20      Boston 
19      Atlanta

因此，当波士顿的湿度数据被导入到city_ID表中时，city_ID列被设置为20。类似地，当亚特兰大的数据被导入到city_ID表中时，city_ID列被设置为19。city_ID表将非常大，并且将存储20个城市的1分钟湿度数据。

所以我的问题是：

1)有没有办法将文件内容导入到第2-7列，并手动指定第一列的值(city_ID)？

2)当我导入或者我必须首先存储数据，然后转换并存储到最终的表时，是否有任何方法可以动态地转换日期。

3)与#2相同的问题，但针对时间列。

我非常感谢你的帮助。

湿度数据文件的THe样本显示如下：

1/4/1999,9:31:00 AM,0.36,0.43,0.23,0.39  
1/4/1999,9:32:00 AM,0.39,0.49,0.39,0.43  
.  
.  
.  

我不确定城市水分表中的city_ID将如何设置。但是，如果有办法做到这一点，我就可以基于这两个表运行连接查询，即每个城市每个日期/时间都有一条记录。

Answer 1

STR_TO_DATE应该可以用来获取日期和时间

mysql> SELECT STR_TO_DATE('01/01/2001', '%m/%d/%Y');
+---------------------------------------+
| STR_TO_DATE('01/01/2001', '%m/%d/%Y') |
+---------------------------------------+
| 2001-01-01                            |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT STR_TO_DATE('10:53:11 AM','%h:%i:%s %p');                                                        
+------------------------------------------+
| STR_TO_DATE('10:53:11 AM','%h:%i:%s %p') |
+------------------------------------------+
| 10:53:11                                 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> 

您将如何“手动”确定每行数据所属的城市，您是否可以包含导入数据文件的样例行？假设你有city_ID，(在下面的代码中替换)：

看起来你会想要用到这个：LOAD DATA INFILE

如果您想要插入数据的城市是来自名为'Boston.dat‘的文件的波士顿，并且您的cityname表中存在一个条目：

SET @c_name = 'Boston';
SET @filename = CONCAT(@c_name,'.dat');

LOAD DATA INFILE     @filename
INTO TABLE           city_moisture
(@date, @time, open, high, low, close)
SET                  city_ID=(SELECT city_ID FROM TABLE cityname WHERE city_name=@c_name),
                     date=STR_TO_DATE(@date, '%m/%d/%Y'),
                     time=STR_TO_DATE(@time, '%H:%i:%s %p');

把上午和下午的时间分开，听起来不是个好主意。