Loop through result set of mysql in Bash

Question

I have a simple bash script. I wish to get an exact count of the number of rows in each table of the database.

#!/bin/bash

TABLES_OLD=$( mysql -u user -ppassword MySchema --batch --skip-column-names -e"SHOW TABLES FROM MySchema" )

for table in "${TABLES_OLD[@]}"
do
    QUERY="SELECT COUNT(*) FROM ${table}"
    echo "${QUERY}"
done

The script prints:

SELECT COUNT(*) FROM Table 1
Table2
Table3
Table4
etc...

Clearly this is not what I want, and I don't even understand how what is happening is possible. What am I doing wrong?

Add output of `mysql -u user -ppassword MySchema --batch --skip-column-names -e"SHOW TABLES FROM MySchema"` to your question. — Cyrus, Apr 25 '16 at 17:54
`TABLES_OLD` is not an array. It is a string. `tables_old=($(mysql ....))` would make an array split on words (but would also glob metacharacters, etc.). The correct way to loop over line-by-line data from a stream is explained in [Bash FAQ 001](http://mywiki.wooledge.org/BashFAQ/001). — Etan Reisner, Apr 25 '16 at 17:54

score 1 · Answer 1 · answered Jan 18 '17 at 20:12

Try this, put the tables into an array then loop thru the results
db_host='host'
db_user='user'
db_pass='password'
db='your_db'

read -ra var_id <<< $(mysql -h $db_host -u $db_user -p$db_pass $db -sse "show tables from $db")
for i in "${var_id[@]}"; 
    do
results=$(mysql -h $db_host -u $db_user -p$db_pass $db -sse "select count(*)from $i")
echo "$i $results"

done

score 0 · Answer 2 · answered Apr 25 '16 at 17:56

0

This should do it :

#/bin/bash
mysql -u user-ppassword -e "SELECT table_name, table_rows
                     FROM INFORMATION_SCHEMA.TABLES
                     WHERE TABLE_SCHEMA = 'your_data_base_name';"

answered Apr 25 '16 at 17:56

sjsam

21,411
5
55
102

This does not seem to give me the exact count on many tables. It's really close though. – LeviX Apr 25 '16 at 18:14
You might wish to have a look [here](http://dba.stackexchange.com/questions/65216/need-help-to-understand-how-information-schema-tables-get-updated) – sjsam Apr 25 '16 at 18:22
@LeviX : Youmay also wish to look [here](http://stackoverflow.com/a/2221898/1620779) – sjsam Apr 25 '16 at 19:08

score -1 · Answer 3 · answered Apr 25 '16 at 17:53

-1

Replace echo with eval

The code will be

#!/bin/bash

TABLES_OLD=$( mysql -u user -ppassword MySchema --batch --skip-column-names -e"SHOW TABLES FROM MySchema" )

for table in "${TABLES_OLD[@]}"
do
    QUERY="SELECT COUNT(*) FROM ${table}"
    eval "${QUERY}"
done

answered Apr 25 '16 at 17:53

piyushj

1,546
5
21
29

No, this won't help. This isn't *at all* the issue and isn't *at all* a good idea. – Etan Reisner Apr 25 '16 at 17:54

Loop through result set of mysql in Bash

3 Answers3