{"id":4138,"date":"2011-11-25T21:39:58","date_gmt":"2011-11-25T19:39:58","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=4138"},"modified":"2011-11-25T21:39:58","modified_gmt":"2011-11-25T19:39:58","slug":"reading-results-of-show-statements-on-server-side","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/reading-results-of-show-statements-on-server-side","title":{"rendered":"Reading results of SHOW statements, on server side"},"content":{"rendered":"

SHOW<\/strong> statements are show stoppers on server side. While clients can get a SHOW statement<\/a> as a result set just as any normal SELECT<\/strong>, things are not as such on server side.<\/p>\n

On server side, that is, from within MySQL itself, one cannot<\/em>:<\/p>\n

\n
SELECT `Database` FROM (SHOW DATABASES);<\/pre>\n<\/blockquote>\n
One cannot<\/em>:<\/p>\n
\n
DECLARE show_cursor CURSOR FOR SHOW TABLES;<\/pre>\n<\/blockquote>\n
One cannot<\/em>:<\/p>\n
\n
SHOW TABLES INTO OUTFILE '\/tmp\/my_file.txt';<\/pre>\n<\/blockquote>\n
So it is impossible to get the results with a query; impossible to get the results from a stored routine; impossible to get the results by file reading…<\/p>\n
Bwahaha! A hack!<\/h4>\n
For some SHOW<\/strong> statements, there is a way around this. I’ve been banging my head against the wall for weeks now on this. Now I have a partial solution: I’m able to read SHOW<\/strong> output for several SHOW<\/strong> statements. Namely, those SHOW<\/strong> statements which allow a LIKE or a WHERE<\/a> clause.<\/p>\n
For example, most are familiar with the following syntax:<\/p>\n
\n
USE mysql;\r\nSHOW TABLE STATUS LIKE 'user';<\/pre>\n<\/blockquote>\n
However not so many know that any SHOW<\/strong> statement which accepts LIKE<\/strong>, can also accept WHERE<\/strong>:<\/p>\n
\n
SHOW TABLE STATUS WHERE Name='user'\\G\r\n*************************** 1. row ***************************\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Name: user\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Engine: MyISAM\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Version: 10\r\n\u00a0\u00a0\u00a0\u00a0 Row_format: Dynamic\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Rows: 17\r\n\u00a0Avg_row_length: 69\r\n\u00a0\u00a0\u00a0 Data_length: 1184\r\nMax_data_length: 281474976710655\r\n\u00a0\u00a0 Index_length: 2048\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Data_free: 0\r\n\u00a0Auto_increment: NULL\r\n\u00a0\u00a0\u00a0 Create_time: 2010-10-03 08:23:48\r\n\u00a0\u00a0\u00a0 Update_time: 2011-07-30 19:31:00\r\n\u00a0\u00a0\u00a0\u00a0 Check_time: NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Collation: utf8_bin\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Checksum: NULL\r\n\u00a0Create_options:\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Comment: Users and global privileges<\/pre>\n<\/blockquote>\n
It’s not just about “Name<\/strong>“. I can filter using any column I like:<\/p>\n
\n
SHOW TABLE STATUS WHERE Rows > 1000;\r\nSHOW TABLE STATUS WHERE Rows > 1000 AND Index_length > 65536;<\/pre>\n<\/blockquote>\n
etc.<\/p>\n
If you’ve been to my talk on Programmatic Queries: things you can code with SQL<\/a>, you have a good guess as for where I’m taking this.<\/p>\n
Where there’s WHERE, there’s code<\/h4>\n
I can write code within the WHERE<\/strong> clause. Specifically, I can work with user defined variables. Shall we cut to the point and provide with an example?<\/p>\n
\n
mysql> SET @databases := '';\r\n\r\nmysql> SHOW DATABASES WHERE (@databases := CONCAT(@databases, `Database`, ',')) IS NULL;\r\n\r\nmysql> SELECT @databases;\r\n+-------------------------------------------------------------------+\r\n| @databases\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-------------------------------------------------------------------+\r\n| information_schema,common_schema,mycheckpoint,mysql,sakila,world, |\r\n+-------------------------------------------------------------------+<\/pre>\n<\/blockquote>\n
Let’s discuss the above. We:<\/p>\n