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

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.

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);