mysql - 使用 LOAD LOCAL DATA INFILE 时如何获取行 ID？

Question

我有 MySQL 数据库，其中包含我从多个文件插入的表 LOAD DATA LOCAL INFILE ...陈述。我有PRIMARY KEY ID设置为自动增量。问题是，当我只想更新表的一部分时。

假设我已插入 file_1, file_2, file_3过去和现在我只想更新file_2 。我想象伪工作流程中的过程

1. 删除与file_2相关的旧数据
2. 插入来自file_2的新数据

但是，很难确定哪些数据最初来自 file_2 。为了找到答案，我想出了这个想法:

当我插入数据时，我会记下已插入的行的 ID，因为我使用的是 auto_increment，所以我可以记下类似 from_id, to_id 的内容。对于每个文件。然后，当我只想更新file_x时我将仅删除 from_id <= id <= to_id 的数据(其中 from_id, to_id 与 file_x 相关)。

经过一番搜索，我发现了 @@identity和last_insert_id() ( see )，但是，当我使用 select last_insert_id() 时之后LOAD DATA LOCAL INFILE我只得到一个 id，而不是与数据对应的最大 id，而是最后添加的(如定义的)。我正在使用 mysql.connnector 从 Python 连接到数据库使用

cur.execute("select last_insert_id();")
print(cur.fetchall())
# gives
# [(<some_number>,)]

那么，有没有办法检索分配给使用 LOAD DATA LOCAL INFILE... 导入的数据的所有(或至少是最小和最大)ID？如上所述？

Answer 1

如果您需要记住表中每条记录的来源，那么最好将信息存储在字段中。

我将向表中添加一个 TINYINT 类型的新字段 (src)，并存储源的 ID (1 for file_1，2 for file_2 a.s.o.)。我认为来源不会超过 255 个；否则使用 SHORTINT 作为其类型。

然后，当您需要更新从 file_2 导入的记录时，您有两个选择:

1. 删除所有具有src = 2的记录，然后将新记录从文件加载到表中；这不完全是更新，而是替代；
2. 将新记录从文件加载到新表中，然后从中复制更新现有记录所需的值。

选项#1

删除是一件容易的事:

DELETE FROM table_1 WHERE src = 2

加载新数据并将 src 的值设置为 2 也很容易(documentation 中对此进行了解释):

LOAD DATA INFILE 'file.txt'
INTO TABLE table_1
  (column1, column2, column42)    # Put all the columns names here
                                  # in the same order the values appear in the file
SET src = 2                       # Set values for other columns too

如果文件中存在不需要的列，则将它们的值加载到变量中并忽略这些变量。例如，如果文件的第三列不包含有用的信息，您可以使用:

INTO TABLE table_1 (column1, column2, @unused, column42, ...)

单个变量(我将其称为@unused，但它可以具有任何名称)可用于从您想要忽略的所有列加载数据。

选项#2

第二个选项需要创建工作表，但它更灵活。它允许根据通常的 WHERE 条件仅更新某些行。但是，只有当可以使用从文件加载的值(带或不带 src 列)来识别记录时，才可以使用它。

工作表(我们将其命名为 table_w)包含您要从文件加载的列，并且是提前创建的。

当需要更新从 file_2 导入的行时，您可以执行以下操作:

1. 截断工作表(只是为了确保它不包含先前导入的任何剩余内容)；
2. 将文件中的数据加载到工作表中；
3. 连接工作表和table_1并根据需要更新table_1的记录；
4. 截断工作表(清理当前导入)。

代码:

# 1
TRUNCATE table_w;

# 2
LOAD DATA INFILE 'file.txt'
INTO TABLE table_w
    (column_1, column_2, column 42);       # etc

# 3
UPDATE table_1 t
INNER JOIN table_w w
    ON t.column_1 = w.column_1
    # AND t.src = 2                      # only if column_1 is not enough
SET t.column_2  = w.column_2,
    t.column_42 = w.column_42
# WHERE ... you can add extra conditions here, if needed

# 4
TRUNCATE TABLE table_w