CODEWITHSUNDEEP
perl
RLO
RLO

Reputation: 15

Saving a Perl Hash (keys/values data structure) in MySQL field

I have a perl hash that I want to save in a database field (MySQL) so that I can retrieve it for update right away.

Inversely, I want to define a datable field (MySQL) that's intended to save the perl hash.

Is this possible and what would be best practice to achieve it?

Upvotes: 1

Views: 649

Answers (3)

oalders
oalders

Reputation: 5279

If you are sure that your data can be serialized to JSON, you can use a column with MySQL's JSON data type.

"As of MySQL 5.7.8, MySQL supports a native JSON data type that enables efficient access to data in JSON (JavaScript Object Notation) documents."

Upvotes: 0

Sobrique
Sobrique

Reputation: 53478

It's not possible, because a hash is a data structure in memory. It may have pointers, buffers etc.. You therefore cannot just shove that into a tablespace.

What you can do is serialise it. Turn it into a single "thing" which you can then stash and retrieve.

You can use a variety of ways of doing this - some common ones might be:

  • Storable (core in perl, produces binary data and not particularly portable).
  • JSON - pretty common and therefore portable. Produces plain text.
  • XML - similar to JSON, but with some different usage scenarios.

Which you use depends very much on what you're trying to accomplish. A very crude example might be to use freeze/thaw from Storable and encode it to text via base64.

use strict;
use warnings;
use Storable qw ( freeze thaw );
use MIME::Base64;

my %test_hash = (
    "fish"   => "paste",
    "apples" => "pears"
);

my $frozen = encode_base64 freeze( \%test_hash );

my %new_hash =  %{ thaw (decode_base64 $frozen ) };
print Dumper $new_hash;

But this really does raise the question - why are you trying to pack a hash - which is a data object - into a database - which is built for handling data objects. You get the worst of both worlds by doing so, because your database will be effectively blind to the hash content. I would suggest that you need to rethink what you're trying to do here.

Edit: You can technically do this with Data::Dumper as well - which prints a variable to text, which you can eval. This is a bad idea, since whilst it looks like it works with trivial structures, there are some gotchas (e.g. 'parent' references). And you still have to read it back in and parse it, which is what you would have to do with JSON or XML. You you end up with the worst of all worlds tackling it this way.

Upvotes: 3

Chankey Pathak
Chankey Pathak

Reputation: 21666

It doesn't sound like a good idea, see jeffa's reply to similar thread: Store hash in mysql

I would suggest using Storable to store your data structure in a file

use Storable;
store \%hash, 'file';
my $hashref = retrieve('file');

You could try the below though:

my @keys = keys %hash;    
my @values = values %hash;

my $sth = $dbh->prepare("INSERT INTO tablename(id, value) VALUES (?,?);");

$sth->execute_array({},\@keys, \@values);

Upvotes: 1

Related Questions