mysql - 如何使用 Perl 根据 id 使用不同的值更新每个数据库记录？

Question

我有一个散列，其中包含所有计算用户的考试成绩(系统的用户在我的例子中是学生)，其中 user_id 是散列的键，成绩是对应的值.这个散列有超过 50,000 个元素。我想用这些计算出的成绩更新数据库。

我的第一次尝试

my %grade_of = compute_grades_hash( ... );   # Some logic to compute grades
my $dbh = DBI->connect( ... ) or die( ... ); # DBI parameters are hidden in this sample
my $sql = "UPDATE User SET grade = ? WHERE id = ?";
my $sth = $dbh->prepare($sql);
foreach my $user_id ( keys %grade_of ) {
    $sth->execute( $grade_of{$user_id}, $user_id );
}

但是，如果我是对的，这将导致执行超过 50,000 次查询，这意味着超过 50,000 次访问数据库(如果我假设的内容有误，请随时纠正我。)

所以我写了这个第二次尝试

my %grade_of = compute_grades_hash( ... );   # Some logic to compute grades
my $dbh = DBI->connect( ... ) or die( ... ); # DBI parameters are hidden in this sample
my $sql = build_query ( \%grade_of );
my $sth = $dbh->prepare($sql);
$sth->execute();

sub build_query {
    # Builds a string in the followig form:
    #     UPDATE User
    #     SET grade = (case when user_id = 10 then 96
    #                       when user_id = 14 then 92
    #                       when user_id = 26 then 85
    #                       ... 
    #                  end)
    # So it generates a very long query-string for the 50000 records

    ...
    return $sql_query;
}

这是我的问题:

1. 对于这个案例，这是正确的解决方案吗？

2. 超长的SQL语句会影响查询性能吗？

3. Perl DBI 和/或 MySQL 中的查询长度是否有任何限制？

4. 有没有更优雅的解决方案？我是否缺少内置功能例如，我可以使用哪个？

顺便说一句，以防万一答案取决于以下信息，这里是我的系统规范的完整性:

$ mysql --version 
mysql  Ver 14.14 Distrib 5.6.25, for Linux (x86_64)

$ perldoc -m DBI | grep -m1 VERSION 
$VERSION = "1.621";

$ perl --version 
This is perl 5, version 18, subversion 2 (v5.18.2) built for x86_64-linux

Answer 1

您是否尝试过运行其中任何一个？

您的第一次尝试是正确的。这就是数据库的用途。您的第二个版本可能要慢得多。取决于 SQL 引擎的优化方式，但无论您如何制定 SQL，您的数据库仍然必须执行 50,000 次更新。您的第二次尝试迫使它做更多的工作来决定这些更新是什么

通过使用交易

，您可以加快流程，避免某些记录已更新而其他记录未更新的可能性

在开始更新数据库之前，调用

$dbh->begin_work

当它们都完成后，你需要

$dbh->commit

这将创建一个要对表进行更改的列表，并最终在您调用 commit 方法时进行这些更改

无论如何，您都应该使用测试数据库，因此我建议您设置一个包含可管理的数据量的数据库，这样可以相当快地给出结果。然后您可以尝试您的两个选项，以及您想到的任何其他选项，并确定它们的行为方式