17

Is it possible in MySQL to use the PREPARE command with named parameters such as PDO in PHP:

Here is my example:

 SET @s = 'SELECT * FROM MY_TABLE WHERE my_column_1 = ? AND my_column_2 = ? ';
 PREPARE stmt2 FROM @s;
 SET @a = 54;
 SET @b = 89';
 EXECUTE stmt2 USING @a, @b;

Is it possible to do something like that :

 SET @s = 'SELECT * FROM MY_TABLE WHERE my_column_1 = :value1 AND my_column_2 = :value2 ';
informatik01
  • 16,038
  • 10
  • 74
  • 104
Ricou
  • 946
  • 3
  • 11
  • 22
  • Possible duplicate of [My SQL Dynamic query execute and get ouput into a variable in stored procedure](http://stackoverflow.com/questions/5591338/my-sql-dynamic-query-execute-and-get-ouput-into-a-variable-in-stored-procedure) – Iharob Al Asimi Jun 12 '16 at 23:59
  • Not duplicate - not even close. That thread is about getting the result of a prepared query into OUT param of a stored procedure. – ToolmakerSteve Aug 27 '20 at 19:46

1 Answers1

3

I suggest looking at the documentation regarding this. https://dev.mysql.com/doc/refman/8.0/en/prepare.html

The documentation makes no references to any other way to bind variables other than the ?s, but it does mention that you can use user defined variables.

SET @s = 'SELECT * FROM MY_TABLE WHERE my_column_1 = @a AND my_column_2 = @b ';
PREPARE stmt2 FROM @s;
SET @a = 54;
SET @b = 89';
EXECUTE stmt2;

Produces the same output and the variables are only evaluated at execution of the statement, it just lacks the explicitness of binding the variable to the query.

scragar
  • 6,764
  • 28
  • 36
  • 2
    Its not clear to me whether this has *performance* implications. Googled, but didn't find any discussion of whether use of `@a` creates a query that optimizes well, like `?` and `USING` does. – ToolmakerSteve Aug 27 '20 at 22:27