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

Published on 20-Feb-2018 11:19:44

• Related Questions & Answers
• How can we use prepared statements in MySQL?
• What are the properties of MySQL user variables?
• Batch Inserts Using JDBC Prepared Statements
• Why are Prepared Statements in JDBC faster than Statements? Explain?
• What is the difference between class variables and instance variables in Java?
• What is the difference between dynamic type variables and object type variables?
• User-defined variables vs Local Variables in MySQL?
• System variables vs User-defined variables in MySQL?
• What is the difference between break and continue statements in JavaScript?
• What is the difference between break and continue statements in C#?
• Using User-Defined Variables in MySQL
• What is the MySQL user creation script?
• What is the difference between global and local variables in Python?
• What is the difference between global and local Variables in JavaScript?
• What is the difference between for...of and for...in statements in JavaScript?