User Variables in mysql


one more mysql post for this week!

variables are mysql specific extension.

They hold single values. They are case sensitive. if you assign to a variable using a statement, that returns multiple rows, the value from the last variable is used. if there are no items in the query, and if it is assigned to a variable, no values will be assigned to the variable. instead the old value is retained, if it has any!

User variables are set for the duration of the thread. they are useful particularly when we work with mysql via console.

here are some examples

mysql> select * from delhi;
+—————+——–+
| first_name | amount |
+—————+——–+
| kavitha | 25 |
| raman | 68 |
| anbu | 45 |
| selva | 98 |
| nalini | 25 |
| amutha | 36 |
| richard | 75 |
| lessthanseven | 5 |
+—————+——–+
8 rows in set (0.00 sec)

mysql> set @testvar=5;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from delhi where amount<@testvar+1;
+—————+——–+
| first_name | amount |
+—————+——–+
| lessthanseven | 5 |
+—————+——–+
1 row in set (0.00 sec) mysql> set @a=5, @b=6, @c=7; Query OK, 0 rows affected (0.00 sec)

mysql> set @a=5, @b=6, @c=7;
Query OK, 0 rows affected (0.00 sec)

mysql> select @a:=(@b:=@b+1);
+—————-+
| @a:=(@b:=@b+1) |
+—————-+
| 7 |
+—————-+
1 row in set (0.00 sec)

A user variable cannot be used directly in an SQL statement as an identifier or as part of an identifier

mysql> set @thisname:=’first_name’;
Query OK, 0 rows affected (0.00 sec)

mysql> select @thisname from delhi;
+————+
| @thisname |
+————+
| first_name |
| first_name |
| first_name |
| first_name |
| first_name |
| first_name |
| first_name |
| first_name |
+————+
8 rows in set (0.00 sec)

Very smart 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s