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.

Related posts:

1. Reset MySQL root password
2. Create temporary table in MySQL
3. How to check and kill long running MySQL query
4. How to rename table in MySQL
5. Set the auto increment start value in MySQL?