php - 保持 MySQL 数据库与外部源 (.csv) 同步，同时维护以前的数据

Question

每天(假设是下午 3 点)我都会收到一个 .csv 文件上传到我的 FTP 服务器的信息。

例如，我们以此为例

population.csv

city,population
New York,8008278
Los Angeles,3694825
San Diego,1223405

我已将 CSV 文件转换为数组，并使用 INSERT INTO 将行插入到我的表 Populations

╔════╦═════════════╦════════════╦════════╗
║ id ║    city     ║ population ║ status ║
╠════╬═════════════╬════════════╬════════╣
║  1 ║ New York    ║    8008278 ║      1 ║
║  2 ║ Los Angeles ║    3694825 ║      1 ║
║  3 ║ San Diego   ║    1223405 ║      1 ║
╚════╩═════════════╩════════════╩════════╝

id 是一个 AUTO INCREMENT 索引，status 是一个 TINYINT(1)，我将其值设置为 1 如果它位于当天上传的 CSV 中。

假设明天下午 3 点此 CSV 文件上传到我的服务器:

city,population
Los Angeles,3694825
San Diego,1229502

如您所见，New York 行未包含在内，San Diego 的 population 已发生变化。

我希望实现的结果是每天使用 CSV 文件中的值更新行。如果某行不包含以前的行(例如 New York)，我想将状态设置为 0。

因此第二天，根据 CSV 文件的内容，我希望我的数据库能够解析为以下内容:

╔════╦═════════════╦════════════╦════════╗
║ id ║    city     ║ population ║ status ║
╠════╬═════════════╬════════════╬════════╣
║  1 ║ New York    ║    8008278 ║      0 ║
║  2 ║ Los Angeles ║    3694825 ║      1 ║
║  3 ║ San Diego   ║    1229502 ║      1 ║
╚════╩═════════════╩════════════╩════════╝

到目前为止，我正在使用 PHP 将上传的 CSV 文件解析为数组，另外还使用 SELECT 查询将数据库解析为数组。

在 PHP 中使用条件语句，我正在比较两个数组的任何差异(行值更改、新行、删除的行)。

这是我第一次尝试使外部源与状态为 1 的数据库内容完全同步。

是否有更有效的方法在 MySQL 本身中执行此操作，而不依赖于其他编程语言来构建我的语句？

我想到的一些想法:

• 为每一行添加一个 LastUpdated 时间戳，如果该时间戳超过 24 小时，则创建一个 TRIGGER 将状态设置为 0

  <

• 创建审核表以引用前一天的更改。 (删除的行、插入的行等)。引用后，重新插入所有唯一/索引值 (id)，并对今天 CSV 文件中未包含的行表示状态 0

或者我当前使用 PHP 将 CSV 与表中当前记录进行比较的方法是解决此问题的最佳方法吗？如前所述，我对数据库有一些经验，但从未尝试过做这样的事情。我应该朝哪个方向看？我是否遗漏了一些明显的东西？

Answer 1

我建议首先使用 LOAD DATA INFILE 语法将新文件插入到临时表中。下面给出了一个示例，您可能需要根据您的用例进行修改(de this tutorial for example )；

LOAD DATA INFILE 'c:/tmp/populations.csv' 
INTO TABLE Temp 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

然后，可以使用以下查询来更新主表中的现有行:

UPDATE Populations p
LEFT JOIN Temp t ON p.city = t.city
SET 
    p.population = COALESCE(t.population, p.population),
    p.status = CASE WHEN p.city IS NULL THEN 1 ELSE 0 END

此查询将插入尚不存在的行:

INSERT INTO Populations
SELECT p.name, p.population, 1
FROM Temp t
WHERE NOT EXISTS (
    SELECT 1 FROM Populations WHERE name = t.name)