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.

• tombom: I don't think there is. You'll have to use information_schema.
• Rick James: You could do SHOW STATUS, then parse the output.

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)

• kasi: yes i am aware of this. Please go through my question thoroughly. Anyway thanks Keriaki.

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'.

• tombom: The question is about status variables, the ones that are displayed by show status like 'Qcache_hits' to stick with the example from the question. It's not about the ones you can display with show variables.... With the latter you can configure the server on the fly, with the first one just the status of certain aspects are shown.