Привязка параметров в Perl dbi приводит к сбою ограничения внешнего ключа

У меня есть идея, что я хочу создать подпрограмму, которая может принимать запрос на выборку, а затем копировать результаты в ту же таблицу, но с некоторыми важными изменениями. Моя проблема в том, что некоторые из таблиц, в которых я запускаю свою подпрограмму, имеют большие текстовые поля, содержащие много разных символов, некоторые из которых сломают мой оператор вставки. Затем я изменил свою вставку, чтобы использовать привязку параметров, но когда я сделал это, мой запрос не будет выполняться из-за некоторого ограничения внешнего ключа в моем поле «профиль»:

DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (retriever.result, ОГРАНИЧЕНИЕ result_ibfk_2 FOREIGN KEY (profile) ССЫЛКИ profile (id) ON DELETE CASCADE ON UPDATE CASCADE) в ./create_query.pl строка 62. Невозможно добавить или обновить дочернюю строку: ограничение внешнего ключа не работает (retriever.result, ОГРАНИЧЕНИЕ result_ibfk_2 ВНЕШНИЙ КЛЮЧ (profile) ССЫЛКИ profile (id) НА КАСКАД УДАЛЕНИЯ НА КАСКАД ОБНОВЛЕНИЯ)

Немного информации о таблице:

CREATE TABLE `result` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `profile` mediumint(8) unsigned NOT NULL DEFAULT '0',
    CONSTRAINT `result_ibfk_2` FOREIGN KEY (`profile`) REFERENCES `profile` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB AUTO_INCREMENT=1037028383 DEFAULT CHARSET=utf8

Вот мой код:


#Define which rows to change
my $rows_to_change = {
    'profile'               => 621420,
};

#Define a select query, the results will then be copyed over
my $sql = "select * from result where profile = 639253";

#Define which table to work with
my $table = "result";
my @inserted_ids = create_insert_query($sql, $table, $rows_to_change);

for my $id (@inserted_ids){
    print $id."\n";
}

$dbh->rollback();
$dbh->disconnect();

sub create_insert_query{
    my $select          = shift;
    my $table           = shift;
    my $rows_to_change  = shift;    

    my $result = $dbh->prepare($select);
    $result->execute() or die $dbh->errstr;

    my @inserted_ids;
    while(my $row = $result->fetchrow_hashref){
        delete $row->{'id'};
        foreach my $key (keys %{$rows_to_change}){
            $row->{$key} = $rows_to_change->{$key};
        }
        my @fields;
        my @values;
        for my $key (keys %{$row}){
            push(@fields, $key);

            if(defined $row->{$key}){
                push(@values, "'$row->{$key}'");
            }else{
                push(@values, "null");
            }
        }
        my $fields_string = join(", ", @fields);
        my $values_string = join(", ", @values);
        my $questionmarks = join( ',', map { '?' } @values );
        my $query = qq[insert into $table ($fields_string) values ($questionmarks)];
        my $sth = $dbh->prepare($query);
        $sth->execute(@values) or die $dbh->errstr;

        push(@inserted_ids, $sth->{mysql_insertid});
    }
    return @inserted_ids;
}

mysql dbi perl parameterbinding
person Twistar    schedule 01.11.2013    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Что делать

Вместо этого вы хотите что-то вроде:

$fieldnames   = join ', ' => map { $dbh->quote_identifier($_) } keys %row;
$placeholders = join ', ' => map { '?' } values %row;
...
$sth = $dbh->prepare('INSERT INTO t ($fieldnames) VALUES ($placeholders)');
$sth->execute(values %row);

(Вызов quote_identifier предназначен для безопасности, если имена ваших столбцов конфликтуют с ключевые слова или требуют специальной кодировки или цитирования.)

Почему?

Ваша мгновенная проблема заключается в том, что вы заключаете в кавычки значения, которые затем привязываете к заполнителям (что связывает значение с кавычками), и что вы привязываете строку «null» к заполнителям (что связывает string< /em> "ноль"). Это нарушает какое-то ограничение внешнего ключа, поскольку ваш FKEY, вероятно, не является буквальной строкой «null» и не является строкой, представляющей цифры в кавычках (например, это, вероятно, число 123, а не строка со встроенными кавычками «'123'").

Для справки, строки привязки, строки, содержащие кавычки, числовые литералы и undef в DBI обычно работают следующим образом:

 my $sth = $dbh->prepare('INSERT INTO t (a,b,c,d,e) VALUES (?,?,?,?,?)');
 $sth->execute("'quoted'", 'null', undef, 123, "a string");
 # Approximately the same as:
 #  INSERT INTO t (a,b,c,d,e) VALUES ('''quoted''', 'null', NULL, 123, 'a string');
 #                                        ^           ^      ^     ^     ^
 #               a string with quotes ----+           |      |     |     |
 #                                                    |      |     |     |
 #               a string (not the NULL value) -------+      |     |     |
 #                                                           |     |     |
 #               NULL (not the string 'null')----------------+     |     |
 #                                                                 |     |
 #               numeric literal ----------------------------------+     |
 #                                                                       |
 #               another string -----------------------------------------+
person pilcrow    schedule 01.11.2013
comment
quote_identifier помогает не только в кодировании — он позволяет использовать столбцы с именами, например. values - person ysth; 01.11.2013
comment
Большое спасибо, это гораздо лучшее решение! - person Twistar; 04.11.2013