How to fetch field from MySQL query result in bash

Question

I would like to get only the value of a MySQL query result in a bash script. For example the running the following command:

mysql -uroot -ppwd -e "SELECT id FROM nagios.host WHERE name='$host'"

returns:

+----+
| id |
+----+
| 0  |
+----+

How can I fetch the value returned in my bash script?

Potherca · Accepted Answer · 2017-07-28 11:37:29Z

134

Use -s and -N:

> id=`mysql -uroot -ppwd -s -N -e "SELECT id FROM nagios.host WHERE name='$host'"`
> echo $id
0

From the manual:

--silent, -s

   Silent mode. Produce less output. This option can be given multiple
   times to produce less and less output.

   This option results in nontabular output format and escaping of
   special characters. Escaping may be disabled by using raw mode; see
   the description for the --raw option.

--skip-column-names, -N

   Do not write column names in results.

EDIT

Looks like -ss works as well and much easier to remember.

edited Jul 28, 2017 at 11:37

Potherca

14.8k5 gold badges86 silver badges108 bronze badges

answered Mar 4, 2012 at 21:26

dorsh

24.9k2 gold badges29 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jess Telford Over a year ago

Similar to @corgi's answer, multiple columns will be tab delimited.

Jess Telford Over a year ago

To avoid getting errors as output, redirect stderr: id=`mysql -uroot -ppwd -ss -e "SELECT id FROM nagios.host WHERE name='$host'" 2> /dev/null`

Deckard Over a year ago

How to fetch multiple columns?

dorsh Over a year ago

@Deckard $ set mysql -uroot -ppwd -ss -e "SELECT 'a','b'. Then the value of $1 should be "a" and $2 "b".

Pratik Patil · Accepted Answer · 2015-04-28 05:52:17Z

13

Even More Compact:

id=$(mysql -uroot -ppwd -se "SELECT id FROM nagios.host WHERE name=$host");
echo $id;

answered Apr 28, 2015 at 5:52

Pratik Patil

3,7833 gold badges33 silver badges33 bronze badges

Comments

Finbar Crago · Accepted Answer · 2012-03-04 21:27:19Z

3

Try:

mysql -B --column-names=0 -uroot -ppwd -e "SELECT id FROM nagios.host WHERE name='$host'"

-B will print results using tab as the column separator and

--column-names=0 will disable the headers.

answered Mar 4, 2012 at 21:27

Finbar Crago

4445 silver badges13 bronze badges

2 Comments

Artistan Over a year ago

thank you, this is helpful for creating a bash option list.

Doug Over a year ago

This is very useful as it shows how to return the result "without" the column name which is really useful when you are trying to set a variable.

Marcelo Amorim · Accepted Answer · 2013-06-12 19:57:37Z

1

I tried the solutions but always received empty response.

In my case the solution was:

#!/bin/sh

FIELDVALUE=$(mysql -ss -N -e "SELECT field FROM db.table where fieldwhere = '$2'")

echo $FIELDVALUE

answered Jun 12, 2013 at 19:57

Marcelo Amorim

1,68223 silver badges23 bronze badges

Collectives™ on Stack Overflow

How to fetch field from MySQL query result in bash

4 Answers 4

4 Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related