5

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?

4

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# 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.