Home / server / Questions / 320457
In Process
Phanindra
Asked: 2011-10-12 04:24:38 +0800 CST

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

  • 772

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

mysql query

3 Answers

  1. 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>
    • 2 
  2. $ 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 }"
    • 1

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

    • 1