[求助]如何把数组批量存入数据库

weininglzu

求教：如何把数组内容批量存入sql特定表的某个字段
例如把@sum=qw/James Benney Rose Chandler Monica Joe/ 的内容存入到mysql的Film表的names字段中？
要用什么特殊的插件么？

问题解决的体会：
参考了yizhengming和7looki同鞋的代码之后，我又看了看书，把我的代码贴上上来，请大家批评交流。

1. #!/usr/bin/perl -w
   
2. use DBI;
   
3. use strict;
   
4. 
   
5. my $insert;
   
6. my @sum=qw/James Benney Rose Chandler Monica Joe/ ;
   
7. my $dbh=DBI->connect('DBI:mysql:test','user','password') or die "can not connect";
   
8. foreach(@sum){
   
9.                 $insert=$dbh->prepare("INSERT INTO test (feild_id) values('$_')");
   
10.                 $insert->execute();
    
11.                         }
    
12. $insert->finish();
    
13. $dbh->disconnect();
    

复制代码

kk861123

1. DBI

复制代码

weininglzu

我看了DBI的--help，但是没有找到相关的内容啊，
还是写不出啦。。。
麻烦您给个提示啥~

kk861123 发表于 2012-10-09 20:33

yizhengming

1. #! /usr/bin/perl -w
   
2. use strict;
   
3. use DBI;
   
4. my $platform = "mysql";
   
5. my $database = "your_database";
   
6. my $host = "localhost";
   
7. my $port = "3306";
   
8. my $tablename = "Film";
   
9. my $user = "your_user";
   
10. my $pw = "your_password";
    
11. my $dsn = "dbi:mysql:$database:localhost:3306";
    
12. my $dbh = DBI->connect($dsn, $user, $pw);
    
13. if (!$dbh) {
    
14.     print "$DBI::errstr\n";
    
15. }
    
16. my $insert = "insert into File (names) values (?)";
    
17. my $sth = $dbh->prepare($insert);
    
18. foreach my $name (@sum) {
    
19.     $sth->execute($name);
    
20. }
    
21. $sth->finish()

复制代码

7looki

回复 1# weininglzu

1. #!/usr/bin/perl
   
2. 
   
3. use strict;
   
4. use warnings;
   
5. use DBI;
   
6. 
   
7. my $db_name = "test";
   
8. my $db_host = "192.168.1.200";
   
9. my $db_port = "3306";
   
10. my $db_user = "test";
    
11. my $db_pass = "passwd123";
    
12. 
    
13. my $database = "DBI:mysql:$db_name:$db_host:$db_port";
    
14. 
    
15. my $dbh = DBI->connect( $database,$db_user,$db_pass );
    
16. 
    
17. my @create = (
    
18. 
    
19. "DROP TABLE IF EXISTS `Film`;",
    
20. 
    
21. "CREATE TABLE `Film` (
    
22.     `names` varchar(16) DEFAULT NULL,
    
23.     PRIMARY KEY (`names`)
    
24. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
    
25. 
    
26. );
    
27. 
    
28. my $create = $dbh->do( $_ ) foreach @create;
    
29. 
    
30. my @sum = qw/James Benney Rose Chandler Monica Joe/;
    
31. 
    
32. foreach my $name ( @sum ) {
    
33.     my $sql = "insert into Film ( names ) values ( '$name' );";
    
34.     my $instert = $dbh->do( $sql );
    
35. }
    
36. 
    
37. my $sth = $dbh->prepare( "select names from Film;" );
    
38. $sth->execute() or die "can not execute SQL:$dbh->errstr";
    
39. 
    
40. print "name\n";
    
41. print "-" x 10,"\n";
    
42. 
    
43. while(my $ref = $sth->fetchrow_hashref()) {
    
44.     print "$ref->{'names'}\n";
    
45. }
    
46. 
    
47. $sth->finish;
    
48. $dbh->disconnect;

复制代码

执行结果:

1. [root@pink mysql]# perl test.pl
   
2. name
   
3. ----------
   
4. Benney
   
5. Chandler
   
6. James
   
7. Joe
   
8. Monica
   
9. Rose

复制代码

Perl菜鸟一个,写的比较丑,勿笑.

laputa73

还以为是讨论批量插入的性能。

weininglzu

谢谢，谢谢
回复 4# yizhengming


   

weininglzu

哈哈，我也是菜鸟，所以更容易看懂，谢谢啦！！
回复 5# 7looki