Getting column information from the MySQL command line

Question

I am trying to get all the columns from MySQL using bash scripting. I want them to be stored in an array. If I write it this way:

mysql -uroot -pPassword1 "select column_name 
 from information_schema.columns 
 where table_schema = 'dbName' 
 and table_name = 'tableName';"

...I get an error that the identifier name is too long. If I write this way:

mysql -uroot -pPassword1 <<- SMTH
 select column_name 
 from information_schema.columns 
 where table_schema = 'dbName' 
 and table_name = 'tableName';
SMTH

everything works fine. But I can't output it to a variable. Any suggestions?

Answer 1

Add -e option:

· --execute=statement, -e statement

Execute the statement and quit. The default output format is like that produced with --batch. See Section 4.2.4, "Using Options on the Command Line", for some examples. With this option, mysql does not use the history file.

mysql -uroot -pPassword1 -e "select column_name from information_schema.columns where table_schema = 'dbName' and table_name = 'tableName';"

Test:

root@onare:/home/onare# mysql -uroot -ponaare -e "select column_name from information_schema.columns where table_schema = 'test' LIMIT 10;"
+--------------+
| column_name |
+--------------+
| AnswerID |
| QuestionID |
| ReplyContent |
| id |
| emp_id |
| call_start |
| call_end |
| call_type |
| idEmployee |
| eName |
+--------------+
root@onare:/home/onare# 

EDIT 2:

If you want to avoid the query header (column_name), use --skip-column-name:

root@onare:/home/onare# mysql -uroot -ponaare -e "select column_name from information_schema.columns where table_schema = 'test' LIMIT 10;" --skip-column-names
+--------------+
| AnswerID |
| QuestionID |
| ReplyContent |
| id |
| emp_id |
| call_start |
| call_end |
| call_type |
| idEmployee |
| eName |
+--------------+
root@onare:/home/onare#