What is the similarity between prepared statements and MySQL user variables?

As we know that MySQL user variables are specific to client connection within which they are used and exist only for the duration of that connection. When a connection ends, all its user variables are lost. Similarly, the prepared statements also exist only for the duration of the session in which it is created and it is visible to the session in which it is created. When a session ends, all the prepared statements for that session are discarded.

Another similarity is that prepared statements are also not case-sensitive like MySQL user variables. For example, stmt11 and STMT11 both are same as illustrated in the following example −

mysql> Select * from student;
+------+-------+
| Id   | Name  |
+------+-------+
| 1    | Ram   |
| 2    | Shyam |
| 3    | Rohan |
+------+-------+
3 rows in set (0.00 sec)

mysql> SET @A = 'Sohan', @B = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE Stmt11 USING @A, @B;
Query OK, 1 row affected (0.12 sec)

mysql> Select * from Student;
+------+-------+
| Id   | Name  |
+------+-------+
| 1    | Ram   |
| 2    | Shyam |
| 3    | Sohan |
+------+-------+
3 rows in set (0.00 sec)

mysql> SET @A = 'Gaurav', @B = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE STMT11 USING @A, @B;
Query OK, 1 row affected (0.04 sec)

mysql> Select * from Student;
+------+--------+
| Id   | Name   |
+------+--------+
| 1    | Ram    |
| 2    | Shyam  |
| 3    | Gaurav |
+------+--------+
3 rows in set (0.00 sec)

In the above example, once we have executed stmt11 and next time we have executed STMT11 and both of them worked same because prepared statements are not case-sensitive.

seetha

e

Updated on: 20-Jun-2020

51 Views

Kickstart Your Career

Get certified by completing the course

Get Started