How can I create a unique value in Perl for each record I fetch from MySQL?

Question

How can I produce a unique value key to be paired with my _rec_key_ field name? I am producing a datafile. I was looking at using an MD5 value as my key or any other suggestions you have to making sure this is unique. I'm not familiar with how to extract this value.

The format of the file should look something like this:

__rec_key__^amd5val^ex_id^a1^einum^a2609^euser^aJoe^e^d
__rec_key__^amd5val^ex_id^a2^einum^a2609^euser^aBob^e^d

Basically, enclosing the value with ^a and ^e and ending the rec with ^d

My sample data table:

+------+------+------+
| x_id | inum | user |
+------+------+------+
|    1 | 2608 | Joe  |
|    2 | 2609 | Bob  |
+------+------+------+

My code thus far is this...which just produces my output, without the md5 unique value. I would need the value to be paired with _rec_key_

my $data = '';
my $dbh = DBI->connect("DBI:mysql:test:localhost:3306");
my $sth = $dbh->prepare("select x_id, inum, user from mytest");
$sth->execute();
while (my($x_id, $inum, $user) = $sth->fetchrow_array() ) {
$data = $data. "__record_key__^a$x_id^e^a$inum^e^a$user^e^d";
}
$sth->finish;
$dbh->disconnect;
print $data;

Answer 1

It's difficult to understand what you're trying to do, so I can't say if using an md5 here is appropriate, but you can create them with the Digest::MD5 distribution from CPAN:

use Digest::MD5 qw(md5_hex);

my $data = 'some arbitrary data';
my $digest = md5_hex($data);
print $digest, "\n";

prints:

a9959dc27c0bac23be48686ceaa1683c

Answer 2

I agree with Ether. You could also use MD5() function in the database. However if your x_id isn't unique then you have to use some other value to make the MD5 with. An MD5 needs to start with unique data.

And if your x_id is unique, then why do you need an MD5 at all?

Answer 3

Have a look at the Data::UUID perl module?

Answer 4

If you're looking to stick with your scheme, your while loop is all wrong:

while (my($x_id, $inum, $user) = $sth->fetchrow_array() ) {
  $data = $data. "__record_key__^a$x_id^e^a$inum^e^a$user^e^d";
}

This will build up a very long string. You actually want one string per user:

while (my($x_id, $inum, $user) = $sth->fetchrow_array() ) {
  my $data = "__record_key__^a${x_id}^e^a${inum}^e^a${user}^e^d";
  ...
}

I've also put brackets around your variable names for clarity (i.e. ${user}^e^d instead of $user^e^d).

Answer 5

What is this record number? Why does it need to be unique? Is x_id unique? Is there any format, range, or other requirements on the record number value? Does the value mean anything or is it just a unique value?

In cases like these, I like to get rid of special cases. If I need a unique string associated with each record, I let the database give each record a unique string. In database land, each record should have a primary or unique key:

 CREATE TABLE (
       pk INT AUTO_INCREMENT PRIMARY KEY,
       ...
       );

When you insert data into that table, MySQL can choose the value for pk for you.

Once the value you need is stored with the other values, the meat of your program is then:

my $sth = $dbh->prepare("select pk, x_id, inum, user from mytest");
$sth->execute();

while (my($pk, $x_id, $inum, $user) = $sth->fetchrow_array() ) {
    $data = $data. "$pk^a$x_id^e^a$inum^e^a$user^e^d";
    }

You haven't said much about the constraints of your problem though.

Answer 6

Why not UUID() in MySQL? Also replication safe.