仅将不存在的数据从CSV导入数据库

我创建了一个从CSV文件中读取数据的脚本，检查数据库中是否已经存在数据，如果不存在，则将其导入。如果数据确实存在(特定产品的代码)，则需要从CSV文件更新其余信息。

例如在我的CSV文件中，我有一个代码为WTW-2LT的成员，名叫Alex，姓Johnson。脚本检查代码为WTW-2LT、名为Alex、姓Johnson的成员是否已经存在，如果已经存在，则需要从脚本中更新联系方式和额外详细信息(还需要检查主题和讲师等其他详细信息，所有详细信息都在CSV中的一行中)，如果不存在，则只需创建新成员

我的剧本——到目前为止，我只有最少的其他检查，以防止分心；

while ($row = fgetcsv($fp, null, ";")) {
if ($header === null) {
$header = $row;
continue;
}
$record = array_combine($header, $row);
$member = $this->em->getRepository(Member::class)->findOneBy([
'code' =>$record['member_code'],
'name' =>$record['name'],
'surname' =>$record['surname'],
]);
if (!$member) {
$member = new Member();
$member->setCode($record['member_code']);
$member->setName($record['name']);
$member->setName($record['surname']);
}    
$member->setContactNumber($record['phone']);
$member->setAddress($record['address']);
$member->setEmail($record['email']);
$subject = $this->em->getRepository(Subject::class)->findOneBy([
'subject_code' => $record['subj_code']
]);
if (!$subject) {
$subject = new Subject();
$subject->setCode($record['subj_code']);
}
$subject->setTitle($record['subj_title']);
$subject->setDescription($record['subj_desc']);
$subject->setLocation($record['subj_loc']);
$lecturer = $this->em->getRepository(Lecturer::class)->findOneBy([
'subject' => $subject,
'name' => $record['lec_name'],
'code' => $record['lec_code'],
]);
if (!$lecturer) {
$lecturer = new Lecturer();
$lecturer->setSubject($subject);
$lecturer->setName($record['lec_name']);
$lecturer->setCode($record['lec_code']);
}
$lecturer->setEmail($record['lec_email']);
$lecturer->setContactNumber($record['lec_phone']);
$member->setLecturer($lecturer);
$validationErrors = $this->validator->validate($member);
if (!count($validationErrors)) {
$this->em->persist($member);
$this->em->flush();
} else {
// ...
}
}

您可以注意到，此脚本必须查询数据库3次才能检查是否存在一个CSV行。在我的情况下，我的文件最多有2000多行，所以每行执行3次查询以检查该行是否存在是非常耗时的。

不幸的是，我也无法批量导入行，因为如果一个主题不存在，它会多次创建它，直到批处理被刷新到数据库，然后我处理重复的记录，这些记录毫无意义。

如何最大限度地提高性能和速度？比如首先从数据库中获取所有记录并将其存储在数组中(占用内存？)，然后进行检查并将行添加到数组中，然后从那里进行检查。。。

有人能帮我找到一种改进的方法吗(请提供样本代码？)

老实说，我确实发现2000多行的查询量是原来的3倍，但并没有那么多。但是，既然你要求的是性能，这是我的两分钱：

使用框架总是会带来开销。这意味着，如果你用原生PHP编写这些代码，它就会运行得更快。我不熟悉symfony，但我认为您将数据存储在数据库中。在MySQL中，您可以使用命令INSERT ... ON DUPLICATE KEY update。如果您已经将这3个字段(code、name、lastname)设置为主键(我假设是这样)，您可以使用它来：插入数据，但如果该键已经存在，则更新数据库中的值。MySQL将为您进行检查，看看数据是否发生了更改：如果没有，则不会发生磁盘写入。

我很确定，您可以将本机SQL编写到symfony，允许您使用框架提供的安全性，同时加快插入速度。

一般来说，如果您想要性能，我的最佳经验是将所有数据转储到数据库中，然后使用SQL语句在其中进行转换。DBS将能够通过这种方式优化您的所有步骤。

您可以使用SQL命令将CSV文件直接导入MySQL数据库

LOAD DATA INFILE 'data.csv'
INTO TABLE tmp_import

该命令有很多选项，您可以在其中指定CSV文件的格式，例如：

MySQL在LOAD DATA INFILE上的引用
https://stackoverflow.com/a/18941427/1220835

如果您的data.csv是一个包含所有新行和新行的完整转储，那么您可以在修复一点后，用导入的表替换当前表。

例如，它看起来像你的csv文件(和导入表)可能看起来有点像

WTW-2LT, Alex, Johnson, subj_code1, ..., lec_name1, ...
WTW-2LT, Alex, Johnson, subj_code1, ..., lec_name2, ...
WTW-2LT, Alex, Johnson, subj_code2, ..., lec_name3, ...
WTW-2LU, John, Doe,     subj_code3, ..., lec_name4, ...

然后，您可以通过分组获得不同的行：

SELECT member_code, name, surname
FROM tmp_import
GROUP BY member_code, name, surname

如果member_code是一个密钥，那么您可以在MySQL中只使用GROUP BY member_code。DBS不会抱怨，尽管我认为它在技术上违反了标准。

要获得其余的数据，您也可以这样做：

SELECT subj_code, subj_title, member_code
FROM tmp_import
GROUP BY subj_code

和

SELECT lec_code, lec_name, subj_code
FROM tmp_import
GROUP BY lec_code

假设CCD_ 4和CCD_。

要将此结果实际保存为表，可以使用MySQL的CREATE TABLE ... SELECT-语法，例如

CREATE TABLE tmp_import_members
SELECT member_code, name, surname
FROM tmp_import
GROUP BY member_code, name, surname

然后，您可以在两个查询中进行插入和更新：

INSERT INTO members (member_code, name, surname)
SELECT member_code, name, surname
FROM tmp_import_members
WHERE tmp_import_members.member_code NOT IN (
SELECT member_code FROM members WHERE member_code IS NOT NULL
);
UPDATE members 
JOIN tmp_import_members ON 
members.member_code = tmp_import_members.members_code
SET 
members.name = tmp_import_members.name,
members.surname = tmp_import_members.surname;

你喜欢的科目和讲座也是如此。

这一切都相当于

批量导入您的CSV文件，这应该非常快
为您的会员、主题和讲座提供3张临时桌子
3个插入语句和3个更新语句(每个表一个)
完成后，将表格放在临时表格上

再次：如果CSV文件包含所有行，您可以替换现有表并保存3个插入和3个更新。

确保在临时表的相关列上创建索引，以便MySQL可以加快上述查询中的NOT IN和JOIN。

您可以先运行一个自定义sql，从的所有三个表中获取计数

SELECT
(SELECT COUNT(*) FROM member WHERE someCondition) as memberCount, 
(SELECT COUNT(*) FROM subject WHERE someCondition) as subjectCount,
(SELECT COUNT(*) FROM lecturer WHERE someCondition) as lecturerCount

然后，根据计数，您可以发现表中是否存在数据。如果使用本机SQL ，则不必多次运行查询以获得唯一性

查看此链接了解如何在Doctrine 中运行自定义SQL

Symfony2&原则：创建自定义SQL查询

相关内容

最新更新

热门标签：