在使用 Perl 插入之前，如何检查表中的重复列值? [英] How do I check for duplicate column values in the table before I insert them with Perl?

问题描述

我正在读取一个文件，其中每行都包含一条记录.我正在提取文件的内容并将其作为列值插入到表中.我面临的问题是，假设如果我在读取文件后将记录插入表中，我想删除重复的字段.例如:

I am reading a file which contains a record in each line. I am extracting the contents of the file and inserting it as column values into a table. The problem I face is, suppose if i insert a record into a table after reading from the file, I want to remove the duplicate fields. For example:

NAME age time
Tom   21  10:30
Tom   21  12:21

插入表格后，我希望它是:

After insertion into the table I want it to be:

NAME AGE TIME
tom   21  10:30
          12:21

它应该消除重复项.如果我在创建表时添加一个唯一条件，我会遇到一个问题，即未插入不同的时间字段并导致 MySQL 出错.

It should eliminate the duplicates. If I add a unique condition while creating the table I face a problem that the distinct time field is not being inserted and results in an error in MySQL.

那我该怎么做呢?我想要一些建议.

So how can I do this? I want some suggestions.

for my $test11 (sort keys %seen) {
    my $test1 = $seen{$test11}{'name'};
    my $test2 = $seen{$test11}{'pid'};
    my $test3 = $seen{$test11}{'type'};
    my $test4 = $seen{$test11}{'time1'};
    print "$test11\t$test1$test2$test3$test4\n";
}

#sub query_execute()
{
    $db_handle = &getdb_handle;
    $sth       = $dbh->prepare("INSERT INTO tahle_new values('$sno','$id','$test1','$test4','$test2','$test3')");

$test1 和 $test2 包含重复项，但不包含 $test3.

$test1 and $test2 contain duplicates but not $test3.

推荐答案

另一种方法，是在 2 列上定义唯一键.您的密钥将是(姓名、年龄)并且是唯一的.所以在插入时你会得到一个错误，或者添加到你的请求中:'... ON DUPLICATE KEY...'然后做一些事情(或什么都不做；))

Another way, is to define a unique key on 2 columns. Your key will be (Name, Age) and unique. So on insert you'll get an error, or add to your request : '... ON DUPLICATE KEY...' and do something (or do nothing ;) )

正如 ysth 所说，我建议您不要将带有空值的行作为第二行插入 (null,null, 12:21)

And as ysth said, I would advise you not to insert your line with null values as the second one (null,null, 12:21)

这篇关于在使用 Perl 插入之前，如何检查表中的重复列值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！