I was just about to post a SoPW about this when I realized my "mistake". (Thank you, oh mighty Monasterial teddy bear!)

MySQL is very datatype dependent. Let's say you have the following table 

create table foo (
    bar1 Int
   ,bar2 Int
);

[download]
with a million rows, indexed properly, and you do the following in Perl. 
my $sth = $dbh->prepare_cached( "SELECT bar2 FROM foo WHERE bar1 = ?" 
+);

$sth->execute( 2 );

[download]

You are going to be very unpleasantly surprised by how long it takes. Then, if you're like me, you'll debug it by going into the mysql commandline and executing the following statement: 

SELECT bar2 FROM foo WHERE bar1 = 2;

[download]

That query is going to execute very quickly. Up to 30 times quicker. And, you're going to be left scratching your head. I mean, the two queries are equivalent, right?

Actually, they're not. Using DBI->trace(2), you'll notice something slightly different about them. What DBD::mysql is actually passing to the database is 

SELECT bar2 FROM foo WHERE bar1 = '2';

[download]

Those quotes make all the difference in the world. MySQL is great, but doesn't do heterogenous comparisons very well. This is one of the few places where I'll agree that other RDBMSes, like Oracle, beat MySQL out. And, it's such an easy mistake to make, too!

I have a solution, but it's not the best one. Let's transform our code a little. 

my $sth = $dbh->prepare_cached( "SELECT bar2 FROM foo WHERE bar1 = ?" 
+);

$sth->bind_param( 1, 2, SQL_INTEGER );

$sth->execute();

[download]
Now, DBI will bind the parameter as if it was an integer, not a varchar.

The better solution, imho, would be for DBD::mysql to go ahead and look up the column type and bind appropriately. However, I am aware that there are potential secury concerns, so it may be appropriate for an additional option to be passed, maybe "mysql_discover_bind_type", that would always default to 0. Then, those of us who want it would have to explicitly turn it on, but those who don't know enough would turn it off.

------
We are the carpenters and bricklayers of the Information Age.

Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

I shouldn't have to say this, but any code, unless otherwise stated, is untested

In reply to DBD::mysql and binding placeholders by dragonchild

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.