mysql - 使用 Perl 程序在 MySQL 的列中插入多行

Question

我想使用单个查询在单个列中插入多行数据。该程序适用于较少的数据。我有另一个天气监测 .txt 文件，其中有 4000 行数据。我一次可以插入一个数据，但对于如此多的数据值来说会变得乏味。

1.     use DBI;
2.     use DBD::mysql;
3.     use warnings;


4.     $connection = ConnectToMySql($database);

5.   # Multiple Data inputs
6.      $myquery = "INSERT INTO data(datatime,battery)
7.           VALUES
8.             (?,?),
9.             ('16.01.2013','6.54'), #data corresponding to date and battery
10.             ('17.01.2013','6.42'),
11.             ('21.01.2013','6.24'),
12.             ('22.01.2013','6.21'),
13.             ('24.01.2013','6.17'),
14.             ('25.01.2013','6.13'),
15.             ('28.01.2013','6.00'),
16.             ('29.01.2013','5.97'),
17.             ('30.01.2013','5.94'),
18.             ('01.02.2013','5.84')";
19.    $statement2 = $connection->prepare($myquery);

20.     $statement2->execute($myquery);

21.    #--- start sub-routine
22.    sub ConnectToMySql {
23.       $database ="xxxx";
24.       $user = "XXXX";
25.       $pass = "XXXX";
26.       $host="XXXX";
27.    my $dbh = DBI->connect("DBI:mysql:$database:$host", $user, $pass);
28.    }

这段代码给我以下错误:

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2 at C:/Users/User/workspace/DataBaseEntry/DataEntry.pl line 20.
DBD::mysql::st execute failed: called with 1 bind variables when 2 are needed at C:/Users/User/workspace/DataBaseEntry/DataEntry.pl line 40.

我无法确定问题所在。是不是占位符。我能做些什么来改善它？我对这些东西很陌生。所以你能不能保持简单。谢谢

Answer 1

您应该将替换 (?, ?) 的数据值作为参数传递给 execute。您编写的代码仅将一个参数传递给 execute，该参数是您查询的 SQL 文本。

试试这个:

$myquery = "INSERT INTO data(datatime,battery) VALUES (?,?)";
my $sth = $connection->prepare($myquery);

$sth->execute('16.01.2013','6.54');
$sth->execute('17.01.2013','6.42');
$sth->execute('21.01.2013','6.24');
$sth->execute('22.01.2013','6.21');
$sth->execute('24.01.2013','6.17');
$sth->execute('25.01.2013','6.13');
$sth->execute('28.01.2013','6.00');
$sth->execute('29.01.2013','5.97');
$sth->execute('30.01.2013','5.94');
$sth->execute('01.02.2013','5.84');