Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

Re: DBI and mysql query formatting

by afoken (Abbot)
on Dec 08, 2013 at 15:12 UTC ( #1066216=note: print w/replies, xml ) Need Help??

in reply to DBI and mysql query formatting

A few points, not related to the keys/values question:

  • the map and the quotes around $_ are redundant in join(", ", map { "$_" } keys %test). Just use join(', ',keys %test).
  • Abusing backticks as quotes for identifiers is MySQL specific, remove them. You don't need them for identifiers that are not reserved names or contain special characters. And you should not use identifiers that are reserved words or contain funny characters. If you insinst on "funny" table and column names, use DBI's quote_identifier method, that works with all databases supported by DBI.
  • Use placeholders. You don't want to discuss with Bobby's mom why your database suddenly looks like ground zero. And you surely don't want to discuss that with your boss. There is absolutely no excuse for not using placeholders. See Re: Counting rows Sqlite, Re^2: Massive Memory Leak, Re^5: Variable interpolation in a file to be read in for some more information.
  • For bulk inserts, consider using the native tools that came with the database. They are optimized for speed, so they are usually much faster than any code you can write in Perl. For just a few inserts into the same table, prepare a single INSERT statement (using placeholders) and execute it repeatedly. This way, the database and the DBI database driver can optimize as much as possible. Read the database driver's manual page (DBD::mysql in your case) to find some optimizing hints: Some databases prefer to have bulk inserts in transactions, others suffer from using transactions in bulk inserts.

Placeholders example:

my $names=join(',',keys %test); my $placeholders=join(',',('?' x scalar keys %test)); my $sth=$dbh->prepare("INSERT into test ($names) VALUES ($placeholders +)"); # no error check, assuming RaiseError => 1 in DBI->connect() $sth->execute(values %test); # again, no error check, DBI will die on +errors $sth->finish(); # no error check, DBI will handle that

Note that DBI automatically translates undef in values to NULL in the database, so you don't have to care for that. And of course, you don't have to care for correctly quoting values.


Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1066216]
[erix]: how does one find out how to install a program like cvsgrep
[erix]: I mean, I suppose it's just the one file so I can download that
[erix]: Makefile.PL should tell me, probably

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (4)
As of 2017-06-22 16:44 GMT
Find Nodes?
    Voting Booth?
    How many monitors do you use while coding?

    Results (524 votes). Check out past polls.