Search code examples
mysqlmysql++

mysql - MySQL Prepared stamement bind same value multiple times using one variable

I have a query that is being run multiple times so i tried to use Prepared statements. But i want to know if there is a way in which i can bind same value/variable to multiple parameter.

Here is the query in question

PREPARE stmt_pn FROM ' SELECT  `date`, `product_id`, `price` FROM `prd`.`pn_ceramic` WHERE `date` IN ( DATE_ADD("2014-08-05", INTERVAL ? DAY) , DATE_ADD("2014-08-16", INTERVAL ? DAY) , DATE_ADD("2014-08-23", INTERVAL ? DAY)  ) ';

SET @dn = '1';

EXECUTE stmt_pn USING @dn;

This gives me 

ERROR 1210 (HY000): Incorrect arguments to EXECUTE

Here is a sample data set

+---------------------+------------+-------+
| date                | product_id | price |
+---------------------+------------+-------+
| 2014-08-05 00:00:00 |  1.27      | 2.10  |
| 2014-08-06 00:00:00 |  1.47      | 3.00  |
| 2014-08-07 00:00:00 |  1.57      | 2.39  |
| 2014-08-16 00:00:00 |  1.87      | 4.17  |
| 2014-08-17 00:00:00 |  1.37      | 6.06  |
| 2014-08-18 00:00:00 |  1.37      | 2.20  |
| 2014-08-23 00:00:00 |  1.67      | 4.85  |
| 2014-08-24 00:00:00 |  1.47      | 5.34  |
+---------------------+------------+-------+

From this data set the query should return 

+---------------------+------------+-------+
| date                | product_id | price |
+---------------------+------------+-------+
| 2014-08-06 00:00:00 |  1.47      | 3.00  |
| 2014-08-17 00:00:00 |  1.37      | 6.06  |
| 2014-08-24 00:00:00 |  1.47      | 5.34  |
+---------------------+------------+-------+

As i'm trying to bind multiple parameter using the same value, but i wonder what is the fastest and cleanest way to achieve this. Because in my case the number of dates in the Where in clause can exceed 100 and for every case the interval will be the same. So i don't want to create like 100 variables for each of the 100 parameters which will all have the same values . Also performance is crucial in my case , and adding another loop to create 100 variables is something i want to avoid.

Solution

  • No, you can not. You should realize, that formal definition isn't same as actuall call. When you're defining your statement, you're defining placeholders and, while using it, all claimed parameters must be passes - no matter if real value is same for all of them (or for some of them).

    MySQL has no idea how you will use your statement, either it will be same value when calling or not. Therefore, the only right way is to recount all your parameters during that call. That is - how it's intended to work.