mysql - perl - 在数据库中发送多个请求

Question

我在 perl 中有这个脚本，这个程序解析一个日志文件并将结果发送到数据库中，我的问题是我的脚本只插入一个请求，我需要插入多个请求：

#Connect to the database.
my $dbh = DBI->connect("DBI:mysql:database=database;host=IP",
"hostname", 'password',
{'RaiseError' => 1});



while (my ($user, $ref) = each %counts) {
  while (my ($program, $count) = each %$ref) {
    #print "$count OSUSER with session $user and with program $program\n";
    print "time = $time, count = $count, user = $user, program = $program, last_line = $last_line\n";

    $request ="'$time', '$count', '$user', '$program', $last_line";

    my $sth = $dbh->prepare("REPLACE `test` (time, nb, os_name, program, last_line) VALUES($request);")
    or die "prepare statement failed: $dbh->errstr()";

    $sth->execute() or die "execution failed: $dbh->errstr()";
    print $sth->rows . " rows found.\n";
    $sth->finish;
  }
}

我的日志：

       ID USER                    TERMINAL        SERVICE                    
---------- ------------------------- --------------- -------------------------  
         1 toto                    titi     roro          
         2 toto                    titi     roro          
         4 gigi                    gege        fefe      

我的数据库：

+----+---------------------+-----------+-------------+----------------+-----------+
| ID | time                | nb        | os_name     | program        | last_line |
+----+---------------------+-----------+-------------+----------------+-----------+
| 15  | 2019-01-04 14:00:00|        33 | titi        | roro           | 109       |

我想要 ：

+----+---------------------+-----------+-------------+----------------+-----------+
| ID | time                | nb        | os_name     | program        | last_line |
+----+---------------------+-----------+-------------+----------------+-----------+
| 15  | 2019-01-04 14:00:00|        33 | titi        | roro           | 109       |
| 16  | 2019-01-04 14:00:00|        9  | gege        | fefe           | 109       |      

（由 Dave Cross 添加 - 从评论中复制。这是表定义。）

CREATE TABLE test (
  ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
  time datetime NOT NULL,
  nb int NOT NULL,
  os_name nvarchar(100) NOT NULL,
  program nvarchar(100) NOT NULL,
  last_line nvarchar(100)NOT NULL,
  PRIMARY KEY (ID),
  UNIQUE KEY (time)
) ENGINE=InnoDB;

标签： mysqlsqlperlmariadb

Answer 1

Your table definition (from a comment) includes this:

UNIQUE KEY (time)

So your table can only ever include a single row for any value of time.

Also, you use REPLACE in your SQL. So when you run your statement for a second time, your database sees there is an existing row with the same unique key, so it updates the row rather than inserting a new one.

Seems to me that your code is working exactly as expected. If you want to add more rows, you need to change the value in $time.

Update:

From your comment on another answer.

i tested with INSERT but don't work : DBD::mysql::st execute failed: Duplicate entry '2019-01-04 17:45:00' for key 'time'

Yes! That's exactly what should happen. By declaring you time column as UNIQUE you are telling your database that each time value can only occur once in your table. So if you try to insert another row with the same time (which is what you are doing) you get that error.

So you switch from INSERT to REPLACE and you get no error, but only one row inserted - because that is what REPLACE does.

The example you give of what you want in the table has two identical values in the time column. That cannot happen while you have the UNIQUE definition on that column. That is exactly what the UNIQUE key is there to prevent.

You need to step back and think about exactly what you want in the table. If you want duplicate time values in the table then you need to remove the UNIQUE key definition on the table (and then change REPLACE with INSERT).