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

Question

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

我有 1 个表，我命名为“cityname”，这个表有 2 列:

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

我创建了另一个名为“citymoisture”的表，该表有 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 设置，以匹配“cityname”表中的 city_ID。例如:

city_ID city_name
20      Boston 
19      Atlanta

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

所以我的问题是:

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

2) 在我导入时以任何方式即时转换日期，或者我是否必须先存储数据，然后转换并存储到最终表格。

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

非常感谢您的帮助。

水分数据文件的样本如下所示:

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  
.  
.  
.  

我不确定 citymoisture 表中的 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”的文件中插入数据的城市是波士顿，并且您的城市名称表中存在一个条目:

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');

部分时间不使用 AM PM 部分听起来是个坏主意。