Linux Ask!

Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.

Jan 302010
 

Prepare statement in MySQL

Answer:

MySQL 5.1 provides support for server-side prepared statements, allow you to code more efficient database driven program.

Example:

mysql> PREPARE stmt_password FROM 'SELECT password(?)';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @a = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt_password USING @a;
+-------------------------------------------+
| password(?)                               |
+-------------------------------------------+
| *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @a = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt_password USING @a;
+-------------------------------------------+
| password(?)                               |
+-------------------------------------------+
| *12033B78389744F3F39AC4CE4CCFCAD6960D8EA0 |
+-------------------------------------------+
1 row in set (0.00 sec)

As you can see above, once you have prepared the statement in server, you can reuse the statement by injecting a new value. It is faster since MySQL only need to one time SQL statement parsing, rather than parse your SQL statement every time and execute.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>