How to select status variables into temp variables in mysql

Question

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.

Answer 1

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)

Answer 2

From documentation:

Some variables displayed by SHOW VARIABLES may not be available using SELECT @@var_name syntax; an Unknown system variable occurs. As a workaround in such cases, you can use SHOW VARIABLES LIKE 'var_name'.