Mysql – How to select status variables into temp variables in thesql

MySQL

I have selected global variables into temporary local variables like below.

mysql> select @@long_query_time into @a;  
Query OK, 1 row affected (0.00 sec)

mysql> select @a;  

+------+  
| @a   |  
+------+  
|    1 |  
+------+  
1 row in set (0.00 sec)

But when i am trying to do the same for status variables like below , i am getting error. 

mysql> select @@qcache_hits;  
ERROR 1193 (HY000): Unknown system variable 'qcache_hits'

I know we can select status variables into temp variables using information_schema tables like below. 

SELECT @a := variable_value from information_schema.global_status where variable_name='com_select';

But I want to know if there is any way to select server status variables into temp variables without using information_schema tables.

Thanks in advance.

Best Answer

You can use information_schema's table for that purpose:

mysql> select VARIABLE_VALUE into @qcache_hits FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE  VARIABLE_NAME="qcache_hits";
Query OK, 1 row affected (0.00 sec)

mysql> select @qcache_hits;
+--------------+
| @qcache_hits |
+--------------+
| 0            |
+--------------+
1 row in set (0.00 sec)
Related Question