php - 表字段值和外键 - LOAD DATA INFILE

Question

我目前正在使用 mysql LOAD DATA INFILE 将 csv 值插入到名为 test 的表中。到目前为止一切都很好，直到现在事情变得有点复杂。我有另一个表 occupations，其中包含 occupation_id，我将其用作表 test 中的外键。原始 csv 文件仅包含以下字段 First Name、Last Name、Age、Date Of Birth 和Occupation(参见下面的值示例)。我想根据 csv 文本字段 Occupation 计算 occupation_id。怎么可能？

csv 文件 中的列标题及其各自的值

+------------+-----------+-----+---------------+------------+
| First Name | Last Name | Age | Date of Birth | Occupation |
+------------+-----------+-----+---------------+------------+
| Lionel     | Messi     |  27 | 6/24/1987     | Soccer     |
| Michael    | Jordan    |  51 | 2/17/1963     | Basketball |
| Lebron     | James     |  30 | 12/30/1984    | Actor      |
+------------+-----------+-----+---------------+------------+

表职业

+---------------+-----------------+
| occupation_id | occupation_name |
+---------------+-----------------+
|             1 | Basketball      |
|             2 | Soccer          |
|             3 | Actor           |
+---------------+-----------------+

csv 插入表测试后的结果

+------------+-----------+-----+-------------+---------------+-----------------+
| first_name | last_name | age |    dob      | occupation_id | occupation_name |
+------------+-----------+-----+-------------+---------------+-----------------+
| Lionel     | Messi     |  27 | 1987-06-24  |             2 | Soccer          |
| Michael    | Jordan    |  51 | 1963-02-17  |             1 | Basketball      |
| Lebron     | James     |  30 | 1984-30-12  |             3 | Actor           |
+------------+-----------+-----+-------------+---------------+-----------------+

PHP/SQL - 目前我的查询

$db_insert = $db_con->prepare("LOAD DATA LOCAL INFILE '".$filename."'
    INTO TABLE test FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES
    (@column1, @column2, @column3, @column4, @column5)
    SET first_name=@column1, last_name=@column2, age=@column3, dob = STR_TO_DATE(@column4, '%m/%d/%Y'), occupation=@column5
");
$db_insert->execute();

Answer 1

我不会尝试在 LOAD DATA 语句中执行此操作。理论上，您可以在 LOAD DATA 语句中执行子查询以查找相应的 occupation_id，但即使可以，它也会损害批量加载的性能。

它看起来是这样的，但如果您加载的行数超过微不足道的行数，我预计性能会很糟糕:

LOAD DATA LOCAL INFILE 't.csv'
INTO TABLE test FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(@column1, @column2, @column3, @column4, @column5)
SET first_name=@column1, last_name=@column2, age=@column3,
  dob = STR_TO_DATE(@column4, '%m/%d/%Y'), occupation=@column5, 
  occupation_id=(SELECT occupation_id FROM occupation WHERE occupation_name=@column5 LIMIT 1);

相反，我会执行加载数据并将 occupation_id 留空。然后在 LOAD DATA 完成后，运行 UPDATE 连接到另一个表:

UPDATE test JOIN occupation ON test.occupation = occupation.occupation_name
SET test.occupation_id = occupation.occupation_id;