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?

score 133 · Accepted Answer · edited Jul 28 '17 at 11:37

133

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 '17 at 11:37

Potherca

13,207
5
76
94

answered Mar 04 '12 at 21:26

dorsh

23,750
2
27
29

Similar to @corgi's answer, multiple columns will be tab delimited. – Jess Telford Nov 09 '12 at 03:29
2

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\`` – Jess Telford Nov 09 '12 at 03:38
How to fetch multiple columns? – Deckard May 15 '14 at 09:30
1

@Deckard $ set `mysql -uroot -ppwd -ss -e "SELECT 'a','b'`. Then the value of $1 should be "a" and $2 "b". – dorsh Oct 20 '14 at 12:32

score 12 · Answer 2 · answered Apr 28 '15 at 05:52

12

Even More Compact:

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

answered Apr 28 '15 at 05:52

Pratik Patil

3,662
3
31
31

score 3 · Answer 3 · answered Mar 04 '12 at 21:27

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 04 '12 at 21:27

Finbar Crago

432
5
12

thank you, this is helpful for creating a bash option list. – Artistan Jan 12 '21 at 02:29
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. – Doug Feb 11 '21 at 01:04

score 1 · Answer 4 · answered Jun 12 '13 at 19:57

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 '13 at 19:57

Marcelo Amorim

1,662
23
23

How to fetch field from MySQL query result in bash

4 Answers4

Linked