Possible to select individual values using SHOW STATUS in MySQL?

Question

Is it possible to write a query that will return only the value of a variable returned by MySQL's SHOW STATUS? Ideally I want something like:

SELECT `Value` FROM (SHOW STATUS LIKE 'Com_delete')

or something similar.

Is this possible?

Answer 1

If you are using MySQL 5.1 or above, you should be able to get that data from INFORMATION_SCHEMA like this for global status:

select VARIABLE_VALUE 
from information_schema.GLOBAL_STATUS 
where VARIABLE_NAME = 'Com_delete';

Or if you want the session status instead:

select VARIABLE_VALUE 
from information_schema.SESSION_STATUS 
where VARIABLE_NAME = 'Com_delete';

The default for SHOW STATUS is SESSION status, so the latter query would work as a replacement for that.

Answer 2

Complementing Ike Walker answer, INFORMATION_SCHEMA is deprecated on MySQL 5.7.6, as the Performance Schema tables are intended to replace the INFORMATION_SCHEMA tables.

So, by querying the INFORMATION_SCHEMA you may get an error like this:

The 'INFORMATION_SCHEMA.SESSION_STATUS' feature is disabled; see the documentation for 'show_compatibility_56'

According to this, you can set show_compatibility_56 to ON to enable backwards compatibility and keep using 5.6 syntax, or enable the PERFORMANCE_SCHEMA and query the new tables:

performance_schema.global_variables
performance_schema.session_variables

performance_schema.global_status
performance_schema.session_status