Need to fetch result of only one column from the MySQL status variables

Question

I wanted to fetch only the value from status variables. Suppose the query is

show status where Variable_name='Aborted_clients'

It returns

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Aborted_clients | 4     |
+-----------------+-------+

From here I need to fetch only the value of column "Value". Is there any way. When I am trying to this query as sub query, it is throwing error.

I am using MySQL version 5.0.24. And OS is windows

The best answer I saw to this is from SO - http://stackoverflow.com/a/4326773/3812704 - `mysql -sse "select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Aborted_clients';"` — Dani_l, Mar 22 '16 at 09:37

score 2 · Answer 1 · answered Oct 11 '11 at 12:58

Go to information_schema database and do a select from the global_status table instead:

mysql> use information_schema;
Database changed
mysql> select * from global_status where variable_name='aborted_clients';
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| ABORTED_CLIENTS | 22             |
+-----------------+----------------+
1 row in set (0.01 sec)

mysql> select variable_value from global_status where variable_name='aborted_clients';
+----------------+
| variable_value |
+----------------+
| 22             |
+----------------+
1 row in set (0.01 sec)

mysql>

I am using MySQL version 5.0.24, so the table global_status is not present in information_schema — Phanindra, Oct 11 '11 at 14:05

quanta · Answer 2 · 2011-10-11T14:58:09.487

1

$ mysql -u root -p --skip-column-name -e \
    "show status where variable_name='Aborted_clients';" | awk '{ print $2 }'

If you want to do it in a shell script, put a ~/.my.cnf file in $HOME with belows content:

[mysql]
user     = root
password = pa$$w0rd
host     = localhost

If you are running MySQL on Windows, take a look at gawk:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql.exe \
    --skip-column-name -u root -p -e \
    "show status where variable_name='aborted_clients';" | \
        "C:\Program Files(x86)\GnuWin32\bin\awk.exe" "{ print $2 }"

edited Oct 11 '11 at 14:58

answered Oct 11 '11 at 12:58

quanta

51,413
19
159
217

I am working on windows based, also I need a MySQL query to get the output which will be used in our application. – Phanindra Oct 11 '11 at 14:06
1

You can use gawk: http://gnuwin32.sourceforge.net/packages/gawk.htm – quanta Oct 11 '11 at 14:50
The option is `--skip-column-names` not `--skip-column-name` (see https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html#option_mysql_skip-column-names). – tsauerwein Jul 09 '18 at 07:42

score 1 · Answer 3 · answered Oct 12 '11 at 03:06

Of course, you can use the following:

C:\> mysql -u... -p... -A -N -e"show global status like 'Aborted_clients'" > abc.txt

This will echo two tokens

Aborted_clients
the number

I am not a Windows expert, but I do know something you will have to emulate from Linux.

The awk command is what you would need.

C:\> mysql -u... -p... -A -N -e"show global status like 'Aborted_clients'" | awk '{print $2}'

The problem is that awk does not exist in Windows batch language or in PowerShell. However, here is a nice blogpost on how to emulate it in Windows PowerShell.

I hope it helps.

Need to fetch result of only one column from the MySQL status variables

3 Answers3