mysql - 有选择地从 CSV 读取到 MySQL

Question

这是一个由两部分组成的问题。

• 首先是我应该使用什么架构来解决以下问题？
• 第二个是如何使用，即我应该使用哪些命令？

我有一些日志文件想要读入数据库。日志文件包含不必要的字段(因为它们可以从其他字段计算)。

方法 1:我应该解析日志文件的每一行并将其插入数据库吗？
缺点:日志条目必须是唯一的，因此我需要首先执行 SELECT，检查 LogItemID 是否存在，如果不存在则插入。这似乎是一项高开销事件，在某些时候这将每小时完成一次。

方法 2: 或者我是否使用 LOAD DATA INFILE(我什至可以在 PHP 中使用它？)并将日志文件加载到临时表中，然后将记录移动到永久表中？
缺点:即使在这种方法中，我仍然必须经历 SELECT、然后 INSERT 的循环。

方法3:或者有更好的方法吗？是否有命令可以将选定字段的记录从一个表批量复制到另一个表？只要 LogItemID 设置为 UNIQUE ，就会 REPLACE INTO .... ON DUPLICATE UPDATE 工作(如果该项目存在，我不想更新，只需忽略)？不管怎样，我需要扔掉无关的字段。这些方法中哪一种更好？不仅更容易，而且从编写优秀的、可扩展的代码的角度来看？

附注不相关，但这里的架构问题的一部分是这样的......如果我有 StartTime、EndTime 和 Interval(EndTime-StartTime)，我应该保留哪一个 - 前两个还是 Interval？为什么？

编辑:为了澄清为什么我不想存储所有三个字段 - 问题当然是标准化，因此不是好的做法。出于审计原因，也许我会存储它们。也许在另一张 table 上？TIA

Answer 1

LOAD DATA INFILE 将比运行单独的插入快得多。

您可以加载到一个单独的临时表，然后从临时表运行 INSERT ... SELECT 到您的实际存储中。但尚不清楚为什么需要这样做。要“跳过”CSV 中的某些字段，只需将这些字段分配给用户定义的虚拟变量即可。无需将这些字段加载到临时表中。

我定义一个UNIQUE键(约束)并只使用INSERT IGNORE；这将比运行单独的 SELECT 快得多，并且比 REPLACE 快得多。 (如果您的要求是不需要更新现有行，您只想“忽略”新行。

LOAD DATA INFILE 'my.csv'
IGNORE
INTO TABLE mytable
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
( mycol
, @dummy2
, @dummy3
, @mm_dd_yyyy
, somecol 
)
SET mydatecol = STR_TO_DATE(@mm_dd_yyyy,'%m-%d-%Y')

如果您有开始、结束和持续时间，请继续存储所有三个。那里存在冗余，主要问题是性能和更新异常。 (如果您更新end，是否也应该更新duration？)如果我不需要进行更新，我只需存储所有三个。我可以根据 start_time 和 end_time 计算 duration，但是存储列将允许我添加索引，并在查询时获得更好的性能持续时间少于 10 分钟或其他。如果没有该列，我将被迫计算表中每一行的表达式，这对于大型集合来说会变得昂贵。