Store mysql query output into a shell variable

Question

I need a variable to hold results retrieved from the database  So far this is basically what I m trying with no success   myvariable   mysql database -u  user -p  password   SELECT A  B  C FROM table a    My understanding of bash commands is not very good as you can see

User · Answer

You have the pipe the other way around and you need to echo the query, like this:

myvariable=$(echo "SELECT A, B, C FROM table_a" | mysql db -u $user -p $password)

Another alternative is to use only the mysql client, like this

myvariable=$(mysql db -u $user -p $password -se "SELECT A, B, C FROM table_a")

(-s is required to avoid the ASCII-art)

Now, BASH isn't the most appropriate language to handle this type of scenarios, especially handling strings and splitting SQL results and the like. You have to work a lot to get things that would be very, very simple in Perl, Python or PHP.

For example, how will you get each of A, B and C on their own variable? It's certainly doable, but if you do not understand pipes and echo (very basic shell stuff), it will not be an easy task for you to do, so if at all possible I'd use a better suited language.

User · Answer

myvariable   mysql database -u  user -p password   SELECT A  B  C FROM table a    without the blank space after -p  Its trivial  but without don t work

User · Answer

If you want to use a single value in bash use     companyid   mysql --user  Username --password  Password --database  Database -s --execute  select CompanyID from mytable limit 1   cut -f1     echo   companyid

User · Answer

My two cents here   myvariable   mysql database -u  user -p password -sse  SELECT A  B  C FROM table a  2 gt  amp 1        grep -v  Using a password     Removes both the column names and the annoying  but necessary  password warning  Thanks  Dominic Bartl and John for this answer

User · Answer

Another example when the table name or database contains unsupported characters such as a space  or  -   db  data-base   db d    db d      db d   db db d       myvariable  mysql --user  user --password  password -e  SELECT A  B  C FROM  db d table a

User · Answer

I don t know much about the MySQL command line interface  but assuming you only need help with the bashing  you should try to either swap the commands around like so   myvariable   echo  SELECT A  B  C FROM table a    mysql database -u  user -p password    which echos the string into MySQL  Or  you can be more fancy and use some new bash-features  the here string   myvariable   mysql database -u  user -p password lt  lt  lt  SELECT A  B  C FROM table a     resulting in the same thing  assuming you re using a recent enough bash version   without involving echo   Please note that the -p password is not a typo  but is the way MySQL expects passwords to be entered through the command line  with no space between the option and value    Note that myvariable will contain everything that MySQL outputs on standard out  usually everything but error messages   including any and all column headers  ASCII-art frames and so on  which may or may not be what you want   EDIT  As has been noted  there appears to be a -e parameter to MySQL  I d go for that one  definitely

User · Answer

To read the data line-by-line into a Bash array you can do this   while read -a row do     echo      row 0      row 1      row 2      done  lt   lt  echo  SELECT A  B  C FROM table a    mysql database -u  user -p  password    Or into individual variables   while read a b c do     echo      a     b     c     done  lt   lt  echo  SELECT A  B  C FROM table a    mysql database -u  user -p  password

User · Answer

myvariable   mysql -u user -p password  -s -N  lt  lt QUERY INPUT     use databaseName      SELECT fieldName FROM tablename WHERE filedName  fieldValue   QUERY INPUT   echo  myvariable  myvariable

User · Answer

A more direct way would be   myvar   mysql mydatabase -u  user -p password -se  SELECT a  b  c FROM table a

User · Answer

If you have particular database name and a host on which you want the query to be executed then follow below query   outputofquery   mysql -u  dbusername  -p  dbpassword  -h  dbhostname  -e  SELECT A  B  C FROM table a    dbname    So to run the mysql queries you need to install mysql client on linux

User · Answer

Other way   Your Script      bin sh    Set these variables MyUSER  root      DB USERNAME MyPASS  yourPass      DB PASSWORD MyHOST  yourHost       DB HOSTNAME DB NAME  dbName  CONTAINER  containerName   if use docker    Get data data    MyHOST -u  MyUSER -p MyPASS  DB NAME -h  CONTAINER -e  SELECT data1 data2 from table name LIMIT 1    -B --skip-column-names     Set data data1   echo  data   awk   print  1    data2   echo  data   awk   print  2       Print data echo  data1  data2

[mysql] Store mysql query output into a shell variable

Examples related to mysql

Examples related to sql

Examples related to bash

Examples related to scripting