5

I'm writing a bash script where I'm using mysql query in that script where the format which I'm getting is quite different.

Query:

root@debian:~# mysql -u root -ptoor super_market -h 0 -e "select * from items;" 
+---------+------------+--------+-------------+-------+
| item_id | item_name  | weight | brand       | price |
+---------+------------+--------+-------------+-------+
|       1 | Milk       |   2.00 | Nestle      |  2.00 |
|       2 | Cheese     |   2.50 | Amul        |  6.00 |
|       3 | Chips      |  25.00 | Lays        |  3.00 |
|       4 | Coke       |   5.00 | Coke Cola   |  3.50 |
|       5 | Engage     |   5.00 | Deo         |  3.50 |
|       6 | Engage     |   5.00 | Deo         |  3.50 |
|       7 | Ear phones |   4.00 | Skull Candy | 32.30 |
+---------+------------+--------+-------------+-------+

formatted using column -t command the output format is getting aligned

root@debian:~# mysql -u root -ptoor super_market -h 0 -e "select * from items;" | column -t
item_id  item_name  weight  brand   price
1        Milk       2.00    Nestle  2.00
2        Cheese     2.50    Amul    6.00
3        Chips      25.00   Lays    3.00
4        Coke       5.00    Coke    Cola   3.50
5        Engage     5.00    Deo     3.50
6        Engage     5.00    Deo     3.50
7        Ear        phones  4.00    Skull  Candy  32.30

Bash Script the bash script which I tried using the above command

root@debian:~# cat test
#!/bin/bash

while read -r output;
do
    echo $output | awk '{print $4}'
    #do something
done< <(mysql -u root -ptoor super_market -h 0 -e "select * from items;" | sed 1d |column -t)

output

root@debian:~# ./test
Nestle
Amul
Lays
Coke
Deo
Deo
4.00

But the Expected Output:

Nestle
Amul
Lays
Coke Cola
Deo
Deo
Skull Candy

Yeah! you can say use select brand from items. This is for example in real time I'm using quite different command.

Any Hint or Help ?

4
  • You're explicitly excluding brand with your call to sed 1d which strips the header from the output. Try removing that from the pipeline. Also, for the love of science, why are you running random bash scripts as root?
    – DopeGhoti
    Jun 1, 2016 at 22:35
  • @DopeGhoti edited. And its a test machine Nothing to worry. Jun 1, 2016 at 22:38
  • Tell awk that your field separator is a tab (^I). The problem is that Ear phones is looking like two fields because a space (by being a non-endline whitespace character) is by default a field separator.
    – DopeGhoti
    Jun 1, 2016 at 22:45
  • @DopeGhoti I tried that too getting a blank output Jun 1, 2016 at 22:49

5 Answers 5

3

Use mysql with the -t flag, to get the output you get from a query in mysql directly as your bash output.

Afterwards you can use sed or awk to clean up the delimiters, to remove the horizontal lines and the |'s.

2

output of mysql is tab delimited. column replaces tabs with spaces breaking your input.

#!/bin/bash

while read -r output;
do
    # use double quotes with "$output" to avoid conversion
    # of tabs to spaces and set awk's Field Separator to "\t"
    echo "$output" | awk -F"\t" '{print $4}'
    #do something
done< <(mysql -u root -ptoor super_market -h 0 -e "select * from items;" | sed 1d)

test run:

root@c2:~# mysql -u root -ptoor super_market -h 0 -e "select * from items;"
+---------+------------+--------+-------------+-------+
| item_id | item_name  | weight | brand       | price |
+---------+------------+--------+-------------+-------+
|       1 | Milk       |      2 | Nestle      |     2 |
|       2 | Cheese     |    2.5 | Amul        |     6 |
|       3 | Chips      |     25 | Lays        |     3 |
|       4 | Coke       |      5 | Coke Cola   |   3.5 |
|       5 | Engage     |      5 | Deo         |   3.5 |
|       6 | Engage     |      5 | Deo         |   3.5 |
|       7 | Ear phones |      4 | Skull Candy |  32.3 |
+---------+------------+--------+-------------+-------+
root@c2:~# ./test.sh 
Nestle
Amul
Lays
Coke Cola
Deo
Deo
Skull Candy
root@c2:~# 
2
  • I got the output as blank enteries Jun 1, 2016 at 23:31
  • try to comment out | awk... to see what you get.
    – adonis
    Jun 1, 2016 at 23:38
0

You don't have to use column tool as your table is really done. Try using awk with the common delimiter |.

You can do the following in your echo line

echo "$output" | awk -F'|' '{print $5}'

So it ends as

#!/bin/bash

while read -r output;
do
    echo $output | awk '{print $5}'
    #do something
    done< <(mysql -u root -ptoor super_market -h 0 -e "select * from items;" | sed '1,2d')
0

This is a very dirty and hacky solution, but you can use the dot (.) as the separator since you have floating point numbers on each side. You can call awk as follows

awk -F '.' '{a=gensub(/^[0-9]*\s*|\s*[0-9]*$/,"","g",$2); print a}'

in the script and it shall print only the brand.

There will be extraneous spaces in the middle of the brand names but you can extend the awk script to cope with that as well:

awk -F '.' '{a=gensub(/^[0-9]*\s*|\s*[0-9]*$/,"","g",$2); b=gensub(/\s+/," ","g",a); print b}'

awk's gensub function is a very powerful tool for such kinds of problems. Although this specific example is dirty, i believe, that knowing how to use gensub (the manual is quite decent) will allow you to solve similar problems with ease.


The above is GNU awk (gawk) specific. Old school unix systems (AIX, HP-UX) have no gawk. Funny enough, for some unknown reason, debian also has no gawk by default. debian has mawk as the default awk installed. Here is a mawk version:

awk -F '.' '{gsub(/^[0-9]*\s*|\s*[0-9]*$/,"",$2); gsub(/\s+/," ",$2); print $2}'

mawk has not gensub, it only has gsub (global substitution). gsub is vastly inferior to gensub because it does not return its substitution. If you are using debian and need awk often i strongly recommend installing gawk (it is in the main repo).

2
  • root@debian:~# mysql -u root -ptoor super_market -h 0 -e "select * from items;" -s | column -t | awk -F '.' '{a=gensub(/^[0-9]*\s*|\s*[0-9]*$/,"","g",$2); b=gensub(/\s+/," ","g",a); print b}' awk: line 2: function gensub never defined awk: line 2: function gensub never defined root@debian:~# I'm getting this error Jun 1, 2016 at 23:35
  • Jolly dee... debian has mawk as the default awk. Still doable but even dirtier. As i mentioned in the edited answer, i strongly recommend installing the GNU awk.
    – grochmal
    Jun 2, 2016 at 0:01
-1
mysql -N -B  moodle -e  "select id from mdl_course;"

output

abcd@pqrs:~/backup/script $ mysql -N -B  moodle -e  "select id from mdl_course;"
1
2
4
5
8
9
10
11
12
14
16
17
3
6
7
15
1
  • 1
    From the question, "you can say use select brand from items. This is for example in real time I'm using quite different command"
    – Jeff Schaller
    Sep 16, 2023 at 18:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .